Mail Merge from Excel to Excel (with Easy Steps)

Often, users have one or two customers’ mailing credentials in an Excel file. Therefore, they need to fetch other credentials matching the existing data in the active file. In those cases, they try to mail merge from Excel to Excel. Excel VBA Macro can achieve mail merge with ease.

Let’s say a user is working on an Excel Workbook that has only one mailing credential (i.e., Email Address). The user has another file where he has all the other needed mailing credentials for the active Workbook entries.

In this article, we demonstrate a VBA Macro to mail merge from Excel to Excel.

Table of Contents hide

Download Excel Workbook

VBA Macro to Mail Merge from Excel to Excel

Existing Mail Data in an Excel Workbook

Required Mail Data in Another Workbook

VBA Macro to Mail Merge from Excel to Excel

Conclusion

Download Excel Workbook

Incomplete mailing data Workbook

Mail Merge.xlsm

Existing mailing data Workbook

Mail Data.xlsx

VBA Macro to Mail Merge from Excel to Excel

As we mentioned earlier, we have to fetch the matched mailing credentials of the existing entries from another workbook. Normally, a simple file merging does the job. But then we have to find each mailing address we were about to send an Email to. So, merging two Excel Workbooks will nullify the entire purpose. Go through the below section to further understand the situation and the way to deal with it.

Existing Mail Data in an Excel Workbook

Suppose the exciting mailing credentials we have in an Excel Workbook may look like the below image. Also, the data may have hundreds of rows and tens of columns. For presentation purposes, we take a couple of rows with a handful of columns. And we named this workbook Mail_Data with a single sheet depicted as Mail Data.

Required Mail Data in Another Workbook

Now, we have another Excel Workbook where only 4 or 5 email addresses are existing. And we want to merge the data with our previously existing workbook to fill all the required blank cells automatically.

VBA Macro to Mail Merge from Excel to Excel

So, as we want to merge our active workbook with the existing workbook, we have to use a VBA Macro code.

Step 1: Use ALT+F11 or go to Developer tab > Visual Basic to open the Microsoft Visual Basic window. In the window, Click on Insert > Module to insert a Module.

Step 2: Paste the following macro in 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

➤ From the above image, the numbered sections define

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

2 – assign the existing workbook path in the device. Also, deliver the variable names and the column number.

3 – open the previously existing workbook using Workbooks.Open command.

4 – assign certain ranges to different workbooks and their sheets.

5 – execute a nested VBA FOR loop to merge data in case the given condition gets satisfied by a VBA IF statement.

Step 3: After inserting the code, use the F5 key or click Run > Run Sub/UserForm to run the macro.

🔺 Instantly, Excel opens up the closed Mail_Data Workbook.

🔺 Return to the Merge_Data workbook to see the changes. You see Excel merged the mail credentials of the existing entries as we desired.

Conclusion

In this article, we demonstrate a VBA Macro Code to mail merge from Excel to Excel. We hope, this method does the job you seek. Comment if you have further inquiries or have anything to add