mail-merge-fu

September 6, 2006 7:12 AM

MailMergeFilter: I want to print a large number of uniquely numbered gift certificates for different businesses using Microsoft Office, and I can't seem figure out a good solution.

I have to print gift certificates for many different businesses that all have different, pre-determined unique consecutive numbers. I'd like to do the design for the gift certificates in Microsoft Publisher and then merge the data (buisness name, address, phone number, and gift certificate number) in from Excel.

For Example:

40 gift certificates for "Company 1" numbered 41-1 ... 41-40
20 gift certificates for "Company 2" numbered 12-20 ... 12-40
80 gift certificates for "Company 3" numbered 17-120 ... 17-200

...and so on.

So far I've been able to mail merge the business details into publisher, but not the gift certificate numbers.

To further complicate things, I need to print the gift certificates 3-up on a page, and I haven't been able to find a way to get the merge to change data on that 3-up page. (If I just type all of the numbers into excel, and try to merge those into the document, page 1 will be 3 gift certificates all with the same number.)

Because of the volume of certificates that I need to print, I'm looking for the least labor intensive solution.

What's the best way to accomplish this with these tools? Suggestions for other ways of accomplising this task are appreciated too.

posted by TurkishGolds to computers & internet (9 comments total)

Just to make sure...have you actually tried printing out a test page? I think some version of Publisher has a print preview problem where the number will not change to the next record...but actually printing it out is ok...
posted by giantfist at 7:29 AM on September 6, 2006

I think that the best solution here would be to tell Word that you're printing labels. There's a "wizard" for that, and it's fairly easy to understand. I'll include some fairly detailed instructions, and I apologize if they seem too stupid; I'm used to giving instructions to people who have the computer skills of a retarded monkey.

First, create an Excel table with columns for each piece of data you need, including a column for the numbers. Put a header on each column. The number of rows in the table should equal the total number of gift certificates you want to print, with one number per row and the address data repeated. [By the way, you don't have to type in each number so long as the numbers are sequential or follow a pattern. Just type in the first number or two in the column, then grab the lower right corner of the cell (the cursor will be a + sign) and pull down until you get all the numbers you need. You can do the same for the names, typing the name once and then pulling the corner down until you get as many as you need. This may be common knowlege, but I'll include it anyway because it took me several years of using Excel to figure it out, and it's saved me a lot of time.]

Then, in Word, open mail merge. Choose labels, and pick the size you want for the gift certificate. (If that size isn't pre-loaded, you can design a custom size to fit your needs.) For "select recipients," choose your excel sheet. It will then ask you to "write your letter." Put all of the stuff that's the same on each certificate on the first certificate. Then pull up the mail merge toolbar and click "insert merge fields" (it will probably be the sixth button from the left). That will pull up a list of the columns in your Excel sheet. Click them to place them where they need to go on the certificate.

Once the first certificate is arranged the way you want it, click "propagate labels." Each of the subsequent certificates should have "< next record>>" at the top of it, which will ensure that it pulls the next line of the excel sheet. If you click "View Merged Data," (the ABC button in the merge toolbar), you'll be able to see what your finished certificates look like to make sure they're right. Once they all look the way you want them, click "Complete the Merge" in the lower right corner of the page, and you'll be able to save or print.

I hope this was helpful and not too hand-holdy. Like I said, I'm not used to helping smart people.
posted by Amy Phillips at 7:44 AM on September 6, 2006 [1 favorite]

Amy, thanks for the advice! Your Excel tricks help quite a bit, but I still can't get them to print from publisher 3-up on a page numbered correctly. Yes, giantfist, I actually tried to print a test page.
posted by TurkishGolds at 8:37 AM on September 6, 2006

What are you using Publisher for? I'm not as familiar with Publisher as I am with Word and Excel, but if you can be more specific about what part of it you're doing in Publisher, I may be able to figure out why it's not integrating properly and/or find a way to do it all in Word instead.
posted by Amy Phillips at 9:13 AM on September 6, 2006

Thanks, Amy!

I'm doing it in publisher because of the better design capabilities. I want it to be kind of flashy.

Perhaps I can design it in publisher, save as JPG, insert that into a word doc, and then put the merge fields on top of the graphic?

I'll play with it this afternoon.
posted by TurkishGolds at 10:05 AM on September 6, 2006

Argh! I still can't get them to print 3-up and have them numbered correctly. All three certificates on a page will all have the same number.

I feel like I've looked through every single option in both word and publisher. Is this impossible? Thoughts?
posted by TurkishGolds at 11:34 AM on September 6, 2006

So you're trying the merge in Word instead of Publisher?

If I understand the problem correctly, the solution might be this, I think. It seems kinda complicated though.

Maybe instead you could setup your mail merge as if you were printing labels? Then use the label options to setup a "new label" that has the dimensions of your page of certificates...
posted by giantfist at 12:12 PM on September 6, 2006

In Word, you need to add a field code to move to the Next Record.

First, enable the Mail Merge toolbar. Right-click in the toolbars at the top and select "Mail Merge." In the new toolbar, there should be a button titled "Insert Word Field" with a dropdown arrow. Click on it and select "Next Record." This inserts a field code that tells Word to move to the next record in your merge data file. Use this each time you want to insert the next certificate number.
posted by junesix at 1:49 PM on September 6, 2006

The merge fields in your Word document should be structured like this:

«Certificate_No»

«Next Record»«Certificate_No»

«Next Record»«Certificate_No»

This will get you 3 consecutive certificate numbers on the same document.
posted by junesix at 1:53 PM on September 6, 2006