Create a form letter with Excel and Word
You can also make a data source for a Word mail merge from an Excel spreadsheet. Create an Excel file as usual and fill it with data. We explain the further steps in this practical tip using an example.
Word mail merge with Excel as data source
For the example, first create an Excel file according to the graphic. Then start Word and follow the instructions below.
- Go to the "SHIPMENTS" tab in Word.
- Click on "Select recipient" and then on "Use existing list".
- In the next dialog window, select the Excel file you just created and confirm by clicking "Open".
- In the next window you have to select the table in the Excel file in which the data is available. In this case, this would be Table 1. Check "First data series contains column headings" and click "OK".
Insert mail merge fields
Since you have confirmed the previous dialog with "OK", you have been using Excel as a data source. To demonstrate this, add mail merge fields to your mail merge as follows.
- Go back to the "SHIPMENTS" tab and click on "Insert Merge Field". The entries in the dropdown list correspond to the column headings in your Excel file.
- Put together a small address block by first inserting the mail merge field "first name", then "last name" and then "address".
- Now click again on the tab "SHOWINGS" on "Preview results".
Word now shows you the first entry in the Excel file. For the second person, click on the blue arrow pointing to the right to the right of the "Preview results" button. With these arrows you can go through all recipients that Word has read from the Excel file. This has the advantage that you have the letter and the layout clearly and uniformly on a Word page. This means that any changes are immediately applied to all recipients. If new recipients are added, you can simply add them to the Excel file and they are already available in your form letter.
These instructions apply to Outlook 2010/2013. You can find tips on conditional formatting in Excel here.