Macro to Populate a Mail Merge Document from Excel

Get FREE Advanced Excel Exercises with Solutions!

Suppose you have a certain email format and a pile of contact details of people. You have to send an email with the specified format, meaning the header, body, footer everything will remain the same but all you want to do is just extract the contact detail from each individual from a sheet and paste that into a certain place in the email. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to populate a mail merge document from Excel with VBA macro.

Table of Contents hide

Download Workbook

VBA Macro to Populate a Mail Merge Document from Excel

Steps to Create a Button

Assign Macro to the Button

Steps to Create a CheckBox

Conclusion

Download Workbook

You can download the free practice Excel workbook from here.

Mail Merge Document with VBA.xlsm

VBA Macro to Populate a Mail Merge Document from Excel

Look at the following image. We have an email format in the Home sheet in our Excel workbook.

And there is another sheet named Info in our workbook that holds the contact details of some people.

What we want to do is, we want to keep the whole email format the same while sending to multiple people at once, changing only the contact details. For your better understanding, we have specified which part we would like to change while sending emails to multiple people in the following image.

Let’s see how to populate a mail merge with the document from another sheet from Excel with VBA macro. We will simply click a button and Excel will do the rest of the task for us. We will just provide the record numbers from the Info sheet and Excel will automatically populate a mail template for each record by merging contact documents.

To do all that, first we need to create a button and then assign the macro to it.

Steps to Create a Button

To assign a button in our dataset, go to the Developer tab.
From there, click Insert and select Button under Form Controls group.

There will be a plus symbol (+). Drag the symbol to create a button in any size anywhere in your spreadsheet.

Assign Macro to the Button

Once you finished dragging the symbol, there will be a pop-up box named Assign Macro. Write the Macro name that you want the sub-procedure of your VBA code to be defined with. In our case, we name the macro as PrintMailMergeDocument.
Then, click New, as it is a new VBA macro.
Later, click OK.

You will be taken into an auto-generated VBA code window.
Then, copy the following code and paste it into the code window.

Sub PrintMailMergeDocument()
Dim FirstRow As Integer
Dim LastRow As Integer
Dim Text As String
Dim GivenName As String
Dim SurName As String
Dim FirstAddress As String
Dim SecondAddress As String
Dim County As String
Dim Country As String
Dim Code As String
'Dim Records As String
'Records = "=counta(Info!B:B)"
'Range("E9") = Records
Dim iDate As Date
iDate = Date
Sheets("Home").Range("A6") = iDate
Sheets("Home").Range("A6").NumberFormat = "[$-F800]dddd, mmmm dd,yyyyy"
Sheets("Home").Range("A6").HorizontalAlignment = xlLeft
FirstRow = InputBox("Enter the First Record")
LastRow = InputBox("Enter the Last Record")
If FirstRow > LastRow Then
Text = "Error" & vbCrLf & "First Row must be less than the last row!"
End If
For i = FirstRow To LastRow
    GivenName = Sheets("Info").Cells(i, 2)
    SurName = Sheets("Info").Cells(i, 3)
    FirstAddress = Sheets("Info").Cells(i, 4)
    SecondAddress = Sheets("Info").Cells(i, 5)
    County = Sheets("Info").Cells(i, 6)
    Country = Sheets("Info").Cells(i, 7)
    Code = Sheets("Info").Cells(i, 8)
    Sheets("Home").Range("A8") = GivenName & " " & SurName & vbCrLf & FirstAddress & vbCrLf & SecondAddress & vbCrLf & County & vbCrLf & Country & vbCrLf & Code
    Sheets("Home").Range("A10") = "Dear" & " " & GivenName & ","
    CheckBox2 = True
    If CheckBox2 Then
    ActiveSheet.PrintPreview
    Else
        ActiveSheet.PrintOut
    End If
Next i
End Sub

Your code is now ready to run.

Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After successfully executing the code, there will be a pop-up box, asking you to enter the first record stored in the Info sheet that you want to send the email.
Write the record number in the input bar. In our case, we want to populate a mail merge document to the contact stored in row number 6 in our Info sheet from Excel. So, we put 6 in the box.
Later, click OK.

After that, another pop-up box will appear, asking you to enter the last record stored in the Info sheet that you want to send the email.
Write the record number in the input bar. In our case, we want to populate a mail merge document to the contact stored in row number 8 in our Info sheet from Excel. So, we put 8 in the box.
Later, click OK.

You will get a Print Preview of the generated mail merge document. Notice the following image closely. We inserted 6 as our first record; in row 6, the contact details of Roman are there. So, as a first mail merge document, we got the email format that will be suitable for sending to Roman.

Now, if you click on the Close Print Preview option, you will see the contact details of Dean (row number 7) are there as the email document. And if you keep pressing the Close Print Preview, then the contact details of Seth (row number 8, our last inserted record in the input box) are there as the email document (see the gif below).

Now, every time running a macro to populate a mail merge document from excel is kind of a mundane way to get any job done. That’s the reason we planned to create a button to populate a mail merge document in the first place. So that we can just simply click on the button every time we need to merge mail from our worksheet.

We have written the code inside a button control, remember? Now let’s get back to it to modify the appearance of the button.

Double click on the text of the button. It will go into the Edit mode. Then give it any name that you want. We defined our button by Print.
Then, right-click on the button and select Format Control…

From the Format Control pop-up window, modify the button the way you like it. For example, we style the text with Bold font and make the size 14, also with colouring it.
After styling your button, click OK.

Look how appealing our Button looks now in the image shown below!

Now, let’s generate a CheckBox for executing the Print Preview option. The Print Preview option will appear till the CheckBox is checked, otherwise, there will be no preview before printing the email.

Similar Readings

Steps to Create a CheckBox

The steps to create a CheckBox are pretty similar to the steps to create a button.

To generate a checkbox in our dataset, go to the Developer tab.
From there, click Insert and select the CheckBox under the Form Controls group.

Same way as creating the button, there will be a plus symbol (+) as well for creating a CheckBox. Drag the symbol to create a CheckBox in any size anywhere in your spreadsheet.
You can also modify the text of the CheckBox from the edit mode like we shown for the button and resize it if you want. In our case, we named our CheckBox as Preview.

We have finished creating the CheckBox for our dataset. Now let’s get back to the Button.

We created the button to populate a mail merge document from Excel, right? So, button styling processes are optional, you can do it if you want, or you can omit it. The button will still work even if it looks unappealing. Or it won’t? Let’s find it out.

Click on the button and you will see it still works just fine (see the gif below).

VBA Code Explanation

Dim FirstRow As Integer
Dim LastRow As Integer
Dim Text As String
Dim Records As String
Dim GivenName As String
Dim SurName As String
Dim FirstAddress As String
Dim SecondAddress As String
Dim County As String
Dim Country As String
Dim Code As String

Defining the variables.

Dim iDate As Date
iDate = Date

Defining and storing date values.

Sheets("Home").Range("A6") = iDate
Sheets("Home").Range("A6").NumberFormat = "[$-F800]dddd, mmmm dd,yyyyy"
Sheets("Home").Range("A6").HorizontalAlignment = xlLeft

Setting and formatting the worksheet and the range; the date value is stored there.

FirstRow = InputBox("Enter the First Record")
LastRow = InputBox("Enter the Last Record")

Storing the first and last record number; it will be inserted in the input box after running the macro.

If FirstRow > LastRow Then
Text = "Error" & vbCrLf & "First Row must be less than the last row!"
End If

If the first row number is greater than the last row number then there will be an error message.

For i = FirstRow To LastRow
    GivenName = Sheets("Info").Cells(i, 2)
    SurName = Sheets("Info").Cells(i, 3)
    FirstAddress = Sheets("Info").Cells(i, 4)
    SecondAddress = Sheets("Info").Cells(i, 5)
    County = Sheets("Info").Cells(i, 6)
    Country = Sheets("Info").Cells(i, 7)
    Code = Sheets("Info").Cells(i, 8)

Starting the iteration of scanning through the whole dataset of the Info sheet and passing and storing appropriate values in the variables.

Sheets("Home").Range("A8") = GivenName & " " & SurName & vbCrLf & FirstAddress & vbCrLf & SecondAddress & vbCrLf & County & vbCrLf & Country & vbCrLf & Code
Sheets("Home").Range("A10") = "Dear" & " " & GivenName & ","

The values in cells A8 and A10 will be modified and displayed based on the iteration.

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

Keeping the CheckBox checked so that every time when we click on the Print button, there will be a Preview of the email before printing. Otherwise, directly print the email without previewing it before. End the If statement and go to the next iteration.

Conclusion

To conclude, this article showed you how to populate a mail merge document from Excel with VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.