Creating a mail merge in Word using QR codes from Google Maps API

Purpose

To create visitors passes / name tags with QR-codes for festivals and events

Setup

Each QR-code contains the basic data on a person. It can simply be their names and e-mail addresses but can be extended to cover a complete business card including telephone numbers, faxes, hyperlinks and business address.

Uses

1. Identify people: when you want to mail a person back, send information, involve them in a game
2. Connecting people: exchange e-mail addresses and personal data (name, telephone number)

Creating the mail merge using Word

Basis

The mail merge will include these elements:

1. Clear text: the name, e-mail and anything you want to put on the name tag / visitor pass
2. A hyperlink to an image on the web: the QR-code as generated by the Google maps API

The result will be a name tag / visitor pass with:

1. The name and other info in clear text
2. A QR code including any data you wish, like name, e-mail and telephone number

Example:

TABLE OF CONTENTS:

1. Before you start – stuff that is important to know regarding Word, images and mail merge
2. The trick: IncludePicture tag – describes how to insert and use the IncludePicture tag
3. Linking the data source – describes how you link the data source to the word document
4. Creating the tags in the document – brief reference to creating the other taqgs
5. Mail merge: the different results and view – showing you the different views Word – including ersatz images – can present you after the mail merge. Includes the tip how to load the actual images.
6. QR-codes from Google Maps – tells you how the QR-codes work and how to get them in the right scale
7. Generating the personalized QR-code links in Excel – as we use Excel as the source of the addresses, this describes how to create the link to Google Maps
8. Getting the size right – describes how to set use the DPI settings for Web content to manipulate the size of the QR code
9. Conclusion – things to know

1: BEFORE YOU START: IMPORTANT TO KNOW

Word does not automatically load the images when you create a mail merge. When Word creates the mail merge, it will not automatically load the image. This is a known problem. The solution is this:

1. Create the Mail Merge: using the option “Edit individual documents”
2. Press <control> A: Selects all / selects entire document
3. Press F9: refreshes the content in the merged document. The images will be retrieved from their web-location. This can take a while depending on your internec connection and the number of images to be retrieved.

Moving an IncludePicture tag with embedded “mergefield” tag

For some reason, Word converts the tag into an image which loses the data-binding options when you move the tag around. So:

1. Determine where the image will be
2. Place the IncludePicture tag

Or use copy/paste

2: The trick: INCLUDEPICTURE tag

Word allows you to include pictures in your mail merge using the INCLUDEPICTURE tag. There is a catch with this tag: to make it work with dynamic image links, you need to embed the {MERGEFIELD “<fieldname>”} tag inside the INCLUDEPICTURE tag.

There are several ways to do this.  The simplest is this:

1. Press <control> F9: an empty mail merge field tag will be created
2. Right click on the created tag: the context menu will appear
3. Click on the option “Edit field”: a popup will appear in which you can select the type: “IncludePicture”
4. Select “IncludePicture” from the list: the tag will be created
   
5. Click on the IncludePicture tag and add quotes: “” : this will contain the hyperlink to the QR-code. It will look like this (cursor is between the quotes):
   
6. Place the cursor within the quotes and click on “Insert merge field”: this will insert the tag of the QR code
   
   Result:
   

4: CREATING THE TAGS IN THE DOCUMENT

There are several ways to create the Mail Merge fields in Word. The simplest is to use the “Insert Merge Field” box as shown in step 6 regarding the IncludePicture tag.

5: MAIL MERGE: DIFFERENT VIEWS AND RESULTS

Below I present the different results Word can and will show you.

1. When the document is loaded in Word. Tags show with French quotes. The image is an erzats image.
   
   As I already run the merge with the following steps, a QR-code is shown. This is not the one that will be the end result
2. Pressing <ALT> F9 will switch to the “edit” modus and reveal the coding of each tag:
   
3. When you perform the mail merge in the tab “Mailings” using:
   
   This will possibly be the result:
   
4. Hit <ALT> F9 if so. You wil be presented with a ersatz image:
   
   In this case, the image is one loaded in a previous session. Very likely it will be an empty box.
5. Hit <CTRL> A to select the entire document
6. Hit F9. The images will be retrieved from their remote location. The result will look like this:
   

6: QR-CODES FROM GOOGLE MAPS

Google maps provides a simple and quick solution to generate QR-codes on the fly. The link looks like this:

http://chart.apis.google.com/chart?cht=qr&amp;chld=L|0&choe=UTF-8&chs=96×96&chl=Peter Kaptein|peter@instantinterfaces.nl|Instant1

Try it here:

1. Change the value of “chs”.
2. Change the value of “chld” from “L” to “H” and back. The QR-code will become more detailed with “H” as it contains more information.

These are the different components:

1. http://chart.apis.google.com/chart: the link to the Google charting API
2. cht=qr: chart type is QR-code
3. chld=L|0: minimal error correction: option “L” offers 7% error correction. Default is “H”. Option “L” contains only the basic info with minimal redundancy. “H” will contain more info and will be more detailed.
4. choe=UTF-8: (character encoding) the character encoding.
5. chs=96×96: (chart size) this defines the size of the QR-code including the white space around it. 96 x 96 pixels is approximately one inch if the default settings for Web-images is 96 pixels per inch.
6. chl=<your text>: this is the parameter containing the information you want to encode.

7: GENERATING THE PERSONALIZED QR-CODE LINKS IN EXCEL

In this example we have 5 columns: first name, last name, e-mail, company and QR-code.

The link is created via a “concatenate” in Excel using the base URL in cell F1.

This base-url looks like this:

http://chart.apis.google.com/chart?cht=qr&chld=L|0&choe=UTF-8&chs=100×100&margin=0&chl=

The formula to create the personalized QR-code links is this:

=CONCATENATE(F$1;  A2; ” “; B2;”|”;C2;”|”;D2)

Where in our example:
A = first name
B = Last name
C = e-mail
D = company name

I use the “|” to separate the different data items.

8: GETTING THE SIZE RIGHT IN WORD

The resolution of the QR-code set in “chs” defines the size it is displayed. The API will always display whole pixels for each bit. So when the data does not fit in the size you gave the API, it will scale the QR-code down from i.e. 2×2 pixels per bit to 1×1 pixels per bit.

By default Word is set to 92DPI per pixel. If you want your QR-code to be printed out in a flexible size like 1.2 x 1.2 centimeters, the best thing to do is to change the print resolution:

1. Open “Word options”
2. Select “Advanced” in the panel (Word 2007)
3. Scroll down
4. Click the button “Web options”
5. Select the tab “Pictures” in the popup
6. Set the DPI to any number. I choose 200 DPI with a QR code of 100 x 100 pixels. This screenshot shows 250 DPI
   

It is by balancing the DPI settings in Word and the pixel-size of the QR code in the Google API where you will get it right.

A simple formula to get the right size is this:

2.5 centimeter = 1 Inch

QRcm = QRpixels / (2.5 * DPI)

QRcm = Size of QR-code in centimeters
QRpixels = Pixel size QR-code
DPI = Pixels Per Inch as set for printing web images in Word

9: CONCLUSION

Using this approach you can create bulk-codes personalized per person. Using Word for this brings it’s quirks and requires your hand AND feet to get the results but generates at least a standard. Once it is working, it is working.

Posted in: How to