Automate Mail Merge Using Excel VBA


How to automate mail merge using excel VBA. Mail merge is the process in which you can draft a letter in a program like MS Word and then merge the names and addresses of the recipients stored in another program like MS Access or MS Excel with this letter automatically. Also the greeting line is added in such a manner that the recipients feel that this letter has been customized for them. Not only that, if you have a large data of names and addresses then you can filter this data to send letters or emails to a specific group like doctors or people living in a specific area. In the video below, we show how you can automate mail merge using Excel VBA in MS Excel itself without the need for any other software.


Watch the video on YouTube.


Given below is the complete VBA code to automate mail merge using Excel VBA for specific records numbers:
Sub PrintLetters()
Dim StartRow As Integer, EndRow As Integer
Dim Msg As String
Dim totalRecords As String
Dim firstName As String, lastName As String, address1 As String, address2 As String, city As String, state As String, zip As String
totalRecords = “=counta(Data!A:A)”
Range(“E10”) = totalRecords

Dim mydate As Date
‘set wsF=Sheets(“Form”)
mydate = Date
Sheets(“Form”).Range(“A9”) = mydate
Sheets(“Form”).Range(“A9”).NumberFormat = “[$-F800]dddd, mmmm dd,yyyyy”
Sheets(“Form”).Range(“A9”).HorizontalAlignment = xlLeft

StartRow = InputBox(“enter the first record to printt.”)
EndRow = InputBox(“enter the last record to print.”)

If StartRow > EndRow Then
Msg = “ERROR” & vbCrLf & “The starting row must be less than the ending row!”
MsgBox Msg, vbCritical, “Advanced Excel Training”
End If

For i = StartRow To EndRow
firstName = Sheets(“Data”).Cells(i, 1)
lastName = Sheets(“Data”).Cells(i, 2)
address1 = Sheets(“Data”).Cells(i, 3)
address2 = Sheets(“Data”).Cells(i, 4)
city = Sheets(“Data”).Cells(i, 5)
state = Sheets(“Data”).Cells(i, 6)
zip = Sheets(“Data”).Cells(i, 7)

Sheets(“Form”).Range(“A11″) = firstName & ” ” & lastName & vbCrLf & address1 & vbCrLf & address2 & vbCrLf & city & vbCrLf & state & vbCrLf & zip
Sheets(“Form”).Range(“A13”) = “Dear” & ” ” & firstName & “,”

checkbox4 = True
If checkbox4 Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
End If
Next i

End Sub

If you wish to automate the mail merge of filtered records you can use the following VBA code. Here we have automated the mail merge of specific records based on the State in which the recipient resides:

Sub PrintLetters()

Dim StartRow As Integer, EndRow As Integer, rowindex As Integer
Dim Msg As String
Dim i As Integer
Dim totalRecords As String
Dim mydate As Date
Dim firstname As String, lastname As String, address1 As String, address2 As String, city As String, state As String, zip As String
Dim mystate As String
totalRecords = “=counta(Data!A:A)”
Range(“E10”) = totalRecords

mydate = Date
Sheets(“Form”).Range(“A9”) = mydate
Sheets(“Form”).Range(“A9”).NumberFormat = “[$-F800]dddd, mmmm dd, yyyy”
Sheets(“Form”).Range(“A9”).HorizontalAlignment = xlLeft

StartRow = InputBox(“Enter the first record to print.”)
EndRow = InputBox(“Enter the last record to print.”)
mystate = InputBox(“Enter name of state.”)

If StartRow > EndRow Then
Msg = “ERROR” & vbCrLf & “The starting row must be less than the ending row!”
MsgBox Msg, vbCritical, “Advanced Excel Training”
End If

For i = StartRow To EndRow
firstname = Sheets(“Data”).Cells(i, 1)
lastname = Sheets(“Data”).Cells(i, 2)
address1 = Sheets(“Data”).Cells(i, 3)
address2 = Sheets(“Data”).Cells(i, 4)
city = Sheets(“Data”).Cells(i, 5)
state = Sheets(“Data”).Cells(i, 6)
zip = Sheets(“Data”).Cells(i, 7)
Sheets(“Form”).Range(“A11″) = firstname & ” ” & lastname & vbCrLf & address1 & vbCrLf & address2 & vbCrLf & city & vbCrLf & state & vbCrLf & zip
Sheets(“Form”).Range(“A13”) = “Dear” & ” ” & firstname & “, ”

checkbox4 = True
If checkbox4 And state = mystate Then
ActiveSheet.PrintPreview
End If
Next i

End Sub

Attaching a macro to a form button control:
After inserting the form button control via:
1. Developer —> Insert —> Form Controls —> Button (Form Control)

2. Right-click on the form control after editing the button text and follow instructions as shown in the images below:

Attaching Macro to Button Form Control
Attaching Macro to Button Form Control
Assign Macro to Button Form Control
Assign Macro to Button Form Control

Download a sample Excel file:


Further Reading:
Mail Merge – Without Word

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! 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: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Automate Mail Merge Using Excel VBA”

  1. Hello. Your videos are outstanding. 2 questions on the mailmerge w/o word video: I get an error msg saying the checkbox4 is an undefined variable. The second question is how to set the print range so the entire document (and not the buttons) print in a single page. Thank you so much for your generously sharing your skills!

  2. Can you create a script to send bulk mail through outlook. We have a E-mail template in word, addresses (names and email IDs) in excel and accounts in outlook.

    We need to automate this process.

    Hoping for your reply soon.

Leave a Reply

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