How to Automate Mail Merge through VBA in Microsoft Excel?
Tags: toautomatemailmerge, vbamacros
Published: 2021-10-22 10:49:19

---
title: How to Automate Mail Merge through VBA in Microsoft Excel?
published: true
date: 2021-10-21 12:06:29 UTC
tags: ToAutomateMailMerge,VBAMacros
canonical_url: https://geekexcel.com/how-to-automate-mail-merge-through-vba-in-microsoft-excel/
---

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](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.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Sample-data-38-1024x307.png)<figcaption>Sample data</figcaption>

- 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**.

![Insert Command Button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/09/Insert-Command-Button.png)<figcaption>Insert Command Button</figcaption>

- 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.

![Select Visual Basic](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- 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**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Save-the-Code-70-1024x735.png)<figcaption>Save the Code</figcaption>

- 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.

![Choose Macro option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- 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](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Keep Reading:**

- **[How To Mail the selection using VBA in Microsoft Excel?](https://geekexcel.com/how-to-mail-the-selection-using-vba-in-microsoft-excel/)**
- **[How To Mail Sheet using Array VBA in Microsoft Excel?](https://geekexcel.com/how-to-mail-sheet-using-array-vba-in-microsoft-excel/)**
- **[Mail every Worksheet with address in cell A1 using VBA in Excel](https://geekexcel.com/how-to-mail-every-worksheet-with-address-in-cell-a1-using-vba-in-excel/)**
- **[How to Merge Cells in Microsoft Excel 2007?](https://geekexcel.com/how-to-merge-cells-in-microsoft-excel-2007/)**
- **[Merge Cells in Microsoft Excel 2013 – {Different ways}](https://geekexcel.com/merge-cells-in-microsoft-excel-2013-different-ways/)**