Mail Merge from Excel to Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


Mail Merge from Excel to Excel: with Easy Steps

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: Creating a Mailing List in Excel


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


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: How to Mail Merge from Excel to Outlook


Download Excel Workbook

Incomplete mailing data Workbook

Existing mailing data Workbook


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

<< Go Back To Mail Merge Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo