Multiple e-mail recipients in strTo=

[medic]

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.

[gmayor]

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.

[medic]

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 :)