Excel 2013 VBA multiple email addresses to Outlook

Question

I am coding in Excel 2013. I've got a table of client data that will increase and decrease in number over time depending my active veteran caseload:

Column A - Last Name
Column B - First Name
Column C - Email Address
Column D - etc...

I need the code to reference column C and place all the emails therein in the BCC of a single Outlook email. The code I have created (through my study) allows for only hard-coded email addresses to the TO, CC or BCC fields of Outlook--with a semicolon between multiple entries. My issue is that the number of email addresses will vary depending on the number of records in the spreadsheet so hard-coding them is useless. The code below has all the functionality I require with the exception of the email problem.

Sub SendBasicEmail()
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "<h3>Testing</h3><br>" & "<br>" & .HTMLBody
    .Attachments.Add "xxx/test.pdf"
    .To = ""
    .BCC = ""
    .Subject = "Test Message"
    '.Send
End With 
End Sub

Lowpar Lowpar · Accepted Answer · 2017-07-07T21:03:07

The code will loop through the contents of sheet 1 (just change to sheet("whateveryoucalledyoursheet")) and keep the cell row.

Sub SendBasicEmail()
dim ws as worksheet, y
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
set ws = sheets(1)
for each y in ws.range("A1:A" & ws.range("A1").SpecialCells(xlCellTypeLastCell).row)

Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "<h3>Testing</h3><br>" & "<br>" & .HTMLBody
    .Attachments.Add "xxx/test.pdf"
    .To = ws.range("A" & y.row)
    .BCC = ws.range("C" & y.row)
    .Subject = "Test Message"
    ' use display to check the email out before you send
    .display
    '.Send
End With
next y

end sub

Excel 2013 VBA multiple email addresses to Outlook

2 Answers