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.
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.
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.
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.
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.
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
- Macro to Populate a Mail Merge Document from Excel
- How to Change Date Format in Excel Mail Merge (with Quick Steps)
- How to Merge Excel File to Mailing Labels (With Easy Steps)
- Mail Merge from Excel to Word Envelopes (2 Easy Methods)
- How to Mail Merge Pictures from Excel to Word (2 Easy Ways)
.