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:= _
Selection.TypeText Text:=”Dear ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
Selection.TypeText Text:=”,”
Selection.TypeText Text:=”Your enrolment due date is ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
Selection.TypeText Text:=”.”
Selection.TypeText Text:=”Ignore if you have initiated action.”
Selection.TypeText Text:=”Regards.”
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


Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

Leave a Reply

Your email address will not be published. Required fields are marked *