How to do a Mail Merge from Excel to Excel (Easy Steps)

Scenario

Often, users have a few customer mailing credentials in an Excel file and need to fetch additional credentials that match existing data in the active file. In such cases, they attempt to perform a mail merge from one Excel file to another. Excel VBA macros can simplify this process.

Let’s consider a scenario: You’re working on an Excel workbook with only one mailing credential (email address), while another file contains all the other necessary mailing credentials for the active workbook entries.

Here are the steps to achieve mail merge from Excel to Excel:

1. Existing Mail Data in an Excel Workbook

Suppose you have mailing credentials stored in an Excel workbook, as shown in the example image. The data may have hundreds of rows and multiple columns.
For presentation purposes, let’s focus on a smaller subset of rows and columns. We’ll call this workbook Mail_Data.

Read More: Creating a Mailing List in Excel

2. Required Mail Data in Another Workbook

You also have another Excel workbook where only 4 or 5 email addresses exist.
Your goal is to merge this data with the Existing Workbook, filling in the necessary blank cells automatically.

3. VBA Macro for Mail Merge

To merge the active workbook with the existing one, use a VBA Macro code.
Follow these steps:

- Step 1: Open the Microsoft Visual Basic window using ALT+F11 or by going to the Developer tab > Visual Basic. Insert a new Module.

- Step 2: Paste the provided VBA Macro code into the Module.

Sub Mail_Merge_From_Excel_to_Excel()
Book1_Path = "C:\Users\maruf\Desktop\Softeko\Mail Merge from Excel to Excel\Mail_Data.xlsx"
Book1_Name = "Mail_Data"
Book2_Name = "Mail_Merge"
Sheet1_Name = "Mail Data"
Sheet2_Name = "Mail Merge"
No_of_Columns = 9
Workbooks.Open Book1_Path
Set Rng1 = Workbooks(Book1_Name).Worksheets(Sheet1_Name).UsedRange
Set Rng2 = Workbooks(Book2_Name).Worksheets(Sheet2_Name).UsedRange
For i = 1 To Rng2.Rows.Count
For j = 1 To Rng1.Rows.Count
If Rng1.Cells(j, No_of_Columns) = Rng2.Cells(i, No_of_Columns) Then
For k = 1 To No_of_Columns - 1
Rng2.Cells(i, k) = Rng1.Cells(j, k)
Next k
Exit For
End If
Next j
Next i
End Sub