Send Customized Emails

 How to Automatically Send Customized Emails with attachments using Excel Macros

Pre-requisites: Outlook Configured

Note:On the VBA editor Go To Tools/References and select "Microsoft Outlook object <version> library" 

Sample vba code below.

Sub sendCustEmails() Dim objOutlook As Object Set objOutlook = CreateObject("Outlook.Application") Dim objEmail As Object intRow = 2 strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text While (strClientID <> "") Set objEmail = objOutlook.CreateItem(olMailItem) strMailSubject = ThisWorkbook.Sheets("Mail_Details").Range("A2").Text strMailBody = ThisWorkbook.Sheets("Mail_Details").Range("B2").Text strMonth = ThisWorkbook.Sheets("Mail_Details").Range("C2").Text strFolder = "D:\Excel Automation\Auto_Emails\Bills" strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text strFName = ThisWorkbook.Sheets("Client_Data").Range("B" & intRow).Text strLName = ThisWorkbook.Sheets("Client_Data").Range("C" & intRow).Text strEmail = ThisWorkbook.Sheets("Client_Data").Range("D" & intRow).Text strAmount = ThisWorkbook.Sheets("Client_Data").Range("E" & intRow).Text strFile = ThisWorkbook.Sheets("Client_Data").Range("F" & intRow).Text strMailSubject = Replace(strMailSubject, "<ClientID>", strClientID) strMailBody = Replace(strMailBody, "<FirstName>", strFName) strMailBody = Replace(strMailBody, "<LastName>", strLName) strMailBody = Replace(strMailBody, "<MONTH>", strMonth) strMailBody = Replace(strMailBody, "<Amount>", strAmount) With objEmail .To = CStr(strEmail) .Subject = strMailSubject .Body = strMailBody .Attachments.Add strFolder & "\" & strFile .Send End With intRow = intRow + 1 strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text Wend MsgBox "Done" End Sub