VBA code to send individual emails with attachments based on cell values

[nathandavies]

Hi All,

I'm trying to create some code to send individual emails from a worksheet, i have inputted all the information into the work sheet (Email Addresses, Attachment Locations, and Subject Lines) just wondering if anyone has any code to create the multiple emails.

Email Address to: Column J & Column K
Subject: Column B & " - " & "Retention Invoice"
Attachment: Column M
Wording In Email will be standard, apart from 1 line which is Column G

if you need any more information please let me know.
ND

[Kenneth Hobs]

It is pretty simple. For you concatenation string for the To field, before to delimit the values with semicolons.

e.g.

'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
'http://www.rondebruin.nl/win/s1/outlook/signature.htm


'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
Sub Main()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim a() As Variant, r As Range, c As Range

Set olApp = New Outlook.Application

Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each c In r
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = c.Value 'Column B
.Subject = "Pin No. " & c.Offset(, 3) 'Column E
.Body = c.Offset(, 1) 'Column C
.Display
'.Send
Exit Sub
End With
Next c

Set olMail = Nothing
Set olApp = Nothing
End Sub