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.

Required Mail Data-Mail Merge from Excel to Excel

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


Download Excel Workbook

Incomplete mailing data Workbook

Existing mailing data Workbook


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.

Existing Data

Read More: How to Mail Merge from Excel to Outlook with Attachments (2 Examples)


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.

Required Mail Data-Mail Merge from Excel to Excel

Read More: How to Mail Merge from Excel to Outlook (with Easy Steps)


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.

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

Macro Code-Mail Merge from Excel to Excel

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.

Run

🔺 Instantly, Excel opens up the closed Mail_Data Workbook.

Workbook1 opens

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

Mail merge-Mail Merge from Excel to Excel

Read More: Macro to Populate a Mail Merge Document from Excel


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


Related Articles

.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo