Posts Tagged ‘mail merge’

h1

Word: Change date format from Excel data

June 27, 2009

When you’re using an Excel spreadsheet for mail merge data in Word, any dates come in in the ‘native’ Excel date format even if you’ve changed the date format for the relevant cells in Excel. From what I can gather the ‘native’ Excel date format is the US date format of m/dd/yyyy (e.g. 9/30/2009 for September 30, 2009).

If you want the date in the mail merged document to be displayed differently, e.g. UK/Australian date format dd MMMM yyyy (30 September 2009), then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

  1. Insert the mail merge field for the date into the Word document as normal. It will look something like this (where StartDate is the name of the mail merge field in this example):
    date_format_mail_merge01
  2. Right-click on the mail merge field, and select Toggle Field Codes.
    date_format_mail_merge02
  3. Put your cursor after “StartDate” and before the closing } and add a space.
  4. Type in the switch: \@ “dd MMMM yyyy”
    date_format_mail_merge03
  5. Right-click on the mail merge field again, and select Toggle Field Codes.
  6. Save the document. The next time you run a mail merge, the date will be in the format you entered at Step 4.

This example shows just one date format switch — experiment with other combinations to get the date format you want. For example, “MMMM dd, yyyy” for September 30, 2009; “dd-mm-yy” for 30-9-09, etc.

h1

Word 2003: Create separate mail merge documents

June 15, 2009

The client from a week or so ago was very pleased with the mail merge document I had created for them. Then they asked if they could get separate documents for each mail merge instance and save them automatically, using the organization’s name in the file name. They are a government department and need to keep separate records of each funding request, which was the purpose of the mail merge document in the first place. So this was not an unreasonable request. Should be easy, right?

What I thought would be a quick thing to do ended up taking a couple of hours of testing, searching, testing some more, followed by hair tearing. ;-) You’d think Microsoft would make this easy, but they don’t. In fact, it’s not something you can do from within the standard Word commands at all — you have to either add VBA to the Word document, purchase a separate application, or download a file someone else created that solves this problem. You’ve got to think that this would have been a common request for all the years that Word’s had a mail merge feature.

Anyhow, on to the solution, which is found here: http://www.gmayor.com/individual_merge_letters.htm

PLEASE NOTE: Graham Mayor's webpage was updated on 19 January 2012 (see his comment for this date below), so the rest of the information in this blog post MAY NOT be relevant. I haven't tested the new version. (Rhonda)

Problem: That webpage is a jumble of Word 2003 and 2007 options, troubleshooting options etc. It’s hard to get a clear picture of what you have to do to get it to work. So in the interests of making their steps nice and clear, here’s what you have to do in Word 2003 (I haven’t tested in Word 2007 yet — if I do, I’ll write a separate blog post for those steps).

Note:

  • These steps must be done on the computer of the person running the mail merge — it’s not something that can be incorporated into the master mail merge document, nor something that can be done on a server.
  • The mail merge document must already exist.

Download and install the Word add-in

  1. Close Word.
  2. Close Outlook. (Check Task Manager to make sure there are no running winword.exe and outlook.exe processes — kill them if there are.)
  3. Go to: http://www.gmayor.com/individual_merge_letters.htm
  4. In the first paragraph after the PayPal Donate button, click the link to Word Add-in-template and download it to the local machine. It’s a zip file called MMtoDocs.zip.
  5. Unzip and extract the contents from the zip file. You’ll get two folders — one for Word 2002-3 and one for Word 2007.
  6. Open the Word 2002-3 folder and copy the MMtoDocsRev37(Word 2003).dot file — it’s the only one in there. (Note: This post was written in May 2009, so the Rev number may be different for your download.)
  7. Open Windows Explorer if you’re not already in there.
  8. In the Address bar, type %appdata%\Microsoft\Word\STARTUP then press Enter. This takes you straight to the STARTUP folder.
  9. Copy the MMtoDocsRev37(Word 2003).dot file into the STARTUP folder.

Create the mail merge split template

  1. Open your mail merge document.
  2. You’ll see a new Word toolbar button called Create Template for the Merge Document (it may be floating) — click it.
  3. You’ll get a warning message that the template will overwrite any other template of the same name. (BTW, the template name is SplitMerge.dot, but you shouldn’t have another one with that name in your Templates folder if this is the first time you’ve done this.) Click OK.
  4. A new document that looks just like your mail merge document is created, called SplitMerge.doc (the title bar shows the new name). Your original mail merge document is not touched.

Run the mail merge

  1. While you’re in SplitMerge.doc, run the mail merge as normal by clicking the Merge to New Document icon in the Mail Merge toolbar.
  2. You’re asked which records you want to merge. Make your selection and click OK.
  3. Now you’ll get asked some new things — first, whether you want to create a separate doc for each record. You do, so click Yes.
  4. Next, you’re asked which of the mail merge fields you want to use for each document’s file name. Select a field — for my client, I chose the organization name field.
  5. Leave both check boxes as they are (the first one is checked, the second isn’t).
  6. Click Browse. Navigate to, then select the folder where you want to save all the separate mail merge documents.
  7. Click Continue.
  8. All your individual files for the record range you selected are created one by one, and each gets the file name of the mail merge field you selected at step 5.

And that’s it! Very clever!

If you get errors, check the original webpage (http://www.gmayor.com/individual_merge_letters.htm) for the likely cause and solution.

And if this has just saved you a heap of time, consider making a donation via PayPal on that website to Graham Mayor, the guy who developed this solution and made it available for free.

h1

Word annoyance: Mail merge fields

June 7, 2009

I had to design a ‘request for funding’ document for a government client who had hundreds to send out, each with customer-specific data that had to be incorporated, such as amounts for various programs etc. The most logical and effective way to create these documents was to create a single document and use mail merge fields to insert the data from a data source (in this case an Excel file). Everything worked really well — I was pleased, and the client was pleased.

What I wasn’t pleased about was the tedium associated with inserting more than a few mail merge fields. This document had at least 100, and every one had to be inserted by clicking on the icon, selecting the field from a long list, clicking Insert, then repeating those steps FOR EACH ONE.

Unlike the Cross-reference dialog box, you can’t leave the mail merge field dialog open and continue selecting and inserting fields. No, you have to close the dialog and then re-open it. For every single field.

And just like the Cross-reference dialog in Word 2003 , you can’t resize this dialog box, so there’s an awful lot of scrolling if you have a lot of fields — as you can see from the screen shot below, you see 15 fields at a time. If you look at the scroll bar, you can get a sense of how many fields were in this document!

Word 2003 Mail Merge Field dialog

Word 2003 Mail Merge Field dialog

I couldn’t find a way to simplify the process, like typing in the field details instead of click, scroll, select, click , repeat… If anyone knows how to add mail merge fields using the keyboard, let me know!

Part of this pain has been eliminated in Word 2007 — instead of a dialog box, when you click Insert Mail Merge Field from the ribbon, you get a drop-down menu of all fields. You still have a lot of scrolling to do for a long list, but you don’t have to click Insert for each one — just select it and that’s it.