In this tutorial, we will guide you on how to automate mail merge through VBA in Microsoft Excel. Let’s get them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Example
- Firstly, you need to create a sample data in sheet 1 with 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 and one command button to move in the report sheet.
- Then, you have to create a data in sheet 2 with 2 command buttons , one button to move on the data sheet and second command button is to work for mail merge.
- Now, you need to insert a command button in the worksheet.
- For that, you have to go to Developer tab and then insert Command button from Activexcontrol.
- You need to rename the Command button with the name “Letter”, and now assign the below-mentioned macro.
- In the Excel Worksheet, you need to go to the Developer Tab.
- Then, you need to ** ** select the Visual Basic option under the Code section.
- Now, you have to copy and paste the code given below.
Private Sub Main_data_Click()
Worksheets("Report").Activate
Range("A19").Show
End Sub
- After that, you need to insert the second command button in the Report sheet and assign the macro to move on the first sheet.
- You need to rename the Command button with the name “Data” , and assign the below-mentioned macro.
- Then, you have to copy and paste the code given below.
Private Sub CommandButton2_Click()
Worksheets("Main_Data").Activate
Range("A1").Show
End Sub
- Now, you need to write the main code for mail merge as given below.
- After that, you have to insert the command button and rename it as “Letter Print” , and then assign the below-mentioned code.
- You need to copy and paste the code given below.
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") = mydat
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
- Now, you need to save the code by selecting it and then close the window.
- Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
- Then, you need to choose the Macros option in the Code section.
- Now, you have to make sure that your macro name is selected and click the *Run * button.
- After that, you have to enter the first record point , and you will get a new message box to enter the last record of point.
- Finally, letter will get updated according to the mentioned details in main data.
Closure
We hope that this tutorial gives you guidelines on how to automate mail merge through VBA in Microsoft Excel. Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!
Keep Reading:
Top comments (0)