May 28, 2018

How to send pesonalized emails automatically

How to send personalized emails automatically using Excel data and Mail Merge in MS Word with VBA.

Sending emails via Excel  and Outlook is easy. But sending personalized emails via Outlook through Excel involves difficult coding. Mostly a temporary workbook is created and later ‘killed’ or deleted to get data from an Excel worksheet into the body of Outlook mail. Even here the data is converted to HTML. In short, the process of sending personalized emails through Outlook using Excel data is cumbersome. It’s better to use the Excel data with the mail Merge facility in MS Word and record a macro or VBA code. This file can be used to automate the email delivery via Outlook even after modifying the Excel data. The process of email delivery is smooth. Watch the video below:

Watch this video on YouTube.

Here’s the complete recorded macro or VBA code:

Sub email_using_excel_data_in_word()

‘ email_using_excel_data_in_word Macro
‘ How to send personalized email messages using Excel data with Mail Merge in MS Word

ActiveDocument.MailMerge.MainDocumentType = wdEMail
ActiveDocument.MailMerge.OpenDataSource Name:= _
“C:\exceltrainingvideos\transfer-data-from-worksheet-to-body-of-email-in-outlook.xlsm” _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:=””, PasswordTemplate:=””, _
WritePasswordDocument:=””, WritePasswordTemplate:=””, Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
“Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\exceltrainingvideos\transfer-data-from-worksheet-to-body-of-email-in-outlook.xlsm;Mode=Read;Extended Properties=””HDR=YES;IMEX=1;””;Jet OLEDB:System database=””””;Jet OLEDB:Registry Path=””””;Jet OL” _
, SQLStatement:=”SELECT * FROM `Sheet1$`”, SQLStatement1:=””, SubType:= _
wdMergeSubTypeAccess
Selection.TypeText Text:=”Dear ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“Last_Name”
Selection.TypeText Text:=”,”
Selection.TypeParagraph
Selection.TypeText Text:=”Your enrolment due date is ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“Date_to_be_enrolled”
Selection.TypeText Text:=”.”
Selection.TypeParagraph
Selection.TypeText Text:=”Ignore if you have initiated action.”
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:=”Regards.”
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:=”Director”
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

Further reading:

Mail Range/Selection in the body of the mail