How to include pictures in a Microsoft Word Mail Merge

by Jim Gordon, Microsoft Macintosh MVP and co-author of
Office 2011 for Mac All-in-One For Dummies - A complete reference book for Microsoft Office 2011

In Microsoft Word 2011 there are at least three ways to make Structured Query Language (SQL) queries of a structured row and column database:
* Use Word's Mail Merge Manager
* Use a DATABASE Word Field
* Use Visual Basic for Applications (VBA) with either Mail Merge or the DATABASE Word Field

Overview
Word's Mail Merge Manger has excellent documentation here in the Office for Mac reference book, and here in Word Help, You can use Word's built-in Macro recorder to get VBA code samples, also explained in the Office 2011 reference book and in Word Help. Therefore, Mail Merge is not discussed here.

[Edited Aug 6, 2012 - You may be able to make this work without using ODBC at all. I plan to update this page with details. Meanwhile, following these steps work even though you really aren't making an ODBC connection.]

This web page attempts to explain how to get a query result set into a Word 2011 document using the DATABASE Word Field using Word's interface and using VBA. Starting with a Word document, an ODBC (Open DataBase Connectivity) connection will be made to an Excel workbook containing data tables on worksheets. A SQL command will be executed and return a result set to the Word document.

Preparations
You will need several ingredients:
* Microsoft Word 2011
* An ODBC driver. Neither Apple nor Microsoft provide ODBC drivers. There are two companies to choose from, and I am happy to report that both companies offer outstanding support for Mac customers:
    - ActualTenchologies. Use ActualTechnology drivers to connect to data sources that are in directories that your Mac can connect to using Finder.
    - OpenLink. Use OpenLink drivers to connect to data sources via network connections.
The ActualTechnologies Microsoft Access driver is used in the examples on this page. Word requires that the data source be in row and column tables that are delimited with a single character such as a comma (csv). Consequently, Microsoft Access databases (.mdb) cannot be used as data sources for Word 2011. Even though the ODBC driver can connect, the data  in a (.mdb) file is not delimited with a common character. Comma Separated Value (.csv), text files, and Microsoft Excel (.xls but not .xlsx) workbooks can all be used as data sources. Each worksheet within a workbook can be treated as a table within the database. Use the name of each worksheet as the name of a table in an Excel workbook. Each column in an Excel workbook represents a Field or Coiumn Header.
* You will need to make a Data Source Name (DSN) using Microsoft Query in Excel before you can use the data source in Word. How to do this is explained below.
* You will need a structured row and column database to use as your data source. The first row (and only the first row) has unique column names (aka Field Names or Column Headers). All remaining rows contain data. The first empty row signfies the end of the data. There are no merged cells anywhere within a database.


The data source
The data source can be an Excel workbook. You can copy and paste this table into an Excel Workbook and then save the workbook to use for an example data source. As of this writing (October, 2011) the file format must be 97 through 2004 (.xls) to work with the ActualTechnologies driver.

IDNumber FirstName LastName
1
Mary
Smith
2
Thomas
Jones
3
Justin
Quick
4
Megan
Time

Later, you will need the exact file path to your Excel workbook. Excel can give this information to you. After you have saved your workbook, insert a new, blank worksheet (on the Insert menu choose Worksheet). In cell A1 type this cell formula =INFO("directory") and then press Return. Excel will display the current file path to the workbook. Select cell A1 and then use Edit > Copy. Select a different cell and use Edit > Paste Special and in the Paste Special dialog click the box for Values and then click the OK button. Now you have the file path as plain text. When it is time to get the full file path for use in your DATABASE or VBA query, you can copy the path from this cell so you won't have to type it.

The data source
After you have installed the ODBC driver, you configure a data source using Microsoft Excel 2011. To configure a data source, do the following:
1. Open Microsoft Excel 2011
2. From the Data menu choose Get External Data > New Database Query. The ODBC Data Source Chooser dialog opens.
3. In the ODBC Data Source Chooser, click the Add button
4. Select a driver. For this example use Actual Access
5. Click Finish. The Access DSN Configuration dialog opens. (DSN stands for Data Source Name)
6. Click the Continue button
7. In the Name field type a Name to use as the Data Source Name (DSN) for your query. Enter a short one-word name you can remember with no special characters. WordTest is used as the Data Source Name (DSN)  in the examples on this web page.
8. In the Description field type a brief description of the data source
9. Click the Choose button. The Choose a File dialog opens. Choose the file that will be used as your data source. ForMSWord.xls is used as the file name in the examples on this page.
10. Click the Continue button. The checkbox should be checked for "Top row contains column names."
11. Click the Continue button and then click the Done button. You should now see WordTest (or whatever DSN you typed) added to the Data Source Chooser dialog.
12. Click OK to close the Data Source Chooser dialog.
13. Click OK to the message Could not establish connection and OK to the message that no data could be retreived.
14. You can now quit Excel


Setting up the Word mail merge document
To illustrate using a picture in a mail merge, you will make a simple form letter.
1. Open a new, blank Microsoft Word document. Use Word’s Print Layout view.
2. Save the word document.
3. From Word’s Tools menu, choose Mail Merge Manager (MMM) to display the manager if it is not already showing.
4. In MMM step 1, choose Create New > Form Letters.
5. In MMM step 2, click the Get List button and from the pop-down menu choose Open Data Source…
6. The Choose a File dialog opens. Choose the Excel data source workbook that you saved earlier.
7. In the resulting dialog that tells you a text converter will be used, click the OK button.
8. In the dialog that shows the sheet names of the workbook, click the top pop-up menu and choose the sheet that contains the data table to use for merging. Tip: In the future, whenever you open the Word document, remember to choose the same sheet from the list.
9. Click the Save button on the standard toolbar or press Command-s to save the mail merge Word document

At this point you have a completely blank mail merge document connected to our Excel data source workbook. Next, you will add placeholders to the document from the MMM.

10. In MMM step 3, drag the First Name placeholder to the mail merge Word document, then press a space.
11. In MMM step 3, drag the Last Name placeholder to the mail merge Word document, and then press Return.
12. In MMM step 5, click the triangle to expose the Preview Results section
13. In MMM step 5, click the first button <<ABC>> View Merged Data
14. In MMM step 5, click the red buttons to move through the data

At this point you should be able to see the data from the Excel workbook refresh using the red buttons. If you click the <<ABC>> View Merged Data button repeatedly, you should toggle between showing the names of the contact fields and the names in the data source table. So far this is all standard procedure for mail merge. Next, you can work with the pictures. You need to get the full file path of one of the pictures, and you can get it this way:

15. Right-click (Control-click) the Finder button on your dock (the blue smiling face) and choose 'New Finder Window
16. In the search field in the upper right corner of the Finder window, type the name of one of the picture files, such as Mary.jpg
17. In the resulting list of files, select the name of the file that is in the pictures folder to be used as the data source in the merge.
18. Right click on the name of the picture file, and choose Get Info from the resulting pop-up menu
19. In the General section of the Get Info dialog, in the Where section, select the text that has the file path and then choose Edit > Copy or press Command-c to copy the file path to the Mac OS clipboard.
20. Click into the mail merge document to give it the focus of the computer’s attention.
21. Press Command-v or choose Edit > Past to paste the file path into the mail merge Word document.
22. You have almost the full file path, but you need to type a slash and the file name, such as /Mary.jpg to make the file path complete. The full file path should like something like this: /Users/JBG/Documents/Databases/MM Include Picture/EmployeePics/Mary.jpg
23. Select the text of the file path, and copy it to the Mac OS clipboard using Edit > Copy or Command-c.

Now you have the file path of one picture on the clipboard so you won’t have to type so much. Now you can link the picture to our mail merge document.

24. Click into the mail merge Word document and choose Edit > Paste or press Command-v to paste the file path into the document body.
25. Replace each slash / with a full colon : in the file name.
26. Select the complete modified file path in the Word document and copy it to the clipboard using Edit > Copy or Command-c
27. Click the Field button on the MVP toolbar, or from the Insert menu choose Field to display the Field dialog.
28. In the Field dialog, under categories choose Links and References.
29. In the Field dialog, under Field Names choose IncludePicture.
30. In the input box, type two quotation marks. See screen shot:

Screen shot of
          Word Field dialog

31. In the input box, click between the two quotation marks you just typed, and press Command-v to paste the file path from the Mac OS clipboard into the field so that the file path is within the quotation marks.
32. Click the OK button. The picture should now appear in the mail merge Word document.

Now replace the file name of the picture with a merge field.

33. In MMM step 5, click the View All Placeholders button, or click the {a} View All Placeholders button on the MVP toolbar. The picture is replaced by the text of the Field Code you entered.
34. Click into the INCLUDEPICTURE Field Code and delete the name of the file, but keep the file extension. In the example so far, delete Mary but keep the colon and .jpg and keeping all the rest of the field code unaltered.
35. In MMM step 3, from Contacts drag the field that is the one for the pictures in the data table and position it exactly between the colon and the file extension. This adds a {MERGEFIELD photo} set of brackets to the INCLUDEPICTURE field code.

The INCLUDE picture Word Field should look something like this: { INCLUDEPICTURE "Macintosh HD:Users:JBG:Documents:Databases:MM Include Picture:EmployeePics:«Picture_File_Name».jpg" \* MERGEFORMAT } with chevrons around the field name you used for the photos column of your data source. Next, test to make sure everything is working.

36. In MMM step 5, click the {a} View All Placeholders button, or click the {a} View All Placeholders button on the MVP toolbar so that you display the picture instead of the field code.
37. In MM step 5, choose a different record by clicking the buttons next to the record number, or by typing a different record number into the box. Toggle display of data by clicking the <<ABC>> View Merged Data button. As you choose different records notice that the text information changes, but the picture does not update.
38. Select the picture by clicking it once.
39. While the picture is selected, click the Update Field button on the MVP toolbar. If you click the Update Field button the MVP toolbar after you change the record number, you should see the picture update to the proper picture for each record.

Note: Updating the field code won’t work unless you have used colons instead of slashes in the file path. Ordinarily, you can right-click on a field code and choose update field from the pop-up menu, but that doesn’t work with INCLUDEPICTURE, so you do need to click the Update Field button on the MVP toolbar to refresh the picture for each different record.

If you made it this far, congratulations! You’ve got INCLUDEPICTURE working in a mail merge. I would love to tell you all you have to do is Complete Merge in MM step 6, but unfortunately, that won’t work (the picture won’t update for each record). To complete the merge you need to turn to the magic of Visual Basic for Applications (VBA). You will enter the Visual Basic Editor, which is like a word processor for computer code. Then, you will put the computer code into a “module,” which is somewhat like a word processing document.

40. On the Tools menu choose Macro > Visual Basic Editor, or click the Visual Basic Editor button on the MVP toolbar to display the Visual Basic Editor (VBE). Don’t be scared, but a little apprehension is OK.
41. In the Visual Basic Editor, on the Insert menu choose Module to open a window that looks like a blank Word document.
42. Copy the following code and paste it into the module, then read through the code starting at the top so you get an idea of what the code will do when it is run. You can make a loop instead of pasting the code over and over again for each record, but that’s a different lesson. Make sure you follow the instructions within the code where you must substitute the name of the mail merge Word document you are using within the quotation marks for each record after the first one.

Sub DoTheMerge()
'Merge the first record to a new document
'Notice the active record is the first record
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
'
'The following section is repeated for each record (except the first one) in the merge. The example has 4 records, so the code is repeated 3 more times
'
'Return focus to the mail merge document
'Put the file name of your mail merge document in the quotation marks in the next line
Windows("MMDocument.doc [Compatibility Mode]").Activate
'Notice the active record is the next record
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
            Selection.Fields.Update
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
'
'Return focus to the mail merge document
'Put the file name of your mail merge document in the quotation marks in the next line
Windows("MMDocument.doc [Compatibility Mode]").Activate
'Notice the active record is the next record
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
            Selection.Fields.Update
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
'
'Return focus to the mail merge document
'Put the file name of your mail merge document in the quotation marks in the next line
Windows("MMDocument.doc [Compatibility Mode]").Activate
'Notice the active record is the next record
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
            Selection.Fields.Update
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
'
End Sub

43. After making the changes to the file name of your own Word document, in the Visual Basic Editor’s Word menu choose Close and Return to Microsoft Word, or press Command-q to return to your mail merge Word document.
44. Click the Save button on the standard toolbar, or press Command-s to save the mail merge Word document.
45. On the Tools menu, choose Macro > Macros… to display the Macros dialog.
46. In the Macros dialog, select DoTheMerge and then click the Run button.

If you followed the instructions carefully and have the right file name in the macro, then Word will generate 4 new documents, each with the name of the employee and their picture. Congratulations! You have succeeded in making your first Word mail merge with pictures.

The example merges the records to a new Word document, but you can easily change the destination by replacing the destination in the VBA code. Here are the valid codes:

        .Destination = wdSendToNewDocument
        .Destination = wdSendToEmail
        .Destination = wdSendToFax
        .Destination = wdSendToPrinter

I have not tried each destination to make sure the code works.

Updated August 16, 2011








Although some of the above books focus on Word 2010, they are also apropos for Word 2011.

Jim Gordon's MVP Home