VBA Code Sending Email to Multiple Recipients with Cell References instead of addresses

[Michael Fowler]

Hi everyone, I need help with this. The relevant part of my VBA Code is as follows, and it's working perfectly when sending to one recipient, with the email successfully going to the email address in cell A3 ...

With OutlookMail
.To = Range("A3").Value
.cc = ""
.BCC = ""
.Subject = "Certification Compliance"

Additionally, I found this code on the web for sending to multiple recipients ...

With OutMail
.To = EmailTo
.CC = "person1@email.com;person2@email.com"
.BCC = ""
.Subject = "RMA #" & Worksheets("RMA").Range("E1")

So, I thought that one of the following two codes would logically follow, but neither is working ...

With OutlookMail
.To = Range("A3;A4;A5").Value
.cc = ""
.BCC = ""
.Subject = "Certification Compliance"

or ...

With OutlookMail
.To = Range("A3").Value;Range("A4").Value;Range("A5").Value
.cc = ""
.BCC = ""
.Subject = "Certification Compliance"

 

[6StringJazzer]

The syntax you are trying to use is invalid. Normally I would use code that adds recipients, but you can do it the following way in one shot. You have to build up the string as though you were typing it into the "To" box.

.To = Range("A3").Value & ";" & Range("A4").Value & ";" & Range("A5").Value

 

[Michael Fowler]

Thank you very much, your code above worked. I'm also interested in your suggestion ... I would use code that adds recipients ... Please elaborate on what you mean. Thanks.

 

[6StringJazzer]

There is really nothing wrong with doing it your way but you have to have all the recipients available at the time this line of code executes. If you are doing a loop or something where you can only get them one at a time, you can use this to add each new recipient:

Public Sub AddRecipientTo(CurMsg As MailItem, NameAddress As String)

   Dim NewRecipient As Recipient
   
   Set NewRecipient = CurMsg.Recipients.Add(NameAddress)
   
   NewRecipient.Type = olTo
   NewRecipient.Resolve

End Sub

 

[Michael Fowler]

Thanks Jeff, if I have any more questions on that, I'll let you know. Thanks again for your solution above, it's much appreciated. Michael

 

[6StringJazzer]

Glad to help!