Sending emails to multiple recipients using VBA

Question

I have the following code which allows me to attach a report then send it to one recipient.

How do I send it to more than one address?

I've tried putting the addresses in an array but it gives a "Type Mismatch" error.

Dim strReportName As String
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.Namespace
Dim strTO As String
Dim strCC As String
Dim strMessageBody As String
Dim strSubject As String

Set oLook = CreateObject("Outlook.Application")
'Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

'*********************** USER DEFINED SECTION ************************
strTO = "[email protected]"
strMessageBody = "<---This is an automatically generated email. Please do not respond.---->"
strSubject = "Daily Skip"
'*********************************************************************

With oMail
.To = strTO
 .CC = strCC
 .Body = strMessageBody
 .Subject = strSubject

 .Attachments.Add "C:\Output Reports\SkipLotReport.xlsx"
 .Send
End With

Set oMail = Nothing
Set oLook = Nothing
'Set olns = Nothing


'DB.Close
'tbloutput.Close
'dbLocal.Close
objWorkbook.Close

'Set objmail = Nothing
'Set DB = Nothing
Set tbloutput = Nothing


Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Set tbloutput = Nothing
Set dbLocal = Nothing

Jacquiline

Answer 1

Semicolon-separated e-mail addresses:

strTO = "[email protected];[email protected];[email protected]"

As @HansUp remarked in a comment, if you have your email addresses already in an array, you can use the Join function to convert it to a semicolon-delimited string:

strTO = Join(YourArrayVariable, ";")

Muumuu

Answer 2

strTO is a string.

Use the same format as you would enter manually in the To box.

strTO = "[email protected]; [email protected]"

Not