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

2 thoughts on “Automate Mail Merge Using Excel VBA

  1. Scott Albert

    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!

    Reply
  2. Aziz

    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.

    Reply

Leave a Reply

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