How To Do A Mail Merge From Excel

Posted on by



Have you ever wished that you could do an email Mail Merge with PDF attachments as the merged results? There are a variety of reasons you may want to do this. Maybe you would like to send individual PDF letters to students via email attachment, or perhaps you want to have a partially completed PDF form that is personalized for each person you are emailing. The possibilities are endless! By the way, big thanks to Julie and Meghan for having an awesome question in last week’s Word session that has inspired this Byte!

Highlight the field you want filled with data sourced from the Excel spreadsheet. Go to the Mailings tab, click on the Insert Merge Field button, and you will be shown a list of column headers for which you’ve specified information in the spreadsheet. Choose the field you want to merge and click on Insert. Following our article on importing Excel data into a Word mail merge, here are the options for using percentages. Excel might show a percentage but the number stored is a fraction. For example 25% is stored in Excel as 0.25. Normally, a Word mail merge will import the raw number from Excel and let you format it in way we’ve described before. Go to Mailings Start Mail Merge E-mail Messages. Step 2: Set up your mailing list The mailing list is your data source. For more info, see Data sources you can use for a mail merge.

Background

  • Today I am going to be referencing an Add In that comes with the installationof Acrobat DC on a computer. Torequest Acrobat DC, or other Adobe CreativeCloud programs, contact the Help Desk at 4357.
  • Also, for these exercises, I am assuming you have some experience with Mail Merge in Word. If you have never merged before, or are not comfortable with the process, please come attend my Microsoft Word Essentials training! You will be comfortable with it in no time.
  • On the Mailings tab, click Insert Merge Field and select the field you want to show on your labels. Continue adding fields until you've added all the information you want to include on the labels, and then click OK. Tip: To sort your recipient list or remove recipients, on the Mailings tab, click Edit Recipient List.
  • Hello, Can we do something in Excel that's similar to a mail merge in Word. I have a spreadsheet that is essentially a form letter in Excel format, contains empty fields which I want to fill with the the records from another spreadsheet which has information i.e., For example, first name, last name, employee id, marks, etc.

How To Do A Mail Merge From Excel To Make Labels

Exercise Files

Here are a couple files for experimentation, if you would liketo follow along:

  • Sample Letter Word Document: this is your file that you would like to turn into a merged PDF for recipients. This is a fictional letter for potential students.
  • Mail Merge Database Excel File: this is a fictional database for linking to the merge document.
  • Important: you should save these both to your desktop before starting, since you will have to browse to find the Excel file when merging.

Creating the Merge

You will start this merge like any other email merge:

1. Open the Sample Letter Word file.

2. Go to Mailings tab and select Start Mail Merge

3. Select Letters

4. Select Recipients and browse for the Excel file

5. Insert Merge Fields as desired. I am going to insert:

  • Address block
  • Greeting line, and
  • Interest field

Work Offline

In real life, this is an optional step when doing an emailmerge, but I will ask you to do it this time, since we are working with fakeemail accounts, and you probably don’t want to receive a bunch of bounce backemails. When you work Offline in Outlook, it allows you the opportunity toreview your email merge before going back online and sending them.

  • In Outlook, go to the send/receive tab and select Work Offline.
Merge

Finish and Merge

1. Instead of going to Finish and Merge, like we usually do, we are going to select Merge to Adobe PDF.

2. In the popup screen:

  • Specify a name for your PDF
  • Check the box next to Automatically send Adobe PDF files by Email. Email data will populate by default, but that is something that could be changed if the wrong column is selected.
  • Type in a special email Message, if desired.
  • Press Ok

3. Select a location to save the merged PDF files. This creates separate PDFs for each of the merge results, for your records.

If there you don’t see a location that will work, notice you can also create a new folder in the lower left.

Double Check Results

  1. Open your Outlook.
  2. Since you are working Offline, these files are going to be sitting in your Outbox. You should see a number on the left side of your screen next to Outbox. Click on the Outbox to access the folder.

3. You will see a list of emails. Double click on any of them and take a look at the email that is set to send. You will see your message, subject line, and a PDF attachment.

4. Double click on the attachment. Here is the letter you wanted to send, as a PDF attachment.

5. The individual PDFs are also living wherever you chose to store them on the previous section, step 3.

Work Online

Don’t forget that you need to go back online again with yourOutlook! When you go back online, all of the emails in your outbox are going tosend automatically, and in this case (with our fake emails) is not a good idea.

  1. Click on the red X to the right of of each ofthe emails to delete them.
  2. When you are finished, revisit the Send/Receive tab and toggle off the Work Offline button to go back onlineagain.

Thoughts?

Will you use this in your area? I would love to hear how youplan to use this tool!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the fullgallery, and more information about the WSU Microsoft Office Power UserProgram, please visit: wichita.edu/poweruser

So much time can be saved when we merge from Excel to Word. We can create and send letters and emails with a large mailing list in a shorter span of time with the mail merge.

In order to mail merge from Excel to Word, we need two files:

  • Microsoft Word file with the letter template
  • Microsoft Excel file with the list of recipients, where we allocate one line for each recipient

Mail merge Excel to Word letter

The MS Word file will look like the example below.

Figure 1. Mail merge Excel to Word letter

Excel mail merge template

The MS Excel mail recipient file will look like this:

Figure 2. Excel mail merge template

Note that for each recipient, we have entered the First Name, Last Name, Address and Country of residence.

Save both files in the local drive for easy reference.

Mail merge from Excel to Word

In order to mail merge Excel to Word, we will make use of the Mail Merge Wizard.

Step 1. Open the MS Word file with the letter template

Merge

Step 2. Click Mailings tab, then Start Mail Merge button and select Step by Step Mail Merge Wizard from the drop-down menu.

Figure 3. Mail Merge Wizard in Excel

The Mail Merge dialog box will appear, featuring 6 steps to mail merge Excel to Word.

Step 3. Select Letters as document typeand click Next: Starting document at the bottom right corner of the window.

Figure 4. Mail Merge Wizard Step 2 of 6: Starting Document

Step 4. Tick Use the current document and click Next: Select recipients for Step 3 of 6

Figure 5. Mail Merge Wizard Step 3 of 6: Select Recipients

Step 5. Minimize the Word file and open the Excel mail recipients file from the local drive

Step 6. Click Browse and the Select Data Source dialog box will appear.

Figure 6. Browse for existing recipients list

Step 7. Browse and select the Excel mail recipients file.

Figure 7. Browse and select the Excel mail recipients file to merge

Step 8. The Select Table dialog box will pop-up. Click OK.

Figure 8. Select table to merge data from Excel to Word

Step 9. The Mail Merge Recipients list will appear. Let us go through the list and check or uncheck recipients that we want to add or remove. Click OK afterwards.

Figure 9. Selecting mail merge recipients

Step 10. Click Next: Write your letter

Figure 10. Mail Merge Wizard Step 4 of 6: Write your letter

Step 11. It is time to write the letter and complete the details by inserting the recipients. Click the space under the date in the Word document and click Mailings tab, Insert Merge Field.

How To Do A Mail Merge From Excel To Powerpoint

Figure 11. Insert Merge Field in Mail Merge Wizard

We will see that the columns in the Excel file First Name, Last Name, Address and Country are already merged into our Word document.

Step 12. Next, we insert the necessary merge fields into our Word document to complete our letter. Insert the fields as shown below in bold letters.

How To Do A Mail Merge From Excel

Figure 12. Inserting the mail merge fields from Excel to Word

Step 13. Click Next: Preview your letters as Step 5 of 6.

How To Do A Mail Merge From Excel Spreadsheet

Figure 13. Mail Merge Wizard Step 5 of 6: Preview your letters

Step 14. We will now be able to preview our letter with the first recipient. Click on the right arrow to scroll to the next recipients.

Figure 14. Merge Excel to Word recipient #1

Step 15. After scrolling through the recipients, we can now move on to the last step to complete the merge. Click Next: Complete the merge.

Figure 15. Mail Merge Wizard Step 6 of 6: Complete the merge

Step 16. Congratulations! The merge is now completed. We can either print the letters or edit individual letters.

Figure 16. Output: Mail merge Excel to Word

When we click Edit individual letters, this pop-up window will appear. Click All to merge all letters into one new document.

Figure 17. Merge letters

Word will then display a document containing all the letters with each letter showing a different recipient. Save the merged Excel to Word document to finalize the changes.

Figure 18. Final result: Mail merge Excel to Word

How To Do A Mail Merge From Excel 2016

Instant Connection to an Expert through our Excelchat Service

How To Do A Mail Merge From Excel To Pdf

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.