Consulting

Results 1 to 3 of 3

Thread: Multiple e-mail recipients in strTo=

  1. #1
    VBAX Newbie
    Joined
    Jul 2020
    Posts
    2
    Location

    Multiple e-mail recipients in strTo=

    I’ve been having fun with a macro that a programmer in our paramedic service created in VBA in Excel but there’s one area that I’m coming up empty after a lot of internet searching for code. The programmer is off on paternity leave and is unavailable to consult with.

    My question is, how can I add ActiveCell.Offset(0, -28).Value & ActiveCell.Offset(0, -27).Value that I currently have in strCc= & strBcc= (they work) beside ActiveCell.Offset(0, -29).Value in strTo= so that all 3 medics are direct recipients of the e-mail? I couldn’t find a way to code the 3 of them together. This is what I have:

    'Build email message
    strTo = ActiveCell.Offset(0, -29).Value
    strCc = ActiveCell.Offset(0, -28).Value
    strBcc = ActiveCell.Offset(0, -27).Value
    strSub = "Referral Update - "
    strCrew1 = ActiveCell.Offset(0, -29).Value
    strCrew2 = ActiveCell.Offset(0, -28).Value
    strCrew3 = ActiveCell.Offset(0, -27).Value

    Thanks for any help anyone can offer.

  2. #2
    In theory you would want

    strTo = ActiveCell.Offset(0, -29).value & ", " & ActiveCell.Offset(0, -28).value & ", " & ActiveCell.Offset(0, -27).value
    strCc = ""
    strBcc = ""
    but you may have trouble with Outlook not validating that as a suitable address. Better to leave the strings as they are and add them to the message separately as recipients e.g.

    With olMail
            .Recipients.Add(strTo).Type = 1
            .Recipients.Add(strCC).Type = 1
            .Recipients.Add(strBcc).Type = 1
            .display
    End With
    You don't then need .To, .CC or .BCC values.
    If you want to hide the recipient details from the other two recipients set .Type = 3 instead of 1 and then they will all go as BCC.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Jul 2020
    Posts
    2
    Location
    Hi gmayor, your first suggestion is what did the trick with just one substitute of semi-colons in place of commas so that Outlook would recognize the string of addresses:

    strTo = ActiveCell.Offset(0, -29).value & "; " & ActiveCell.Offset(0, -28).value & "; " & ActiveCell.Offset(0, -27).value
    strCc = ""
    strBcc = ""

    Thank you very much gmayor for your help! I really appreciate it

Tags for this Thread

Posting Permissions

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