In this article, we are going to learn how to automate the mail merge by using the VBA in Microsoft Excel.
Mail Merge: - This is the source to merge the data’s information into text and then print the document. To perform such operation, we use Microsoft Word.
Let’s understand with a simple exercise:-
We have a letter format in Report sheet, and we want to apply mail merge through VBA in which we want to change the letter details as well.
We have 2 sheets. 1 sheet contains data with details to whom we want to give letters. In first data, column A contains Name, column B contains street address, column C contains city, column D region, and column E and column F contain postal zip. There is one command button to move in the report sheet.
2nd sheet is having the letter format with 2 command buttons; one button to move on the data sheet and second command button is to work for mail merge
Firstly, we will write the VBA code for command button of Main Data. We need to follow below given steps:-
Private Sub Main_data_Click()
Worksheets("Report").Activate
Range("A19").Show
End Sub
Now, we will insert the second command button in the Report sheet and assign the macro to move on the first sheet. We need to follow below given steps:-
Private Sub CommandButton2_Click()
Worksheets("Main_Data").Activate
Range("A1").Show
End Sub
Now we will write the main code for mail merge by following below given steps:-
Insert the command button and rename it as “Letter Print”, and then assign the below mentioned code:-
Private Sub CommandButton1_Click()
Dim StartrowAs Integer, lastrow As Integer
Dim MsgAs String
Dim TotalrecordsAs String
Dim name As String, Street_AddressAs String, city As String, region As String, country As String, postal As String
Totalrecords = "=counta(Main_Data!A:A)"
Range("L1") = Totalrecords
Dim mydate As Date
Set WRP = Sheets("Report")
mydate = Date
WRP.Range("A9") = mydate
WRP.Range("A9").NumberFormat = "[$-F800]dddd,mmmm,dd,yyyy"
WRP.Range("A9").HorizontalAlignment = xlLeft
Startrow = InputBox("Enter the first record to print.")
lastrow = InputBox("Enter the last record to print.")
If Startrow>lastrow Then
Msg = "ERROR" &vbCrLf& "Starting row must be less than last row"
Msgbox Msg, vbCritical, "ExcelTip"
End If
For i = Startrow To lastrow
name = Sheets("Main_data").Cells(i, 1)
Street_Address = Sheets("Main_data").Cells(i, 2)
city = Sheets("Main_data").Cells(i, 3)
region = Sheets("Main_data").Cells(i, 4)
country = Sheets("Main_data").Cells(i, 5)
postal = Sheets("Main_data").Cells(i, 6)
Sheets("Report").Range("A7") = name &vbCrLf&Street_Address&vbCrLf& city & region & country &vbCrLf& postal
Sheets("Report").Range("A11") = "Dear" & " " & name & ","
CheckBox1 = True
If CheckBox1 Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
End If
Next i
End Sub
Code Explanation: - First, we will define the variables then we will define the date and date format, then we will define the last row and start row. Then we have created message box for transmitting the message. Then we will define the data and range that we want to capture in letter.
This is the way we can automate mail merge through VBA in Microsoft Excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
In addition to code, how do you save all letters in a single pdf?
I want to mail merge and create one document containing all the letters in it and then save that file with .xls formate. how to do that?
give me a file of this article. Thank you
n/a
Please send this file.
Cool!! By the way, If some record has two row, how do I Mail merge as one record?