This post is about an interesting problem that came to me by e-mail about a mail merge problem in Microsoft Word.
Someone from a training company wanted to send out summary data about in-progress courses to every student by e-mail. As every message would require a number of different records, it seemed logical to use the 'Directory' option. That wasn't a good solution, however, as the sender wanted to be able to use the e-mail integration features of the 'Letter' option. Here's how to use some 'Directory' features using 'Letters'.
1- Start a mail merge document using the 'Letters' option, and set up everything in your letter (including the data source).
2 - Go to the Insert Field dialog box (not a mail merge field, but just the regular fields) and choose 'Database' from the categories. Click on the 'Insert Database' button.
3 - Click on the 'Get Data' button, and choose the same data source that you used for your mail merge.
4 - Click on the 'Query Options...' button. Go to the Select Fields tab and make sure the right-hand list contains only those fields that you want to see in the table.
5 - Click on the 'Filter Records' button, and set up a criteria for the UserID field to be 'Equal to' arealuserid (choose any existing UserID and put it here - you will change this later, so it doesn't matter which one you pick). Click the 'OK' button.
6 - Click on the 'Insert Data...' button, and place a checkmark in the 'Insert data as field' checkbox. Click the 'OK' button.
7 - Now you should be back in your document, and you'll see a table there that shows the records for the UserID you chose. Press Alt+F9, and you'll be able to see the field codes. It will probably look like a mess. That's a good thing.
8 - Look right near the bottom of all of that code, and you will see a SELECT statement. In this statement, highlight where you had entered a random UserID, and now go up to your toolbar and insert the UserID mail merge field. What you're doing is setting the criteria to be equal to whichever UserID is currently being used for the mail merge. Make sure you keep the single quotes around the mail merge field and are only replacing the actual UserID.
9 - At this point, you might want to hit Alt+F9 again to stop seeing all of that code. Try to finish your mail merge, and you should see separate tables for each individual letter.
Someone from a training company wanted to send out summary data about in-progress courses to every student by e-mail. As every message would require a number of different records, it seemed logical to use the 'Directory' option. That wasn't a good solution, however, as the sender wanted to be able to use the e-mail integration features of the 'Letter' option. Here's how to use some 'Directory' features using 'Letters'.
1- Start a mail merge document using the 'Letters' option, and set up everything in your letter (including the data source).
2 - Go to the Insert Field dialog box (not a mail merge field, but just the regular fields) and choose 'Database' from the categories. Click on the 'Insert Database' button.
3 - Click on the 'Get Data' button, and choose the same data source that you used for your mail merge.
4 - Click on the 'Query Options...' button. Go to the Select Fields tab and make sure the right-hand list contains only those fields that you want to see in the table.
5 - Click on the 'Filter Records' button, and set up a criteria for the UserID field to be 'Equal to' arealuserid (choose any existing UserID and put it here - you will change this later, so it doesn't matter which one you pick). Click the 'OK' button.
6 - Click on the 'Insert Data...' button, and place a checkmark in the 'Insert data as field' checkbox. Click the 'OK' button.
7 - Now you should be back in your document, and you'll see a table there that shows the records for the UserID you chose. Press Alt+F9, and you'll be able to see the field codes. It will probably look like a mess. That's a good thing.
8 - Look right near the bottom of all of that code, and you will see a SELECT statement. In this statement, highlight where you had entered a random UserID, and now go up to your toolbar and insert the UserID mail merge field. What you're doing is setting the criteria to be equal to whichever UserID is currently being used for the mail merge. Make sure you keep the single quotes around the mail merge field and are only replacing the actual UserID.
9 - At this point, you might want to hit Alt+F9 again to stop seeing all of that code. Try to finish your mail merge, and you should see separate tables for each individual letter.
Posts
Great tutorial, thanks a lot for sharing!
ReplyDeleteThanks so much for the feedback! Glad you liked it.
DeleteThis was very helpful. I was able to follow your instructions with no problem. However, I did run into an issue when trying to email. If I had a User that had 3 courses, Word would send 3 emails to that User with the same content. It sends an email for each course. Have you ever ran into that?
ReplyDeleteIt sounds like it's grouping by course instead of grouping by user. It's hard to say without knowing what your data source and merge document look like, but I would have the data source sorted by user and then by course.
DeleteHi Stacy, I have tried this and it works fine except no matter the key field for sorting, the resulting merge document includes the grouped records in the table BUT it creates a page for every entry. In other words, For Bill Smith who has 3 course, I get three items in the table, but three separate letters. I have tried every imaginable sort option with no success. If you can tell me now to fix this I would be truly grateful. --Dene
DeleteVery helpful article and definitely a time saver over other methods I have used in the past. Thanks!
ReplyDeleteGreat solution to a problem I'd only ever found clumsy solutions to before!
ReplyDeleteIf you don't want the grouping field to be repeated I found it necessary to have a query which just returned UserID and then the data source with the remaining fields you want to display in the table for each UserID for the Insert database.
Is there a way to define formatting in the database table, e.g. column headers in bold, which get repeated in the resulting merge for all UserIDs?
Thanks, Cecil! I'm not sure if I completely understand what you're asking about in terms of the formatting definitions, but are you aware of the ability to use IF statements in mail merge? There's a pretty good article here that covers the basics: http://word.mvps.org/faqs/mailmerge/mmergeiffields.htm
DeleteSometimes I use IF statements in mail merge documents to force extra formatting or to handle certain pieces of data differently.
nice share
ReplyDeletethanks for information
This is great and worked perfectly except now, I don't know how to format the switches in the database side of the mail merge. Numbers displayed to the 13th decimal and I only need it to the 2nd. HELP!
ReplyDeletevery good which I searching from 2 months
ReplyDeleteLike a couple of the commenters, I've found that the groupings are generated correctly, but multiple pages/ letters are being generated for each group. (In your example, 3 pages with identical content if a student was taking 3 courses.) Any suggestions to correct this?
ReplyDelete