Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create mailto: URL with multiple recipients (2007)

    Hello everybody,

    I want to manage a small "database" of contact addresses with Excel. I know it's better with Access - it's not available at where I work.

    Now I'm a little stuck with Excel's functions (or probably do it with VB).

    The spreadsheet looks like this:
    Name
    Address
    E-Mail

    All I want is to create a field "Create Mail to all" and have it call up "mailto:email@none.com, email2@yes.com, ..." - whereas these e-mails are stored in C1, C2, C3,... . I saw some commercial software out there doing exactly that (XL Email Manager), but I don't think it's too hard.

    Hope somebody can help me,
    moon

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create mailto: URL with multiple recipients (2

    Hi Moontear

    Here is some code that will loop through Column C and create a mailto list, you may want to adapt it to your needs:

    <pre>Sub MailAll()
    Dim intCount As Integer
    Dim i As Integer
    Dim strMail As String

    intCount = Range("C65536").End(xlUp).Row


    For i = intCount To 1 Step -1

    strMail = strMail & ";" & Cells(i, 3).Value

    Next i

    strMail = "mailto:" & Right(strMail, Len(strMail) - 1)

    'ADD WHAT CODE IS NEEDED HERE FOR EMAIL

    End Sub</pre>

    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    Thx works like a charm. I added some search code so it only adds e-mail addresses and no headings etc.

    <pre>Sub MailAll()
    Dim intCount As Integer
    Dim i As Integer
    Dim strMailT As String
    Dim SearchChar, MyPos
    Dim StringLen As Integer
    Dim strMail As String

    intCount = Range("D65536").End(xlUp).Row

    For i = intCount To 1 Step -1
    strMailT = Cells(i, 4).Value
    SearchChar = "@"
    MyPos = InStr(strMailT, SearchChar)
    If MyPos > 0 Then
    strMail = strMail & "; " & Cells(i, 4).Value
    End If

    Next i

    strMail = "mailto:?to=xxx@none.com&bcc=" & Right(strMail, Len(strMail) - 1)

    Worksheets(1).Range("D1").Value = "Send Mail to All"
    With Worksheets(1)
    .Hyperlinks.Add .Range("D1"), strMail
    End With

    End Sub</pre>




    One question remains: How do I actually run the code without using the play button in the VB Editor? Can I create a button somehow? Could it automatically run on start?

    moon

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Create mailto: URL with multiple recipients (2

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Moon

    To add to Jerry's reply, and to answer your question:

    You could add a button to the worksheet that you have this data on. You could use the Forms ToolBar (View | Tool bars | put a check next to Forms) and once you add the button to the worksheet it will ask you if you want to assign a macro to it, and you will specify the code that you wrote.

    I also think that you could use the <font color=blue>CONCATENATE()</font color=blue> function to concatenate all the e-mail addresses separated by a <font color=blue>";"</font color=blue> and use that for your MailTo.

    I did not test this one, but I think it should work as well as the VBA code.

    Hope this helps

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailto: URL with multiple recipients (2

    If you want the macro to be available in all workbooks, put it in your Personal.xls workbook - see Legare Coleman's Personal.xls Tutorial (All).

    For a step-by-step description of creating a toolbar button, see <post:=555,164>post 555,164</post:>.

    The CONCATENATE function won't help here.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    Concatenate didn't help, but I found the Button. It was just the Office 2007 problem not knowing where to look for stuff. It's actually under Developer>Insert>Form Controls.

    I have a rather ugly solution now, creating a temporary hyperlink and just running that, if there is a nicer way i'm up for it. Furthermore My E-Mail program doesn't recognize the BCC part, meaning it only shows up the TO address (I tried &BCC and ?BCC, &Subject works). When changing the BCC to CC, all the addresses show up as usual. I guess its my mail program but I don't have anything else to test now so I'll try it at work.

    <pre>Sub MailAll()
    Dim intCount As Integer
    Dim i As Integer
    Dim strMailT As String
    Dim SearchChar, MyPos
    Dim StringLen As Integer
    Dim strMail As String

    intCount = Range("D65536").End(xlUp).Row

    For i = intCount To 1 Step -1
    strMailT = Cells(i, 4).Value
    SearchChar = "@"
    MyPos = InStr(strMailT, SearchChar)
    If MyPos > 0 Then
    strMail = strMail & ", " & Cells(i, 4).Value
    End If

    Next i

    strMail = "mailto:test@test.com?BCC=" & Right(strMail, Len(strMail) - 2)

    Worksheets(1).Range("D1").Value = " "
    With Worksheets(1)
    .Hyperlinks.Add .Range("D1"), strMail
    .Range("D1").Hyperlinks(1).Follow
    End With

    End Sub</pre>


  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailto: URL with multiple recipients (2

    You don't need to create a hyperlink in a cell. You can use

    ActiveWorkbook.FollowHyperlink strMail

    The BCC problem must be an issue with your e-mail client; it works OK in Outlook.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    That's it. Thanks Hans, as always!

    Problem solved.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    One more thing:

    I split the one sheet up into several sheets "Sheet1, Sheet2" etc. How do i apply the For ... Next statement to all sheets? It's always the same row (E), the formula has to look for the e-mail addresses. So basically the code should now collect e-mail addresses from Sheet1!Row E, Sheet2!Row E...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailto: URL with multiple recipients (2

    Row E? Or column E? Since you used a letter, I'll assume thay you meant the latter. Try the following:

    Sub MailAll()
    Dim wsh As Worksheet
    Dim intCount As Integer
    Dim i As Integer
    Dim strMailT As String
    Dim SearchChar, MyPos
    Dim StringLen As Integer
    Dim strMail As String

    For Each wsh In ActiveWorkbook.Worksheets
    intCount = wsh.Range("E65536").End(xlUp).Row
    For i = intCount To 1 Step -1
    strMailT = wsh.Cells(i, 5).Value
    SearchChar = "@"
    MyPos = InStr(strMailT, SearchChar)
    If MyPos > 0 Then
    strMail = strMail & ", " & strMailT
    End If
    Next i
    Next wsh

    strMail = "mailto:test@test.com?BCC=" & Right(strMail, Len(strMail) - 2)
    ActiveWorkbook.FollowHyperlink strMail
    End Sub

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    Yes column...

    Works like a charm. If I want to only collect the e-mail addresses from certain Worksheets, all I have to do is replace wsh with e.g. Worksheet(2) and delete the sourrounding "For Each wsh In ActiveWorkbook.Worksheets".

    Thx again Hans

  12. #12
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    I miss my Access...

    Actually this whole solution doesn't work, because Excel only allows a certain number of e-mail addresses to be passed on with a Hyperlink. I didn't want to count the number of allowed characters but is is around 256Bit (+/- 50). You can easily try to recreate this problem by just creating a hyperlink manually, and entering test@test.com as e-mail address, then copy n paste - after a couple of e-mail addresses the field does not let you enter anymore, the same if done programmatically.

    Is there a way to circumvent this? Any other solutions? Maybe possible using OLE, DDE?

    moon

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailto: URL with multiple recipients (2

    The maximum length is probably 255 characters (2^8 - 1).
    One option would be to loop through the cells and send an individual e-mail to each recipient.
    Or build the string of recipients until it approaches 255 characters in length, send the e-mail and start building the string anew with the next recipient.
    Or automate Microsoft Outlook - you can add a very large number of recipients that way. See Wendell’s tutorial Automation 101 - it contains an introduction and useful links.

  14. #14
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create mailto: URL with multiple recipients (2

    Hmm and I thought this would be an easy task...

    Working on a System with Outlook is not an option as the client is using Thunderbird.

    Looping through the cells and send an individual e-mail to each recipient & Build string until 255 characters, then anew both have one problem:
    I would either have to type the e-mail every time, or I have to create it in Excel with a text field (possible with using the &body="" string in mailto - here again applies the 255 characters rule when using the Hyperlink...

    One scenario I can think of right now is just copying all e-mail addresses into one cell (temporary) and having the user to copy n paste these into his e-mail program.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailto: URL with multiple recipients (2

    If your client doesn't want to invest in suitable software, you'll have to make do with what you have. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •