Mailmerge Tutorial: Create Sorted Listings (2000-2010)

[macropod]

Hi folks,

Attached is a tutorial on how to use Word's mailmerge facility to create lists sorted by category.

Microsoft has similar articles at:
http://support.microsoft.com/?kbid=211303
and:
http://support.microsoft.com/kb/294686/en-us
but my tutorial goes way beyond those articles' scope by:
a ) providing a sample data source that can be used to demonstrate/test the use of different keys,
b ) including working mailmerge fields
c ) adding examples to show how:

. text can be categorised using two keys
. text can be added after the repeated data
. group and sub-group counts and totals can be calculated without the need for such fields in the source data
. to create a two-column output
. to create a series of tables from the merged data
. to shade alternate rows when merging to a table
. to merge to email

Plus, unlike the field coding suggested by the MSKB articles, my approach doesn't insert extra lines for Excel data sources.

Note: Please read the tutorial before trying to use the mailmerge main document for a mailmerge - the merge field coding you'll need is in the tutorial and has to be copied from there into the mailmerge main document before you can do a mailmerge.

Enjoy.

[Shinano]

Hi macropod,

Thank you very much for the tutorial. I am impressed.

I have a question for you though.

I have an Excel file with data in three columns: Users, Dates, and Transactions.

I have been asked to feed this data into a report in Word. So far, so good.

The user name should be the primary key, and should appear in the top left corner of the page, then indented from the user name should be the dates, going down a column, and indented from the date should be the transactions, also going down in a column.

The user name should appear only once, irrespective of how many dates there are, and a date should only appear once in the "date" column, irrespective of how many transaction the user did on that particular date.

I am playing around with the code in the tutorial, but I just can not get it right. Any idea one how to get around this one?

Thank you very much in advance.

Shinano

[Shinano]

Hi again,

I got it. I figured it out, and thought I would share the result with the rest of you.

{QOUTE{IF{MERGESEQ}= 1 "{SET Key1 ""}{SET Key2 ""{SET Key3""}"}"{IF{MERGEFIELD Team}<>{Key1} "{IF{MERGESEQ}> 1 "[hard page break]
"}{MERGEFIELD State}{SET Key1 {MERGEFIELD Team}}hard page break
" }{IF{MERGEFIELD Employee}<>{Key2} "hard page break
{MERGEFIELD Employee}{SET Key2 {MERGEFIELD Employee}}" ""}tab{IF{MERGEFIELD Date}<> {Key3} "{MERGEFIELD Date}{SET Key3 {MERGEFIELD Date}}" ""}{MERGEFIELD Transation}hard page break
"}

-Sorry about the bold faced remarks. I had no idea on how to display a hard page break and a tab here on this page. Please replace them with the relevant keystrokes in Word.

-Hope it may serve a purpose.

Again, thanks to macropod for the inspiration.

Shinano

[macropod]

I got it. I figured it out, and thought I would share the result with the rest of you.
....
-Sorry about the bold faced remarks. I had no idea on how to display a hard page break and a tab here on this page. Please replace them with the relevant keystrokes in Word.

Hi Shinano,

I'm confused - I can't see why you need 3 keys. You previously said:

The user name should appear only once, irrespective of how many dates there are, and a date should only appear once in the "date" column, irrespective of how many transaction the user did on that particular date.

If I understand this correctly, all transactions are to appear, sorted by date for each user. In that case, you should be able to use the code under the heading 'Merge Records by Category Using Two Keys'. All you might need to do is to edit out the coding for the ditto marks, change the formatting to suit your needs and insert the code for a hard page break.

Your field coding suggests you're also categorising by State, but there's no mention of a State field in your posts. It also appears you've nested your 'SET Key3' field inside the 'SET Key2' field, but maybe that's just a typo in the post.

As for inserting the tabs and hard page breaks, you can insert them explicitly in the code as you've done, or via fields coded as {QUOTE 09} and {QUOTE 12}, respectively.

[armsys]

Hi Macropod,
I have trouble to understand your field codes.
{Quote{If{MergeSeq}=1}{SET Key ""}}"If{MergeField State}<>{Key} "{If{MERGESEQ}>1 {QUOTE 12}}{MergeField State}{Set Key {MergeField State}}<line feed><line feed>"}{MergeField City}<tab>{MergeField Representative}<tab>{Mergefield Sales}<line feed>"}<Paragraph>

I don't understanding the meaning of:
{If{MergeSeq}>1 {QUOTE 12}}{MergeField State}{Set Key {MergeField State}}<line feed><line feed>"}
After the first record, would always the MergeSeq be set to greater than 1?


{MergeField City}<tab>{MergeField Representative}<tab>{Mergefield Sales}<line feed>
Regardless whether MergeSeq is equal to or greater than 1, the City, Representative, and Sales are always printed out? Isn't it?

Thanks for your help in advance.

Armstrong

[HansV]

Hi armsys,

Welcome to the Lounge!

MERGESEQ is the sequence number of the merged record: 1 for the first record, 2 for the second record, etc. The part {If{MERGESEQ}>1 {QUOTE 12}} inserts a manual page break (ASCII character 12) only if you're not on the first record of the merge - you don't want to insert a page break before the first record!

In this example, the merge field State is only displayed when State changes compared to the previous record. The merge fields City, Representative and Sales are displayed for every record.

[armsys]

Hi Han,
Thanks a lot for adding a lot of clarity. Now it makes a lot of sense. Compared with Macro, field codes lack the readability. Both brackets and quotation marks are hard to trace.
Thanks.
Armstrong

[HansV]

Compared with Macro, field codes lack the readability.

That's true, especially in complicated applicaitons of fields, such as those described by macropod. But they can be very useful - in some situations you can't rely on macros because users or their IT department may disable them. Fields will still work in such circumstances.

[claubercy]

Hi Paul,

I'm using your tutorial, but my Word 2007 add a section break after each item list.

What's wrong?

Thanks.

[macropod]

I'm using your tutorial, but my Word 2007 add a section break after each item list.

What's wrong?

Hi claubercy,

Welcome to the lounge.

I suspect you are using a normal 'letter' merge, not a Directory or Catalog merge. Please re-read the instructions under 'Set Up the Main Mailmerge Document'.

[mikeyy]

Hello! I know there's a really simple answer but after 3 hours of trying I'm running out of juice....

I understand the coding and I'm re-entering it in my own merged doc BUT I can't seem to insert the bookmark field "{key}" in the line that compares the new line...

{if {MERGEFIELD City}<>{Key}

I mean... you can't just type " {Key} " right? and I can't find it in the 'Insert Merge Field', 'insert Word Field' or bookmarks menus...

Help will be gratefully received (as well as prevent me from going crazy! )

Cheers... Mike

[HansV]

Welcome to the Lounge!

You are correct that you should not type {Key} just like that. You can insert the field like this:
- Press Ctrl+F9 to insert the field braces { }
- Type the word Key between the braces.

In this situation, you'll already be displaying field codes, and you'll hide them later on.

In a situation where you aren't displaying field codes, Ctrl+F9 will display the field code for the field you're inserting only. After typing the text you need, press F9 to hide the field code and to update its value.
So for example, if you want to insert an automatically updating date field:
- Press Ctrl+F9.
- Type the word Date between the braces.
- Press F9.

[macropod]

I understand the coding and I'm re-entering it in my own merged doc BUT I can't seem to insert the bookmark field "{key}" in the line that compares the new line...

{if {MERGEFIELD City}<>{Key}

I mean... you can't just type " {Key} " right? and I can't find it in the 'Insert Merge Field', 'insert Word Field' or bookmarks menus...

Hi Mike,

Welcome to the Lounge.

All the fields a created via either the field selections offered by Word, or by pressing Ctl-F9 to create the field braces (ie '{}') and typing whatever is required between them.

Another approach would be to copy & paste the fields coding from the tutorial into your own mailmerge main document, then modify the mergefield names to suit your needs, including adding/deleting mergefields as needed.

[mikeyy]

Great - thanks Hans!!!

It took me a couple more hours to get right but it's working well now

Now I've got to figure out the formatting lol

Thanks so much for your help - cheers Mike

[asliceofpi]

Hello,

I am fairly new to mail merges and field codes (basically, I can follow the Mail Merge wizard but not much more). I think I understand in concept how your example works, but I can't seem to get it to work when I create a new document in Word 2003. When I toggle the field codes in order to evaluate the code in your example, all I get to display is:

				Yang		   2100

I entered the code manually into a blank document - mostly to get used to entering field codes - which looked like this when entered (all written on one line):

{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD City}<> {Key} "{IF{MERGESEQ}> 1 ""}{MERGEFIELD State}				{MERGEFIELD City}{SET Key {MERGEFIELD City}}"}		  {MERGEFIELD Representative}				{MERGEFIELD Sales}"}

I made sure to use Ctrl+F9 to enter the fields, so what am I doing wrong? Do these have to evaluate one at a time rather than all at once? Are there hard breaks in the code that I'm missing?

I hope that once I get this much figured out, your examples fit right in with what I'm trying to accomplish.

Thanks!