Mail Merge in Excel: Purposes, Procedure, Uses & Limitations

Mail Merge is a feature in Microsoft Word that enables you to add personal details like name and address from a database (Like Excel, SQL Server Database, or Microsoft Access) to multiple letters, and create thousands of Labels with just a few clicks. You can use this feature to create personalized letters, emails, labels, etc.

In this Excel tutorial, we have discussed and shown everything (i.e., Definitions, Purposes, Procedure, Uses & Limitations) about Mail Merge in Excel.

As you can see in the image above, we have imported the dataset from Excel to Word using the Mailing tab. Then we called the Field (Title, Name, Address, and Country) into our personalized email for each person in the Excel database. Lastly, we have sent this custom email to each client to their email address from Outlook. That’s how you can use Mail Merge to send multiple customized emails automatically.

⏷Mail Merge in Excel
⏷Purposes of Mail Merges in Excel?
⏷Benefits of Mail Merge
⏷Main Components for Doing Mail Merge
⏷Prepare Excel Worksheet for Mail Merge
⏷How to Do Mail Merge from Excel to Word
   ⏵1st Step: Composing Email Template in Word
   ⏵2nd Step: Connecting with Data Source & Editing Mailing List
   ⏵3rd Step: Inserting Address and Greeting Line on Each Message
   ⏵4th Step: Checking Preview & Finishing Mail Merge
   ⏵5th Step: Formatting Mail Merge Fields (Optional)
   ⏵6th Step: Mail Merge from Excel to Outlook with Attachment
   ⏵7th Step: Saving Mail Merge for Next Bulk Mailing
⏷“Step-by-Step Mail Merge Wizard”
⏷Mail Merge Labels from Excel
⏷Useful Keyboard Shortcuts
⏷Limitations of Excel Mail Merge
⏷Alternatives to Excel Mail Merge
⏷Solve Possible Issues with Mail Merge

In this blog post, you will learn about:

• Mail Merge.
• Purposes of Mail Merge.
• Main Components of Mail Merge.
• Preparing Excel Worksheet for Mail Merge.
• Doing Mail Merge (Sending Email Automatically with Personalized Message.)
• “Step by Step Mail Merge Wizard.”
• Making Labels with Mail Merge.
• Some useful keyboard shortcuts, Limitations, Issues, and Solutions for Mail Merge.

What Is Mail Merge in Excel?

Mail Merge is a feature that lets you personalize and send the same document, like emails or letters, to multiple recipients. It’s useful when you want a consistent message but with individual details for each recipient.

For instance, imagine you’re a Shop owner, and you want to send a special discount offer to each one of your customers. Instead of typing out each offer individually, you can use Mail Merge to send that email to each customer with their name on it.

Softwares that supports mail merge:

• Microsoft Word
• Microsoft Excel
• Google Docs
• OpenOffice Writer
• LibreOffice Writer

What Are the Purposes of Mail Merges in Excel?

Mail Merge serves several useful purposes. For example, you can use Mail Merge to:

• Send personalized emails to a list of clients for a product update.
• Create customized promotional letters for a targeted audience.
• Generate personalized invoices by merging Excel data.
• Compile individual sales reports for team members.
• Send personalized invitations and RSVPs for an event.
• Distribute personalized internal memos or updates.
• Sending personalized survey links to gather feedback.
• Create address labels for holiday card mailings.
• Generate customized progress reports for students.

What Are the Benefits of Mail Merge?

You can consider using the Mail Merge feature because Mail Merge can:

• Automate the process of creating and sending personalized messages, which can save you a lot of time and effort.
• Help you create professional-looking documents and messages.
• Assist in reducing errors by automatically inserting the correct information into your messages.
• Support you in tracking the results of your marketing campaigns and other communications.

Additionally, personalized messages are more likely to get a response than generic messages.

What Are the Main Components for Doing Mail Merge from Excel to Word?

The process Mail Merge contains four basic components Data Source, Word Documents, Merge Fields, and  Mail Merge Wizard.

How to Prepare Excel Worksheet for Mail Merge?

If you want to make an efficient and functional Mail Merge, you have to:

• Make sure each column should match the field names intended for the mail merge, such as first and last names.
• Confirm there are no blank rows or columns, and avoid unnecessary formatting like special characters.
• Ensure percentages, currencies, and postal codes are appropriately formatted for accurate reading in Word. Because Consistent formatting prevents errors during the mail merge process.
• Keep all data for merging on the first sheet of your spreadsheet.
• Store the Excel file in your local machine (i.e., PC, Laptop).
• Save as .xlsx or .csv for compatibility with mail merge tools.

Additional Tips:

• Use a consistent naming convention for your columns and rows.
• Utilize conditional formatting to highlight important information or errors.
• Add a column to identify duplicate records.
• Back up your data regularly.

How to Do Mail Merge from Excel to Word?

In this section, you will learn to use Mail Merge to send customized email. As we are talking previously, suppose you are a super shop owner and you want to send email with a discount message with customers name on it to each one. Do you know it will take forever if you want to do it manually, but in this case you can use Mail Merge feature to do the same with a few clicks.

So, you have an Excel worksheet with details (i.e., Name, Address, Country, Phone, Email, etc.) of the customers of your shop as shown in the figure below.

Now, we will send a customized greetings message to each email address. To send this customized message to each customer with Mail Merge:

1st Step: Composing Email Template in Word

To begin, we will write our email message in Microsoft Word. So, you have to:

• Open Microsoft Word.> Click on Blank Document.> “Save As”> Browse (locate the folder where have saved the Excel file about Customer details”.> Save.

• Click on Mailings in the Mailings > Start Mail Merge.> E-mail Messages.

• Write the message according to your preference and based on your business. We are writing a message that offers discounts to the customers.
• Save the File

2nd Step: Connecting with Data Source & Editing Mailing List

We will connect the Excel worksheet to Word. To establish the connection:

• Click on Mailings.> Select Recipients.> “Use an Existing List.”

• Locate and select the file in the “Select Data Source” dialogue box. (i.e., Client Details) > Click on Open.

• Select the worksheet in the workbook where we have stored customers’ details (i.e., Personal Details).> Click on OK.

We have decided not to send any message to “Alice Lee”, and “Emma Brown”. So, to do this:

• Click on Mailings.> Edit Recipients List.> Uncheck the names “Alice Lee”, and “Emma Brown” in the “Mail Merge Recipients” dialogue box.

If you want to send the message to all, you can skip this step.

3rd Step: Inserting Address and Greeting Line on Each Message

Now, we will use the Mail Merge to add the imported fields to our message.  We can do this in two ways Using “Insert Merge Field” or “Address Block and Greeting Line”. But don’t worry, both return the same result. You can use any of these methods.

Using “Insert Merge Field”

To add Title from the imported Excel sheet in Microsoft Word using “Insert Merge Field”:

• Click on Mailings.> Insert Merge Field.> Title.

This will add the “Title” field in the Word document.

We have repeated the steps above to add other fields Name, Address, Country in the mail text

Using Address Block and Greeting Line

We can also add these fields using the Address block and greeting line, so we have the message as below. Have a look, to use this feature we don’t need to type greeting message by yourself (i.e., Dear X). Mail merge will do it for you.

To add address to the top,

Place your cursor at the place you want to add the address.> Click on Mailings.> “Address Block” in the “Write& Insert Fields” Options.

It will show you a dialogue box, how your address going to look like.

• Check the Preview at the right portion of the dialogue box.> Just click on OK.

This will add the “Address Block” as shown.

Now we will add the Greeting Line. To add it:

• Click on Mailings.> Greeting Line.

It is using “Dear” as a greeting line, you can change it as you like.

• See the Preview of the Greeting Line.> Click on OK.

Now, this will add the Greeting Line in the Message.

4th Step: Checking Preview & Finishing Mail Merge

Now, we will check what our email message looks like when the customer receives it. To see the full preview:

• Click on Mailings.> Preview Result.> Click on the Icon to See the Next Message.

This will show the message as shown below. This message is for “John Doe” and the Greeting line for him is “Dear John Doe”.

You can see the next message for “Jane Smith” by clicking on the icon.

5th Step: Formatting Mail Merge Fields (Optional)

If any of your fields contains time, date, number, or percentage, we have to format the field in the Mail Merge feature.

Suppose we have a Date field in mail merge, to format this Date  Merge Field in your template document, follow these steps:

a) Display the Field Code:

• Select the Merge Field (i.e., Name, Date, etc.), then press Shift + F9.

This will show the Mail Merge Field Item code for formatting.

b) Modify the Field Code:

• Within the displayed field code, remove “*MERGEFORMAT Date.”
• Enter your desired switch in the field code. For example:

{MERGEFIELD Date \@ “d MMMM yyyy”}

c) Apply the Update:

• To apply the changes to the field, press F9.

This will show the date format as 07 April 2003.

By following these steps, you can precisely format your Merge Fields, ensuring the desired appearance in your final document.

We are providing a Field Code table that you can use for your Mail Merge.

6th Step: Mail Merge from Excel to Outlook with Attachment

Now, our message is ready and we will send an email to the individual customer. To send email from Mail Merge:

• Click on Mailings.> Finish & Merge.> “Send Email Messages..”

• Type “A Token of Gratitude: 10% Discount.” in the “Subject Line” box from “Merge to E-Mail” dialogue box.> Click on OK.

This will upload all the messages to your Outlook app in the Outbox folder like in the image below. It should send these messages automatically from this app.

If it doesn’t send messages automatically, to send it:

• Go to Outlook app.> Click on Send/Receive.> Send All.

You can check the sent mail from the “Sent Items” folder in Outlook.

So, that’s how we sent a customized email with Mail Merge.

7th Step: Saving Mail Merge for Next Bulk Mailing

Now, save the Mail Merge Word document to any folder on your PC. You can send bulk mail to these customers with any message next time. Just follow the steps for the next mailing.

How to Use the “Step-by-Step Mail Merge Wizard”?

Did you know we can use “Step by Step Mail Merge Wizard” to send bulk mail messages to the customer? It is as same as the seven methods above.

We can do the same using this wizard. To enable this wizard:

• Click on Mailings.> “Start Mail Merge.”> “Step by Step Mail Merge Wizard.”

This will enable the wizard on the right side of the documents. To start Mail Merge:

• Click on E-mail messages.> “Next: Starting Documents”.

So, you can perform the same task (as we have done in the steps above) using the wizard. So to avoid redundancy we are not showing those same steps in this wizard.

How to Mail Merge Labels from Excel?

You can also create Labels using Mail Merge feature. A label is a piece of material, such as paper, plastic, cloth, or metal, that is attached to an object and provides information about it. This information can include a variety of details, such as the name of the object, Instructions for use, and Address.

Now, imagine a situation where the shop owner wants to send ordered items from his store to the customer. After packaging the ordered item, he will need an Address Label for each customer to send it to them via courier service. We will use the Mail Merge feature to produce the Label for each customer. The owner has the details about the customer as the image below.

To start creating a Label for each customer:

• Open Microsoft Word.> Click on Blank Document.> “Save As”> Browse (locate the folder where have saved the Excel file about Customer details”.> Save.
• Click on Mailings.> “Start Mail Merge.”> Labels.

We will use Avery 5160 Address Labels to create our desired Label for the Address.

• Select “Avery US Letter” in the “Label vendors”> “5160 Address Labels as Product Number.” > OK.

This will create a borderless table. Now,

• Click on the Plus icon at the top left corner.> Table Design.> Borders.

This will show the borders of each cell in the table as it is in the table below.

We will connect the Excel worksheet to Word. To establish the connection:

• Click on Mailings.> Select Recipients.> “Use an Existing List.”
• Locate and select the file in the “Select Data Source” dialogue box. (i.e., Client Details) > Click on Open.
• Select the worksheet in the workbook where we have stored customers details (i.e., Details for Label).> Click on OK.

You will see <<Next Record>> in each cell after the step above. It means It successfully imported the data field.

• Click on Mailings.> Address Block.

As you can see, in the Preview section of the dialogue box, it is not showing street number. But we must add it. For that:

• Click on “Match Fields…” in the Insert Block Address dialogue box.

• Click on the box of Address 1.>  OK.

Now the Preview will show the Street number in the address.

• Click on OK to continue.

This will add the Address Block in the first cell of the table. But we have to add this to every cell.

To add this to every cell:

• Click on Mailings.> Update Labels.

This will add it to each cell of the table.

Now, click on Preview Results to see the real preview of each Label.

To print the Labels:

• Click on Mailings.>Finish and Merge.> Print Documents.

• Select All.> Click on OK in the “Merge to Printer” dialogue box.

Finally set up the Printing set-up and environment to print the documents. That’s how you create and print Labels with Mail Merge feature in Excel.

Useful Keyboard Shortcuts for Mail Merge Procedures

Here are some keyboard shortcuts you can use while doing Mail Merge.

What Are the Limitations of Excel Mail Merge?

There are some basic limitations of Mail Merge. Like You cannot-

• See who opened or clicked your emails.
• Attach files like PDFs or videos to your emails.
• Properly manage unsubscribe requests, this will increase the risk of being marked as spam.

Alternatives to Excel Mail Merge

These are the top efficient alternatives to Mail Merge in Excel that you can use. Some of them come with a premium version.

• Mail Merge for Gmail: Easy Gmail add-on for sending 1,500 personalized emails. A free plan is available, but branding is in the free version.
• Yet Another Mail Merge (YAMM): Simple Gmail tool, that is good for surveys but may be tricky for beginners. Free plan with limited analytics.
• Right Inbox: Gmail tool with reminders and recurring emails. Starts at $7.95/month, and a free plan limited to 5 emails monthly.
• MailShake: Good for marketing, integrates widely but might be complicated. Starts at $58/month.
• Mailmeteor: Gmail tool with templates, easy to use, starts at $9.99/month. Free plan for 50 emails/day.

How to Solve Possible Issues with Mail Merge?

You might face some issues with Data Source, Merge Field, or Sending Email. Here could be the possible issues with Mail Merge and Solutions of the issues.

a) Data Source Issues:
Error Message: “Word cannot find the data source.”
Solution:

• Ensure the data source file is in the same folder as your Word document.
• Verify the correct file path in the Mailings tab.
• Check if the data source file is open elsewhere; close it and retry.
• If using Excel, save it as .xlsx or .csv.
• Confirm permission to access the data source file.

b) Merge Field Issues:
Problem: Merged fields missing or displaying incorrect data.
Solution:

• Verify that the merge fields are correctly inserted in your document.
• Check if the merge field names match the column headers in your data source.
• Ensure you are using the correct merge field type (e.g., text, number, date).
• If using nested merge fields, check for errors in the nested merge field code.

c) Email Sending Issues:
Problem: Emails are not being sent or are sent with errors.
Solution:

• Ensure you are connected to the internet.
• Check your email settings in Word and confirm you have configured the correct email account.
• Verify that your email server is not experiencing any outages.
• If using an add-on for sending email through mail merge, check for updates or contact the add-on developer for assistance.

Hopefully, this will resolve the issue you are facing or might face.

Download Practice Workbooks

You can download the workbook & doc files that we’ve used to prepare this article.

In this guide, we have covered Definitions, Purposes, and Main Components of Mail Merge in Excel. Also discussed Preparing an Excel Worksheet for Mail Merge, Doing Mail Merge (Sending Email Automatically), “Step by step-by-step Mail Merge Wizard.”, Making Labels with Mail Merge, and Some useful keyboard shortcuts, Limitations, Issues, and Solutions for Mail Merge.

Hopefully, this article was helpful and easy to understand those methods. Yet, if you have any problem, please drop your comments below.

Mail Merge Excel: Knowledge Hub

• How to Format Address Labels in Excel
• How to Mail Merge from Excel to Outlook with Attachments
• How to Copy and Paste Excel Table into Outlook Email
• Creating a Mailing List in Excel
• Mail Merge from Excel to Excel
• How to Mail Merge from Excel to Powerpoint
• How to Make Address Labels in Word from Excel
• How to Mail Merge from Excel to Word Envelopes
• How to Mail Merge Pictures from Excel to Word
• Mail Merge in Excel without Word
• How to Insert Merge Fields in Word from Excel
• [Solved]: Word Mail Merge Not Working with Excel
• Macro to Populate a Mail Merge Document from Excel

<< Go Back To Learn Excel