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

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.

Required Mail Data-Mail Merge from Excel to Excel

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.

Existing 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.

Required Mail Data-Mail Merge from Excel to Excel


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.

 

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

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: Run the macro (F5 key or Run > Run Sub/UserForm).

Run

Excel will open the closed Mail_Data workbook and merge the mail credentials as desired.

Workbook1 opens

Return to the Merge_Data workbook to see the changes.

Mail merge-Mail Merge from Excel to Excel

Read More: How to Mail Merge from Excel to Outlook


Download Excel Workbook

You can download the practice workbook from here:

Existing mailing data Workbook


 

Related Articles

<< Go Back To Mail Merge Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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