[macropod]

Hi folks,

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

Microsoft has similar articles at:
http://support.micro...om/?kbid=211303
and:
http://support.micro...kb/294686/en-us
but my tutorial goes 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 counts and totals can be calculated without the need for such fields in the source data * new
. 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 * new
. 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.

Attached file(s)

•  Catalogue Mailmerge.zip (49.53K)
  Number of downloads: 796

This post has been edited by macropod: 2009-07-30 18:37
Reason for edit: Improvements to email merge process

[ginaguzman]

Hello,

Your tutorial is great I found it really useful, but I have one question.

I see that it is possible to include text AFTER the repeated data and I got it to work just fine. Now is it possible to add a FIELD into that area.

I've tried it and it adds the value from the PREVIOUS record, not the current one.

Thanks!

Gina

[macropod]

Hi Gina,

---

is it possible to add a FIELD into that area ... I've tried it and it adds the value from the PREVIOUS record, not the current one.

---

Unfortunately, that's a limitation of the way things work. Although the output appears as though the text following the field coding belongs to the next record, that text is actually part of the output for the previous record.

[ginaguzman]

oh well, Thanks for your help

Gina

[TomieJ]

This seems a bit silly but I'm not able to find the attached file(s).
Can you please specify how I can download them. Thank you.

[Slinky]

Welcome to the Lounge!

We recently moved to a new server, and Macropod's attachment appears to have slipped between the cracks. He'll repost it as soon as possible. I apologize for the inconvenience.

[macropod]

TomieJ, on 25-Feb-09 21:50, said:

This seems a bit silly but I'm not able to find the attached file(s).
Can you please specify how I can download them. Thank you.

Hi TomieJ,

I've re-loaded the tutorial. You should be able to access it now.

[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]

Shinano, on 02-Mar-2009 00:56, said:

Hi again,

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:

Quote

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

[Slinky]

armsys, on 09-Mar-2009 11:50, said:

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

[Slinky]

armsys, on 10-Mar-2009 00:09, said:

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.

[MischieviousM]

I've gotten it to work (having a category field after multiple non-category fields):

******
E.g.

FilmTitle<tab>FilmLength

DateTime1<tab>MovieTheatre1<tab>Address1<tab>phone1
DateTime2<tab>MovieTheatre2<tab>Address2<tab>phone2
DateTime3<tab>MovieTheatre3<tab>Address3<tab>phone3
DateTime4<tab>MovieTheatre4<tab>Address4<tab>phone4

FilmSynopsis

******

I have not played around much to try to clean it up-it does the trick for me:

Early in the document, set a bookmark to contain the field data you want to print after the repeated data: {SET Synopsis {MERGEFIELD "FilmSynopsis"}}

Then add a text box below the repeated text and add the bookmark: {Synopsis }.
Be sure to set the Advanced properties of the textbox to absolute position relative to the page & to Allow Overlap.
The textbox will actually print many times, but since they overlap exactly, you'll see it only once.

Good luck!

macropod, on 31-Jan-2009 00:08, said:

Hi Gina,

<hr>is it possible to add a FIELD into that area ... I've tried it and it adds the value from the PREVIOUS record, not the current one.<hr>

Unfortunately, that's a limitation of the way things work. Although the output appears as though the text following the field coding belongs to the next record, that text is actually part of the output for the previous record.

[macropod]

MischieviousM, on 20-Mar-2009 03:39, said:

I've gotten it to work (having a category field after multiple non-category fields):

.......

Early in the document, set a bookmark to contain the field data you want to print after the repeated data: {SET Synopsis {MERGEFIELD "FilmSynopsis"}}

Then add a text box below the repeated text and add the bookmark: {Synopsis }.
Be sure to set the Advanced properties of the textbox to absolute position relative to the page & to Allow Overlap.
The textbox will actually print many times, but since they overlap exactly, you'll see it only once.

Hi MischieviousM,

While putting merge result into a textbox can be done with any type of mailmerge, the problem you have using it for a catalog/directory mailmerge as you have done is that the textbox has to remain in a fixed position instead of re-positioning its output in accordance the the changing # of rows of data. I think you'll find real problems with this once you've got enough data rows that they start disappearing behind the textbox - or even flow over to the next page.

There is another way of getting text to appear after the last entry in a catalog/directory mailmerge, however. You can do that by having in the data source another field containing a flag that appears only against the last record for each grouping. This is quite easy to achieve with a formula in an Excel data source. The value of the flag field can then be used in an IF test as part of the normal catalog/directory mailmerge field construction to suppress or generate the final text stream, which could include another mergefield.

[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]

claubercy, on 20-May-2009 23:12, said:

Hi Paul,

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

What's wrong?

Thanks.

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

[Slinky]

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.