fbpx
Search
Close this search box.

Use mail merge to create custom letters, invoices, labels and more

Share

Facebook
Twitter
LinkedIn

The idea of mail merge is simple & powerful. Imagine you handle recruitment at a large company. You just hired 300 analysts for the big data division. The next job – generate employment offer letters for each of them. Of course, you don’t want to type these letters one at a time. You have the details of all the 300 offers (name, email, address, offer code, role, benefit package details and date of joining etc.) in a spreadsheet. You also have a template of the offer letter.

Enter mail merge. This powerful feature combines MS Word documents with (Excel) data to generate all the 300 letters in just few minutes.

introduction-to-mail-merge-excel-word

You can use mail merge in many similar situations – like generating invoices, address labels, certificates etc.

Read the below tutorial to learn how to set up mail merge in Word.

Tutorial – using mail merge to generate invoices

For this tutorial, let’s pick the example of generating invoices.

Step 1 – Create a workbook with your data

Let’s say your invoice has 11 fields, as shown below.

fields-for-mailmerge

Create a workbook with 11 columns and load data as shown below.

sample-data-invoice-generation-mail-merge

Step 2 – Create an invoice template in MS Word

Open a new document in MS Word and create a document structure that reflects your invoice. You may download the invoice template for inspiration.

At this stage, our invoice looks like this:

mail-merge-template-blank

Step 3 – Activate Mail Merge

mailings-ribbon-word

Go to Mailings ribbon in MS Word and click on “Start mail merge” button. Choose the document type that best describes what you are doing (for invoices, you can choose either letter or normal word document)

mail-merge-from-regular-word-document

This activates mail merge mode on your document.

Step 4 – Load data

get-data-from-external-source

Now, we need to load the field data. Click on “Select recipients” and choose “Use an existing list”. This opens File>Open dialog. Navigate to the folder where you saved Excel workbook with invoice data and select the file.

Step 5 – Insert fields at right places

Now that we have a list of fields, load them at necessary location in the invoice template using the “Insert Merge Field” button.

Pro tip: You can use Rules option to set up If then rules based on field values. (for example, if the payment reference # is empty, you can show different text)

At this stage, our invoice looks like this:

mail-merge-template

Step 6 – Preview & complete mail merge

previewing-mail-merge

Using the preview results area, check if everything is ok. Once you are ready, click on “Finish & Merge” to generate individual word documents or emails or prints of the invoices.

Pro tip: If you have a PDF printer, you can use that to generate PDFs for each invoice automatically.

Download Mail Merge tutorial files

There are 2 files in this tutorial – Excel workbook with invoice data & Word document with mail merge setup.

After downloading the files: 

  1. Place them in the same folder.
  2. Open the Word document.
  3. At this point, you should get an error. Click ok.
  4. This will open Data link properties dialog.
  5. Type the full path of the downloaded Excel workbook in the Data source field. Click ok (see image).
  6. This should establish the connection for you.

Do you mail merge?

Mail merge is a very powerful and time saving feature. I use it often when I need to generate a lot of documents in one go.

What about you? Do you use mail merge? Please share your experiences & tips in the comments section.

Other ways to generate documents in Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

16 Responses to “Use mail merge to create custom letters, invoices, labels and more”

  1. Lisa says:

    I have a monthly report where the wording is the same but the data is different. I have a page for the data and then a page that is set up with the data for that month. The mail merge takes that second page and instantly creates my document. It is so much easier than before hurting my neck looking at two documents and hoping I saw the right line.

  2. Candy says:

    This is super useful, thanks for the tip!

    I was wondering, if we're trying to send all these as email messages, will we be able to set up the Mail Merge to also send out the individualised letter to each individual email address?

    • Candy says:

      Ah, it's ok, I've played with it, and yes, it's possible! This is a wonderful function, wish I knew about this much earlier on.

      • scipion says:

        .. well, and how you do it ? would be useful to share.

        • Candy says:

          You just need an additional column for the email addresses. Thereafter, click on "Finish & Merge", and choose "Send E-mail Messages". Use that column you created for the email addresses, and put it under the "To" field.

  3. excel says:

    This is very useful to send enquiries / RFQs.
    Thereby people are no more require to maintain a separate table for
    response time analysis/ offer tracking.
    Its awesome... doing two work at one time, RFQ creation plus tracking of the same.
    And the data can now give you a whole new world for analyzing and decision making. Awesome.

  4. scipion says:

    Thanks - very helpful.

    On a similar topic,
    I am trying to find a tutorial or help on how to use mailing from an excel spreadsheet. let's says one pivot table where I can filter on a given criteria with several known values (departments or users for example).
    Then generating an email with a spreadtsheet that contain only data filtered for a given value of the filter.

    If someone can point me to a link where I can find some help that would be great.

  5. Jayant Gotawala says:

    if there is a any way we can send multiple Email in mail merger

    if yes can you please help.

  6. Wayne says:

    Can I set the date format?
    like from 3/22/2016 to 22 March 2016

    please help.

  7. Kevin says:

    Can this merging be done as email message?

  8. obaid khan says:

    MailMerge with Excel AlphaNumeric data showing as zero,please suggest solution.

  9. gopal says:

    Could you please send me the details as how to merge invoice data for invoice numbers having multiple rows of items?

  10. Brandee says:

    I have formatted a column in Excel as currency with 2 decimals, yet once I merge the data into Word, it only comes out as a number, without currency symbol and without decimals. Please advise.

    Thank you.

  11. Imran says:

    thank Sir i have a problem, i want to sent thanks letter to my customer with the products he purchased. in typical mail merge it will send separate letter for each product one customer purchased. i want multiple products on one letter. next letter only when new customer.

  12. Nicole says:

    Is it possible to send all emails at once & have the subject line change for each email?

    Example:
    Subject: "Your Policy is Now Inforce ~ , «Name» = , «Policy #»

Leave a Reply