Catalog Mailmerge Tutorial Discussions

Topic

New Reply

This thread has been closed. Please start any new discussions in a new thread.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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}}“}{MergeField City}{MergeField Representative}{Mergefield Sales}”}

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

{MergeField City}{MergeField Representative}{Mergefield Sales}
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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hi Paul,

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

What’s wrong?

Thanks.

Reply | Quote

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’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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:

Code:

				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):

Code:

{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!

Reply | Quote

Hi asliceofpi,

How areyou toggling the field codes? Alt-F9, or Tools|Options|View > Field codes (checked) should do it.

If you toggle the field codes as described, you’ll see there are various manual line breaks you’ve omitted from the field. More seriously, though, you’ve omitted a double-quote character after:
{SET Key {MERGEFIELD City}}”

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

If you toggle the field codes as described, you’ll see there are various manual line breaks you’ve omitted from the field. More seriously, though, you’ve omitted a double-quote character after:
{SET Key {MERGEFIELD City}}”

Thanks, I was able to make it work after that.

But now I’m absolutely stuck on what to do next with my problem. I’ve attached a (very) sanitized version of what I’d like to accomplish with the mail merge. To sum up, I have a header that will appear at the top of each page and has merge data within it. Below that header, I have a table which can be of varying length (which is what I need the looped merge for) based on the number of orders. I want each company to be a distinct page, with all records pertaining to that company appearing on that page, which will also format a new row to the bottom of the table with each new record. My biggest problem right now is that I can make the new company appear on the next page, but the header won’t update.

I’m sorry if any of that was unclear. I’d greatly appreciate any help you can offer!

Reply | Quote

Hi Alice,

The document you posted doesn’t have any of the field coding from the tutorial for doing catalog/directory merges, so it’s impossible to see what you might have wrong in that respect. However, given the previous discussions, it seems you’re only using the basic catalog/directory merge code from the ‘Merge Records By Category’ section of the tutorial.

For your purposes, you’ll need to base the field coding on the ‘Merge to Table By Category, With a Heading Row & Preceding Text’ section of the tutorial or maybe even the ‘Merge to Table By Category, With Text After the Table’ section of the tutorial (if you want any text after the items list).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I am trying to set up a merge where the Category field appears only once… for the first record which is, say, category Apple. For the next records where field Category = Apple, I don’t want to see Apple. When the Category changes to Banana, I want to see Banana.. but just once, not again until the Category = Cherry.

I’m just not grasping this, and I don’t understand the use of {Quote} at all. Would someone kindly have a look at the attached document and tell me what I’m doing wrong? The data file will always be sorted by Category, so the actual sorting is not an issue for me.

Many thanks.
Richard

Reply | Quote

The tutorial shows you how to do this with the third field construction under the ‘Merge Records By Category’ topic. There, you’ll see each State’s name listed once. The ‘Merge Records by Category Using Two Keys’ topic shows a variation on this, with both the State names listed once and dittos for repeated city names.

Your own document needs to be set up the same way. You could simply copy/paste the appropriate field set from the tutorial, then edit the field names to suit your data. If you’re using a table, you can only have one row each for the table header and the table data, inserted into the appropriate portions of the field code.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I created a data source with 10 unique States
Copied and pasted the code from the tutorial for Counting Group and Sub-Group Records
And made adjustments to include a few other fields in the table that is created

I expected 10 letters with each letter having the data totaled by State, grouped and subtotaled according to City
However, States having just one City were skipped and/or States were combined – I just made a big mess of it
I think this is because I need to have four keys?? (State is the main key, then data per State could be different according to any subkey – City, Item, Date)
I do not know how to incorporate a third or fourth key (subkey)
However, I am sure there is a better way to accomplish it

Here is a small sample of what I am trying to do
33193-Expected-results
I have also attached the word doc and excel data source
Please review and let me know what I have done wrong

Also, I want the ability to select Edit Recipient List > click on the filter for State, and select one State for merge from the list
This is because there are circumstances when only one letter needs to be generated from the list of States
When I try to do this, it merges the data for the one State but it leaves off the total values

I have attached the document samples

Thanks

Reply | Quote

hi Folks,

I’m new on mail merge and I need some help

How do I repeat record based on mergefield, I’ve follow tutorial. but the result just one record per page.
Case example :
I want to make a list of all City in a State, like :
State A
City A
City B
City C

State B
City D
City E …and so on

What should I do?

Thank you,

Reply | Quote

Your mailmerge main document has little in common with what is described in the tutorial. Your field structure is different (the tutorial has no NEXTIF fields, for example), and your document is not set up for a Directory mailmerge.

{Quote{IF{MERGESEQ}=1{SET Key “”}}”{IF{MERGEFIELD State} {Key}
”{IF{MERGESEQ}> 1 “”}{MERGESEQ State}{SET Key {MERGEFIELD State}}
”}{MERGEFIELD City} {MERGEFIELD Representative} {MERGEFIELD Sales}
“}
On Catalogue Mailmerge Tutorial.doc the result sould be :
ACT

Canberra Yang $2,100

NSW

Coffs Harbour Roulston $4,000
Dubbo Kristiansen $6,700
Newcastle Avard $3,400
Newcastle James $8,600
Sydney Brown $5,300
Sydney Driver $8,700
Sydney Gadzic $7,400
Sydney Williams $700
Wollongong Day $6,200
Wollongong McFarlane $8,100

NT

Darwin Couchman $1,800

… etc.

But on my case :
ACT

Canberra Yang 2100

Result like this :
ACT
Canberra Yang 2100

NSW
Coffs Harbour Roulston 4000

NSW
Dubbo Kristiansen 6700

NSW
Newcastle Avard 3400
..etc

Where do I wrong on this case.

Please Help.
Thank You,.

Reply | Quote

Hi Riko,

Welcome to the lounge.

What should I do?

Using the approach described in the tutorial would be a good starting point! Your mailmerge main document has little in common with what is described in the tutorial. Your field structure is different (the tutorial has no NEXTIF fields, for example), and your document is not set up for a Directory mailmerge.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Riko,

That’s probably because you haven’t followed the instructions on page 2 of the tutorial about setting up a Directory merge. The mailmerge main document attached to the tutorial is already set up that way and is what I used to produce the outputs you see in the tutorial.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi,

I’m struggling to figure out the proper code to accomplish my goal. I also don’t know how to tie in the Emailing code, because I have never done something like this before and I am also very inexperienced when it comes to coding.

Here is my set up:

I have 5 fields, my key field is AccountNumber. I also have TranDate, PostDate, Description and Amount as my other fields. This is going to be in a letter form and this is how I hope its displayed..

<>,

The following purchases…

[In table form now, although doesn’t necessiarly have to be if all info is captured neatly]
Tran Date Post Date Description Amount [this above the table]
<> <> <> <>
<> <> <> <> (if there was a 2nd under the same acct name)
<> <> <> <> (if there was a 3rd under…

Sincerely,

JL

There are many AccountNames that are duplicate, hence the whole point of this merge, but the other 4 fields will always be different so I need the “AccountName” (per page) to display once, and the other 4 fields in a table format until the AccountName is different (and a new page should start).

Then I need to email this information to the respected “AccountName(s)”.

So I have the jist of how to show my fields, but don’t know how to do it in table form on different pages (when a new AccountName shows). I also have never worked with Macros in Word before.

I will attach the document that I would like to set up the mail merge for. In “()” will be where I want the merge fields to be inserted. I’m having trouble actually coding the document because I have normal text and merge fields basically through the whole document alternating and I don’t know what example to work off of from the tutorial.

One more thing regarding the emailing…the actual email isn’t going to be on the document or the excel table it is accessing the data from…I originally set this up as a normal mail merge for email recipients where outlook took the account name and used this name to translate to the actual email. Will this be a problem based on your code?

Thank you for whatever help in advance you can give me.

Josh

Reply | Quote

Hi Josh,

Since you want to generate e-mails, you’ll need the part of the tutorial dealing with email merges, ie ‘Merging by Catalog/Directory to E-Mail’. What you need to understand is that performing a Catalog/Directory to e-mail is a two-part process. First, you need to use the Director/Catalog merge to take your raw data and, with the help of the macro, turn it into a table that can then be used as the data source for a normal merge to email. The creation of that table is what the tutorial describes.

At no point do you end up with a series of mergefields such as:

Tran Date Post Date Description Amount [this above the table]
<> <> <> <>
<> <> <> <> (if there was a 2nd under the same acct name)
<> <> <> <> (if there was a 3rd under…

What you do get is a table like that shown on page 22 of the tuorial. That table becomes the data source, with multiple rows of data per record, and a pair of mergefields, named «Recipient» and «Data», respectively, the second of which you’d insert into the document you now have. For example:

Code:

...
detailed information relative to the following charge(s) posted to your Citibank Visa Procurement Card:

«Data»

Please provide the following documentation for each purchase listed above:
...

I am not sure whether the «AccountName» field you refer to in the document attached to your post is the recipient’s email address or some other piece of information that needs to be included in the email merge. It it’s the latter, the solution I tailored for [post=”797815″]monkeyeb[/post] shows how you can create a data source table with an extra column for the «AccountName» field. The table would thus end up with three columns (ie fields) for the final merge: «Recipient», «AccountName» and «Data».

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yes, it is the recepient’s email..so I wouldn’t have to do monkeyeb’s way correct?

Would I need to change that field in excel (my raw data) to “recipient” each time I’m about to pull the data?

Reply | Quote

Yes, it is the recepient’s email..so I wouldn’t have to do monkeyeb’s way correct?

Would I need to change that field in excel (my raw data) to “recipient” each time I’m about to pull the data?

Hi Josh,

For the actual Directory/Catalog merge, you’ll need to set your mailmerge main document up with the field coding like:

Code:

{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD AccountName} {Key} "{SET Key {MERGEFIELD AccountName}}

|{MERGEFIELD AccountName}|Tran Date			|Post Date			|Description			 |Amount			 |
"}
|						|{MERGEFIELD TranDate}|{MERGEFIELD PostDate}|{MERGEFIELD Description}|{MERGEFIELD Amount}|
"}

where the ‘|’ symbols represent the sides of each cell. Note that, compared to the field code in the tutorial, your tables requires an extra column because there’s an extra field in your data. Note also the the field names used in the first line. If you don’t want the words ‘Tran Date’, ‘Post Date’, ‘Description’, ‘Amount’ being included in the data tables, because you want them in the body of your email merge main document, that’s OK -simply code the field like:

Code:

{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD AccountName} {Key} "{SET Key {MERGEFIELD AccountName}}

|{MERGEFIELD AccountName}|Tran Date			|					 |						|				   |
"}
|						|{MERGEFIELD TranDate}|{MERGEFIELD PostDate}|{MERGEFIELD Description}|{MERGEFIELD Amount}|
"}

Given your advice that the ‘AccountName’ field is the client’s email alias/address, rather than describing something different, there’s no need to use the solution I provided for monkeyb.

And no, you don’t need to change the field your Excel workbook to ‘Recipient’. Rather, your email merge main document will require just two mergefields, one for a field named ‘Recipient’ and one for a field named ‘Data’ – because your email merge main document will be using the table generated by the Directory/Catalog merge (not the Excel workbook) as its data source.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Macro,

Just wanted to say thank you so far for the help.

I am going to give you step by step of what I did so you can tell me what I’m doing wrong, or what I still need to do.

I took my Main Doc and added the jointable macro. I went to Tools–>Macro–>Macros–>Named it and added your code from the tutorial.

Next, I wrote my plain text and spaced it out to put the MailMerge code right in the middle.

Next, I went to Tools–>Mail Merge–>Directory–>On step 3, I located my Raw data–>Went to step 4 and began writing word for word your exact code using Ctrl F9 and all that good stuff between my text.
(Used your first example of the code since it applied)

And then completed the mail merge steps to a New Doc.

I will attach all 3 files in a zip folder.

But, it didn’t work how I wanted it, but I think I am going in the right direction.

For one, it didn’t show in an actually table format like your tutorial examples have.
It also didn’t have the multiple lines of data given the AccountName.
It still needs to have a page break whenever the AccountName Changes.
And Lastly, we still need to set up the emailing part, but I’m saving that for last until we can get it to display properly.

Please take a look and let me know what I left out or need to edit and again, thank you for working me through this. If you could code my document properly I know it will make life easier for me, but it will probably save you some time helping me as well. Whatever you want to do though!

Sincerely,
Josh

Reply | Quote

Oh one more thing,

I don’t need AccountName to display in the table. It needs to go before all of my text on my Main Doc OR if it can be embedded just for emailing purposes.

TY

Reply | Quote

Hi Josh,

You haven’t been paying attention!

As I’ve said, both in the tutorial and a couple of times in this thread, the merge is a two-stage process.

The 1st stage is to run a Directory/Calatlog merge using a document containing field code like that in the tutorial, plus the macros – and nothing more – to generate an output document containing a table like that shown in the tutorial.

The 2nd stage is to run a standard merge to email using a document containing your boilerplate text and the «Data» field, as shown in a previous post, with the document from the 1st stage as the data source for the 2nd stage.

You’ve evidently tried do the lot in one stage. I also note that the field in your ‘maindocument’ doesn’t even contain the tables needed for the 1st stage of the process.

The attached file contains:
. your Excel test data source;
. a document named ‘Merge Data Table Maker.doc’ containing the field code & macros for the 1st stage;
. a document named ‘EmailDataSource.doc’, representing the 1st stage output and the 2nd stage input;
. a document named ‘Main Document.doc’ (based on your’s), being the mailmerge main document for the 2nd stage; and
. a document named ‘OutputDemo.doc’ representing the output from the 2nd stage.

Note: The document named ‘Main Document.doc’ has been set up for a merge to file or printer, instead of merge to email, so you’ll need to change that aspect of its operation and the document named ‘OutputDemo.doc’ is for illustration purposes only, each page indicating what would be in the respective emails.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Wow…that looks great!! Thank you so much

I am hanging up on the email part. I added an additional macro (your “RunMerge” email macro) to the main document and ran it. It results in an error on this run everytime, but when I hit “end” it displays the output almost perfect.

The one thing it is missing is the first AccountName. But it does page break each AccountName properly.

I think based on your instructions, I’m suppose to then go to Mail Merge-Email Messages-Reselect my data source and continue with the emailing process? For the “To:”, I select AccountName and change it from html to plain text.

I might be screwing up the process once again, but when I do it that way, it emails the amount of records per AccountName..so if there were two lines of data, it sends two emails and it also sends the whole list with everyone’s information on it

I need it to email each AccountName ONLY once with their respective information.

I know we are close and you’ve basically done all the work, but can you please get me one step closer or at least let me know what I am doing wrong.

I will attach the zip folder with this RunMerge Macro. (attached the same folder twice, disregard one)

Thank you for your time and patience,

Josh

Reply | Quote

@ Riko,

I am sure that you have merged the data from the source file but you have not clicked the “Finish merge” button to get it merged to a new document.

DO have a look whether you have completed the whole merging of the document.

I had the same problem

Best

Reply | Quote

I am trying to get the mail merge work with directory as the source that would help me categorize the records in my current template.

I am using this field code:
{QUOTE{IF{MERGESEQ}=1{SET Key “”}}”{IF{MERGEFIELD Description} {KEY}”{IF{MERGESEQ}>1″ “} Values for {MERGEFIELD Description}{SET Key{MERGEFIELD Description}} Input “}{MERGEFIELD ColDesc} “}

Now the output I get is fine for a new fresh document, but when I try this code with an existing template, what it outputs is a document for each record it merges.

What I would like to have is a kind of this output on one page:

Details for the record selected from the file provided:

Values for ‘Subjects’
Input
Study ID
Class ID
Subject Name
Student ID

Values for ‘Visits’
Input
Visit ID
Visit date
Room No.
Summary
etc…..
But what my output is the following:

Details for the record selected from the file provided:
Values for ‘Subjects’
Input
Study ID
Class ID
Subject Name
Student ID

Details for the record selected from the file provided:
Values for ‘Visits’
Input
Visit ID
Visit date
Room No.
Summary

I agree by default, Mail Merge using directory tells word to repeat all the contents in the sheet. ref: http://lis.dickinson.edu/technology/…tory_merge.pdf . But I wanted to have the output my way.

N.B:- Found a similar post. http://forums.techguy.org/business-applications/985156-word-mailmerge-directory-listing.html

All suggestions are appreciated.

Best

Reply | Quote

Now the output I get is fine for a new fresh document, but when I try this code with an existing template, what it outputs is a document for each record it merges.

That suggests your existing mailmerge main document is not configured for a directory/catalog merge.

PS: your first link is broken and the second doesn’t appear to have any relevant mailmerge field content.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Oh I see what I did wrong, instead of linking to the excel document, I need to link to the EmailDataSource Word Document.

Alright! Works Perfect (Emailing and everything)!

One small tweak though, the formatting for the table (data) is off a little bit. Since description is always a different size, sometimes Amount doesn’t line up under the Column header and/or goes to the line below. How do I make the table wider on the Main Document to prevent this from happening?

Other than that…I am extremely excited about using this and again you’re the best!

Thanks,

Josh

Reply | Quote

Hi Josh,

You can fix the formatting by changing the position of the tab stops in the paragraph containing the «Data» field in the Main Document. I don’t think the tab formating in the ‘Merge Data Table Maker.doc’ or the ‘EmailDataSource.doc’ has any bearing on the final output.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yes..I fixed the column widths and what not.

My one last problem deals with the data transfer from the excel document to the Merge Data Table Maker. I have a column for amount in currency format (with the dollar sign, but with the transfer, it loses the $ sign (looks like it loses the whole Currency Format in general).

Do you know why this might be happening?

Reply | Quote

Yes..I fixed the column widths and what not.

My one last problem deals with the data transfer from the excel document to the Merge Data Table Maker. I have a column for amount in currency format (with the dollar sign, but with the transfer, it loses the $ sign (looks like it loses the whole Currency Format in general).

Do you know why this might be happening?

Hi Josh,

You should be able to fix the currency formatting issue by adding a numeric picture switch to the ‘Amount’ mergefield in the ‘Merge Data Table Maker.doc’ file. To do that:
. open the file;
. press Altt-F9 to reveal the field coding. You should see in the field code one that looks like {MERGEFIELD Amount};
. edit the field so that you get {MERGEFIELD Amount # $,0.00} (or whatever other number format you prefer – see below);
. when you’re done, press Alt-F9 to hide the field codes;
. run your mailmerge.

Note: The ‘# $,0.00′ in the field is referred to as a numeric picture switch. Other possibilities include:
. # 0 for rounded whole numbers
. # ,0 for rounded whole numbers with a thousands separator
. # ,0.00 for numbers accurate to two decimal places, with a thousands separator
. # $,0 for rounded whole dollars with a thousands separator
. # $,0.00;($,0.00);’-‘ for currency, with brackets around negative numbers and a hyphen for 0 values

The precision of the displayed value is controlled by the ‘0.00’. You can use anything from ‘0’ to ‘0.000000000000000’.

If you use a final ‘;’ in the formatting switch with nothing following, (eg # $,0.00;($,0.00) zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Got It! Thanks!

Can’t thank you enough for your help

Take Care.

Josh

Reply | Quote

Hi all,
You’ve all come through before and now I realy need help!! Attached is the mail merge that I’m trying to do. I’ve banged my head with the tutorial, but I just don’t see how to do it. What I would like is for the next day and room to be the key on when a new page is started. Tried just manualy sorting the data but that’s not working was well. Your help is greatly appriciated. thanks

Reply | Quote

Hi all,
You’ve all come through before and now I realy need help!! Attached is the mail merge that I’m trying to do. I’ve banged my head with the tutorial, but I just don’t see how to do it. What I would like is for the next day and room to be the key on when a new page is started. Tried just manualy sorting the data but that’s not working was well. Your help is greatly appriciated. thanks

Hi cbenson,

I can’t see any relationship between your document and anything described in the tutorial. It seems to me that you’re using a label-merge main document. That’s indicated by the presence of the numerous «NextRecord» fields, which are not used in a Directory/Catalogue merge – you won’t find any in the tutorial. The fact you’ve got such fields suggests you haven’t actually run any of the merges by copying the relevant fields from the tutorial into the accompnying mailmerge main document.

If I understand your requirements correctly, you need a merge using two keys: Date & Room. In that case, your mailmerge should be based on the tutorial example ‘Merge Records by Category Using Two Keys’. If you need an actual table, as distinct from a tabbed column layout, that’d need to be worked into the example, using the approach I’ve taken in the various table merge examples.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi cbenson,

I can’t see any relationship between your document and anything described in the tutorial. It seems to me that you’re using a label-merge main document. That’s indicated by the presence of the numerous «NextRecord» fields, which are not used in a Directory/Catalogue merge – you won’t find any in the tutorial. The fact you’ve got such fields suggests you haven’t actually run any of the merges by copying the relevant fields from the tutorial into the accompnying mailmerge main document.

If I understand you requirements correctly, you need a merge using two keys: Date & Room. In that case, your mailmerge should be based on the tutorial example ‘Merge Records by Category Using Two Keys’. If you need an actual table, as distinct from a tabbed column layout, that’d need to be worked into the example, using the approach I’ve taken in the various table merge examples.

Macropod,
Sorry for the miss understanding, I used the next record fields, to try and make it work the only way I knew how. Hence the confusion on your part. I do need the merge using the two keys. I’ll try again. I’ve tried cutting and pasting with no luck. Where do I type in the formulas that you show?

C

OK update,
This is what I tried this AM and it didn’t work. And when I went to preview it took away the spaces on the merged field.

{IF{MERGEFIELD Room}{Key1} {IF{MERGESEQ}>
}{MERGEFIELDRoom}{SETKey1{MERGEFIELDRoom}}}{IF{MERGEFIELD Day}{Key2}{MERGEFIELDDay}{SETKey2{MERGEFIELDDay}} } {MERGEFIELD Time} {MERGEFIELD FINISH} {MERGEFIELD Session_Title} {MERGEFIELD FIRST} {MERGEFIELD Last}

Reply | Quote

Here is everything again. Below all of this I was just playing to figure somethings out. And is how I would like it all to look. But with the complete room assignments by room and by day. With each room on a new page. Why cant I get this code to work??

Reply | Quote

Here is everything again. Below all of this I was just playing to figure somethings out. And is how I would like it all to look. But with the complete room assignments by room and by day. With each room on a new page. Why cant I get this code to work??

Hi cbenson,

I wasn’t confused – it was clear that your document lacked any of the catalogue/directory merge field coding and had label-merge coding instead.

Your latest attached document still didn’t have the correct field code structure – it looks like you mostly used a plain-text representation of the field codes, since most of the field braces had been replaced by ordinary braces.

Anyway, check out the attached. You’ll see both a correctly-formed mailmerge main document (Rooms.doc) and a sample output document. I also had to make some changes to your Excel workbook:
1. All double-quotes had to be replaced;
2: Hidden columns needed to be unhidden; and
3: Unlabeled columns needed to have labels assigned.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

HI Paul

I don’t know if I am right here. I believe this thread has been very useful, however I am not experienced enough to get what I want.

I want to send a letter to shareholders, some of them are trustees in a trust or directors in a Company. For these cases I would like to have the individual names listed under the “shareholder” to check if they are still the same. The problem is that in our excel sheet some of them are in the same row and different columns and some of the are below. Furthermore I would like to add other fields (phone number and address, tax rates etc) that don’t apply to all of them.

Currently I have it set up as a normal mail merge, but have started playing with directories, but it gives me a new page for each row. Can you tell me if it is possible through directories, before I carry on spending too much time and would I be able to send you what I have to review?

Kind regards Petra

Reply | Quote

I want to send a letter to shareholders, some of them are trustees in a trust or directors in a Company. For these cases I would like to have the individual names listed under the “shareholder” to check if they are still the same. The problem is that in our excel sheet some of them are in the same row and different columns and some of the are below. Furthermore I would like to add other fields (phone number and address, tax rates etc) that don’t apply to all of them.

Currently I have it set up as a normal mail merge, but have started playing with directories, but it gives me a new page for each row. Can you tell me if it is possible through directories, before I carry on spending too much time and would I be able to send you what I have to review?

Hi Petra,

Welcome to the Lounge.

For any mailmerge, you need your source data to have a consistent layout. Having some of the shareholder data in different fields and other shareholder data in different records could make it very difficult to get a useful output. Having empty fields (eg where you don’t have a phone number, address or tax rate) is easily handled, though.

Are you able to attach a zip file to a post with a representative sub-set of the data and mailmerge main document (both sanitised to remove anything confidential) and a document showing what you’d like that data to look like? That way I’ll be able to get a better idea of what the issues are.

As for getting a separate page per record (row), that suggests you’re still working with a letter merge, or that you haven’t set the key field(s) up correctly.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I’ve been having a problem with the final output of my email merge.
some of the data is misaligned

In the sample merge, these are the email letters that are misaligned ( names have been changed to protect the innocent as it were) : Ann Devon, Peter Franken, Howard Snyder, John Steel

I use Office 2007 though I get the same result in Office 2003

Reply | Quote

I’ve been having a problem with the final output of my email merge.
some of the data is misaligned

How is it misaligned? You have a tab immediately prior to most of your merge fields. Did the tab move? Were spaces inserted?

Where you have lists that continue onto additional lines, you might want to set up a hanging indent.

Note: If this level of formatting is not obvious, make sure to turn on “Show All” from the toolbar or Tools > Options > View (Word 2003) or Office Button > Word Options > Display (Word 2007).

Reply | Quote

How is it misaligned? You have a tab immediately prior to most of your merge fields. Did the tab move? Were spaces inserted?

Where you have lists that continue onto additional lines, you might want to set up a hanging indent.

Note: If this level of formatting is not obvious, make sure to turn on “Show All” from the toolbar or Tools > Options > View (Word 2003) or Office Button > Word Options > Display (Word 2007).

Do you mean in the table that I created with macropod’s word macro? There is a tab when I check the field code. There are also a lot of varied tab stops in the table that macropod’s macro creates.

I’ve enclosed a word document with samples of the mis-alignment. I’m not sure how to post them otherwise.

Here’s the code I used to create the table to be used as the email merge data-source.

And thanks for the rapid reply!

Reply | Quote

Hi chocolate raisin,

Your alignment problem is occurring because you haven’t set the tab stops in your ‘Main Doc sample’ document or your ‘Completed merge sample’ document to match your output requirements. To get the layout you’ve indicated in your ‘sample of email merge result’ document, you need to set a pair of left tab stops at 0.5in and 1.1in, respectively. The macro has no bearing on this – it’s purely a formatting issue for you as the output document’s creator to decide upon.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks All. Thank you macropod and jscher2000! That’s all it needed. And thanks for everyone’s posts. I’ve done a few types of email merges over this year using various techniques from this forum. A wonderful resource.

Reply | Quote

Hi Macropod,

I’ve been trying figure out how to insert text after all the different categories appear. I’m having a very difficult time.

I started off the mail merge using Microsoft’s example and I got the mail merge showing different categories on seperate pages. Now I need some trailing text to appear after the categories are showing but I’m stuck. Can you take a look at my code and see if you can give me any pointers.

I have attachd the documents that I’m working on.

Thanks in advance!

Carl.

Reply | Quote

I’ve been trying figure out how to insert text after all the different categories appear. I’m having a very difficult time.

I started off the mail merge using Microsoft’s example and I got the mail merge showing different categories on seperate pages. Now I need some trailing text to appear after the categories are showing but I’m stuck. Can you take a look at my code and see if you can give me any pointers.

Hi Carl,

Welcome to the Lounge.

Although the principles of the directory mailmerge code from Microsoft are basically the same as what my tutorial uses, the actual coding is quite different. For one thing, the basic Microsoft coding doesn’t support the addition of text before & after each record sequence. I suggest you download the tutorial and work through it, and the examples leading up to the insertion of text after each record sequence. Doing so, especially if you examine and try out the associated field coding examples in the accompanying mailmerge main document using the supplied data source, will help you to understand better what’s going on. The example you’ll need for what it appears you’re trying to do appearsunder the heading ‘Inserting Text After the Repeated Data’.

As an aside, I note that you haven’t coded the directory mailmerge code from Microsoft correctly – your document effectively has four independent blocks of field coding – all the mailmerge field code goes into a single block.

If you’re still unable to get things working after worked through the tutorial, you’ll need to post two documents – one showing your latest field coding and one showing what the final output from the sample data you’ve already posted is supposed to look like after the merge has been executed.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Macropod,

Thanks for the tutorial again. I went ahead and redid mail merge using your method. I finally managed to insert the text after the categories appear. I kind of wish it was easier to code the mail merge.

Thanks again!

Reply | Quote

Hi Carl,

I’m glad you got it sorted – less work for me

Yes, it’d be nice if this kind of functionality was built in, but it isn’t and the field coding in this and my other tutorials are pushing Word to do things its designers probably never intended.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

sorry for not coming back to you earlier, but I lost the webpage and my login, this time of the year.

Thank you for your offer, but I have no clue how I can upload any data here. Shame on me. I have sort of got the mail merge to work, however I would like to use it for other purposes as well.

Currently I would like to create a list that shows the company name up top and then some shareholder details in a list below with their ownership details and subtotals per company on a separate page.

I know this is part of your tutorial, but I just can’t get it to work.

If you could give me a hint how to upload the data, I would appreciate you helping me, otherwise you could maybe contact me by e-mail?

P

Reply | Quote

Hi Mephisto,

When you click on the ‘Add Reply’ button, the message panel opens. If you scroll down, just below the message panel you’ll see the buttons for managing attachments. After composing your message, simply browse to & select your file, click ‘upload’ then ‘Add Reply’ to complete a post with an attachment.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

GREAT TUTORIAL! They are going to have to add a rating or tagging system to this Lounge so items like this can be tagged!!

Reply | Quote

Actually there is a “Star Post” system with that purpose in mind, though it got buried a bit in the conversion from Woody’s to Windows Secrets. You can find them in Lounge Matters as pinned posts – see Office Applications to find this post there.

Reply | Quote

I am hoping someone can help me. I had tried the tutorial and it works fine except between all the records there are 3 blank lines. I have tried and tried and can not figure out how to remove these lines.

Here is an example:

ATLANTA

Smith 3,000.00

Gates 50,000.00

Henderson 10,000.00

HOUSTON

Jones 8,000.00

Kelley 9,000.00

Peterson 0.00

I would truly appreciate any help. Thanks

Reply | Quote

I had tried the tutorial and it works fine except between all the records there are 3 blank lines. I have tried and tried and can not figure out how to remove these lines.

It would be very helpful to see the “main document” that contains your merge fields.

Reply | Quote

Hi Gigi,

OK, my first observation is that you are probably not using the field coding from my tutorial, but the field code from one of the MS website pages instead:
at:
http://support.microsoft.com/?kbid=211303
or:
http://support.microsoft.com/kb/294686/en-us

The sample output data you posted is from one of the above – not from the mailmerge datasource attached to my tutorial. If you’re using my field coding with the MS sample data, there wouldn’t be any excess spaces unless you modified the field code and/or had extra lines after the field code in your mailmerge main document.

Sorry about the delay in replying – I’m travelling with only limited internet access.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I would like to thank you for these detailed instructions on Mail Merge. I wouldnt have known where to start. I have come across a problem though and I hope you can help. I have got as far as calculating group totals. This worked perfectly the first time I ran it however the second time I ran the mail merge the document seemed to have saved the previous totals and the totals al accumulate.

I.e. 256 rows in Excel – Total to approxmately €500,000
1st time I ran merge each group calculated succussfully
2nd time I ran merge each group was correct total plus €500,000
3rd time I ran merge each group was correct total plus €1,000,000
4th time I ran merge each group was correct total plus €1,500,000
5th time I ran merge each group was correct total plus €2,000,000
6th time I ran merge each group was correct total plus €2,500,000
and so on… Any ideas?

Reply | Quote

Hi Lynn,

Your field coding has some errors. For example, the first line is missing some double-quotes and the space that’s needed to separate two expressions. Instead of:
{IF{ MERGESEQ }= 1 {SET Key “”}{SET Group -1}{ SET Tot1 0 }{SET Tot2 0}{SET LastKey { Key }}}
you should have:
{IF{ MERGESEQ }= 1 “{SET Key “”}{SET Group -1}{SET Tot1 0}{SET Tot2 0}” {SET LastKey { Key }}}

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks a million. That fixed it.

Regards
Lynn

Reply | Quote

Hi macropod, Ive run into another issue with this and hoping you can help me.
I have attached my mail merge document and the data source.

When I run the mail merge document, I get the correct results down as far as the Broker field, and the incorrect results (results from next row down) from Broker onwards.
I obviously have a something wrong somewhere, can you spot it easily?
Thanks

Reply | Quote

Hi Lynn,

The error is because you’ve got the broker fields in the wrong place. Instead of following the totals, they should occur either just before the line:
Policies Insurer Last Renewal Premiums
or immediately after the mergefields related to that line (ie the last {MERGEFIELD Last_Years_Premium # €,0.00}) – depending on what layout you’re trying to achieve.

Note also that your checkbox formfields won’t survive the merge.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks a million. I had tried moving lots of stuff around and couldnt crack it. Thanks again.

Reply | Quote

Hello there

I do hope you can help me…..

I have read with interest your amazing article and tutorial…. however – I am still baffled! I am basically trying to create a reminder letter or email to clients to remind them of courses their delegates need to attend during the current year. I will be using the emails sheet in the workbook. My field names are name, address, expiry date and email. There could be several names per address (which is the client). So, I need one letter or email per address which lists all the names and expiry dates for that address.

I have attached my word document and the database – can anyone help please?

Hope this makes sense! Many thanks in advance

Jan

Reply | Quote

Hi Fawa,

Apologies for the delay in replying – I haven’t been getting notifications about posts to this thread.

I’ve taken a look at your mailmerge main document and I can’t see any indication of the use of fields from the tutorial. The fields you’ve used instead aren’t correct for this. Please read the tutorial carefully and adapt the relevant example fields to your own needs. If you get stuck, re-post with a copy of the document that includes the appropriate field codes from the tutorial and I’ll see what I can do to guide you through the process.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi there,

Thanks for the tutorial you have supplied for complex mail merges.
I have tried to duplicate what you have done in your examples with my own data, however, i am not successful.

I want to be able to produce a “letter” per insured, and then at the bottom of this insert the Peril Type, followed by the data specific to this.

I have attached the docs.

Could someone please help out a poor sole, (who has now spent little over a week trying to get somewhere with this???)

Reply | Quote

Hi Lulabell,

See attached. I changed your ‘Key’ field’s name to ‘Insured’, so as to avoid confusion between the field name and the bookmark name used in the mailmerge main document.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi,

I have followed the document provided and I seem to be having a problem. All goes well with the data generation but I can´t seem to insert text before all the data generation occurs.

Normally I would do this in the MERGESEQ = 1 section and for text after the data generation I would do this logic in the LASTITEM section, but since the MERGESEQ section has the Key logic I can´t add text there (I mean static text).
I need the text to only be presented once and not repeated with the data.

Attached is a doc file with what I got so far.
28086-TemplateFinalV3

Any help/tips is appreciated.

Thanks in advance

Reply | Quote

Hi Agenteusa,

You field code is missing part of the logic. Try the attached.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hmmm it still didn´t solve it for me. Now the headers are gone and I still don´t understand where to put text or an image if I don´t want it to be repeated.

Corrected file (as you suggested):
28090-CorrectedFile

Output from my original file:
28089-OriginalOutput

Output from the corrected file:
28091-FinalOutput

Am I still doing something wrong?

Thanks for the patience. Appreciate it.

Reply | Quote

Hello,

I have followed this tutorial and got my info to merge correctly to a new document. THANK YOU! It works perfect.

The only thing I am confused about is the “Merging by Catalog/Directory to Email” process.

For the Email Mailmerge Main Document, it states “You’ll need to format the paragraph containing the Data field with as many tabs as there are data fields in your primary data source”. What does it mean by “tab’s”? Also, if I have an IF statement returning one of two data fields, do I put the IF statement in this document?

For the word file named “EmailDataSourc.doc”, do we just create a two row table with the column titles of “Recipient” and “Data”? It shows xxxx’s in the second row, do we put xxx’s as well?

Thank you for the clarification. Sorry if this is obvious but I can’t figure it out.
Dawn

Reply | Quote

Hi Dawn,

Regarding the tabs, say you have five data fields in your source that need to be ‘Data’ output. In that case you’ll need four tabs – one separating each field in the ‘Data’ column, so that you end up with:
«Item1»«Item2»«Item3»«Item4»«Item5»
If your data source has two fields, only one of which will be included in the final output then, yes, this is probably the best place to put them. Suppose, your data source actually has six data fields, two of which indicate, say, gender (M/F) and get output at Item2 above. In that case, you’d replace the «Item2» mergefield with a field coded along the lines of:
{IF «Male» “” «Male» «Female»}
You could do likewise with the substitution of a field’s value for some other constant, like:
{IF «Gender» = “M” “Male” “Female”}

For the ‘EmailDataSource’ document’s table, yes, you only need the two rows shown – the macro & merge process take care of the rest. As for the ‘x’ strings, no, you don’t replicate those – they’re just in the tutorial document to show where the various ‘Recipient’ and ‘Data’ fields are to go.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi
I have found this all really useful and I’ve just about got my head around the logic that makes it work.

I’m having problems trying to do two sets of categorised and non-categorised text per record. I need the merge to show:

[*]HEADER per primary key
[*]REPEATING FIELDS until primary key changes
[*]TEXT and FIELD (which is the same in all rows per primary key)
[*]ANOTHER REPEATING FIELD.

I can make this work fine without the second repeating field but I always get the second set of repeating fields appearing within the first set.

I’ve tried setting bookmarks and conditions (and it’s easy enough to repeat a column in the data if necessary) but no luck.

Is there any way of setting bookmarks or some other method to allow this to happen? The data could be resorted if necessary.

I hope the attached files make it reasonably clear what I’m trying to do. There are loads of carriage returns in at the moment to make the code easier to read. Once these are removed and the table-joiner macro run, it does tidy up.

Thanks in advance for your help with this.

Reply | Quote

hi Gary,

The tutorial really doesn’t contemplate multiple sets of repeating fields per group, interspersed with non-repeating text. Whilst I supose what might be possible, it could take a considerable effort to develop. Indeed, even the examples you see in the tutorial took many hours of work to develop and some were considered impossible before I developed the techniques used there.

Unless you can restructure your merge so that only one set of repeating data is required per group, the only workaround I can suggest at this stage is to do two separate merges (one for each set of repeating data), then combine the documents – perhaps using a macro.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi all ;
i am using the two keys in my mail merge i have to do the group total for the key2 i don’t know how to do it i have the post where i can do it on the first key but if we are using two keys and i have to calculate the group total on key 2 dont know how to do this can anyone help me please it is urgent

Reply | Quote

Hi addiaddi,

See attached. If you copy the field code from this file into the tutorial’s mailmerge main document and execute the merge, you should get the sub-group totals. Do note that, with the tutorial’s source file, you’ll get a field calculation error for the dummy last record.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi Macropod it is still giving the error field calculation error for record.I am attaching the file what i am doing actually product option is my first key in the file and product type is the second key i want to do the subtotal and the totlal for the non option product (means option field is no) please help.please find the attachment

Reply | Quote

Hi addiaddi,

it is still giving the error field calculation error for record

I did say it would do that. Your attached document also doesn’t show any indication that you’ve implemented the new field coding.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

addi
We can’t help you when you don’t provide enough information to work out what you want to do. You will need to attach sample data so we can understand what the problem is.

If you need to calculate something as part of the merge, it may be easier to do the calculation in the data source (excel or access) than it is to do the calculation after importing the other data.

Reply | Quote

Hi macropod i am attaching the new word file i tried you code on my word file and also the tutorial file removing the last dummy record from the tutorial excel file but in the both scenario i am getting the error and no calculation is being done

Reply | Quote

I have re-tested my previous attachment and have confirmed that it does do the calculations. I have also looked at your latest attachment and there is still none of the calculating field coding in it. How can you expect me to tell you what you might be doing wrong if you don’t provide any evidence of what you are doing?

The file I am now attaching (which also works with the tutorial’s data once you copy the field coding into the tutorial’s mailmerge main document) takes care of the calculation error message.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi macropod sorry again i did both of the files this is the actual file i am working on sorry for wrong files

Reply | Quote

Hi addiaddi,

I would be surprised if the your document didn’t produce some output that you could use for further refinement for the field layout. Even so, I had to do a considerable amount of re-formatting of the content you supplied because you had a heading row for your table, but none of your data rows were in a table format. I note too that, despite the fact I’ve posted an update that doesn’t generate the field calculation errors, you have chosen not to use it.

Try the attached, which uses the latest revisions of the field coding I’d posted. To get the totals for the last group, you will need to add a dummy data row to your data as the last row, with a 0 as the ‘Product Optional’ value.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi macropod i apologize for the inconvenience but i just used your lates file you sent me with the name mail merge sub group calculation i used that file with the tutorial Catalogue Merge Data and i am still getting the error and no cal calculation is being done it is just showing 0 i am attaching both the files.i am really thankful to you

Reply | Quote

Works just fine for me. What regional settings are you using on your PC?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi macropod the regional setting is (UTC+01:00) Brussels, Copenhagen, Madrid, Paris

Reply | Quote

That is a time-zone setting, not a region setting. However, it does indicate that you’re probably using a system that does not use English-language delimiters. In that case, you will need to make the following changes to the field code:
(Group,2) should be (Group;2)
$,0 should be $.0 or $0
These are the normal kinds of changes one needs to make when using regional settings for systems that do not use English-language delimiters.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks alot it is working i really happy ok the last thing that i have to ask as i am getting the data from microsoft Dynamic crm we do not have the last dummy record in the data what should i do for this one to get the total on the last record as well

Reply | Quote

I can’t help you with that. As clearly stated in the tutorial, the technique you’re using relies on the presence of a dummy last record. The only other way of automatically achieving the same kind of output is to have another field in the data that has the sub-group totals – on the last row for each sub-group. This, too, is explained in the tutorial. If you can’t do either of those things, you will need to add the data for the last row manually.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod one thing i have to ask that in mscrm we have currency type of number field how can i convert this into numeric integer or decimal to calculate the total please help is there any way to convert the type in mail merge

Reply | Quote

If the problem is with adding mergefield values that already have a currency symbol before the values, you could simply add the ‘* arabic’ switch to the mergefields concerned.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

hi macropod sorry for late reply …ok i have solved those issues but i have another one i want to calculate the subtotal for non optional products how can i achieve that and also ths sunm of all sub total of non optional products please help

Reply | Quote

Hi macropod did you see my last message please help

Reply | Quote

Hi macropod did you see my last message please help

Please learn to be patient. Some of us have lives to live outside of answering forum questions. Furthermore, this kind of field coding doesn’t just materialize out of thin air; if it was that easy you wouldn’t need help.

You asked for code to calculate the group totals as well as the sub-group totals. See attached.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

thanks alot macropod it works

Reply | Quote

Wow! Thank you for putting this together. I will be adding a link to it from my Mail Merge page.

Reply | Quote

if the LastItem=”1″ so can i run the merge again from start like a loop???

Reply | Quote

if the LastItem=”1″ so can i run the merge again from start like a loop???

I have no idea what you’re referring to. There is no ‘LastItem’ in the field code I provided, or in any of tutorial examples.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod;
yes there is no field used in your tutorial but we can use LastItem as a field codes in mail merge ok what i want that loop through the records again Example:in our case when we do mail merge we throgh all the records and and display them in the desired format when we finish with the first time i want to do it again in the same tempalte becauee i want to display data in one format an then same data with another format in the same template is it possible to loop throguh the records

Reply | Quote

There is no Word field that can test whether you have reached the last item in a mailmerge, much less implement a loop. If you’re using a dummy last record as indicated in the tutorial, you can know it’s the last item, but Word does not – as far as Word is concerned it’s just another record. You will need to execute two separate mailmerges. Since you want two separate formats, I suggest you create two separate mailmerge main documents – one for each format. The only way to implement a loop would be with vba but, if you’re using vba, then you don’t need all the field coding that goes with the non-vba solution.

If you want to explore vba solutions, you may want to try a Many-to-One Mail Merge add-in from either:
• Graham Mayor at: http://www.gmayor.com/ManyToOne.htm
• Doug Robbins’ Windows Live SkyDrive page at: https://skydrive.live.com/?cid=5aedcb43615e886b#cid=5AEDCB43615E886B&id=5AEDCB43615E886B%21111

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod;

as we discuss about the looping through the datasource can you please suggest me any tutorial or example for vba solution because i am struggling please help

Reply | Quote

Hi addiaddi,

The barebones code could be something like:

Code:

Dim i As Long
With ActiveDocument.MailMerge.DataSource
  For i = .FirstRecord To .LastRecord
    'Do your 1st round of processing here
  Next
  For i = .FirstRecord To .LastRecord
    'Do your 2nd round of processing here
  Next
End With

I don’t understand why you’d want to do something like you’re suggesting, though, as you could just as easily (if not more so) execute two mailmerges – one for each format, and preferably using two different mailmerge documents.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Cross-posted at: http://www.vbaexpress.com/forum/showthread/?t=42175
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

thx Macropod ;

this is the client requirement.that’s why i really appreciate your replies.i will have to do something through macro vba thanks anyway

Reply | Quote

I am sorry but I really don’t understand how this is a tutorial. I still don’t know how to add the actual code to the word doc, where is step 0?, how or where do I select a key?
What am i missing?
jos

Reply | Quote

Hi jos,

The tutorial explains that … on page 1.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Great tutorial thanks. I am having trouble with email part. I have tried to follow the steps but no email generated. I changed to wdSendToNewDocument and also no new document created.

I have attached my documents and would appreciate if you could share some light where I have done wrong.:confused:

Oh, I have to change Private Sub to Sub as the Macro will not proceed. Can this be the problem??

Thank you so much.:)

Reply | Quote

The only macro you should be running is the one named ‘RunMerge’. There is no need to make any changes to the macro code. As the tutorial clearly states:

The ‘RunMerge’ macro takes care of the whole process, executing a Directory/Catalog merge on the raw data to consolidate the groups, then taking the consolidated data and executing an e mail merge to generate the e mails.

Your ‘Email Merge Main Document’ also has none of the field coding described in the tutorial. The ‘DATA’ field in your ‘Email Merge Main Document’ is not constructed correctly (it’s a mergefield and, so, should be coded as {MERGEFIELD DATA}) and should be in your ‘EmailDataSource’ document. The field coding that should be in your ‘Email Merge Main Document’ is instead found in your ‘EmailDataSource’ document.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the quick response. As I am a newbie for this, sorry, would it be possible to have a sample of ‘Email Merge Main Document’ and ‘EmailDataSource’? I am really struggle and confused to figure out how construct it. :confused:

Thank you very much.:)

Reply | Quote

You could rename your ‘EmailDataSource’ document to ‘Email Merge Main Document’, then create a new ‘EmailDataSource’ document that has nothing more than copies of the «Data» and «Recipient» fields from the tutorial.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi, thanks for the tutorial sharing. Unfortunately, looks like I unable to get the final result as per the guideline by using my own scenario. :confused:
Kindly refer to my attached files, as indicated by the file name, the xls is the datasource I used, the mailmerge document is my work around and the desired result is the sample result I type in manually. I need to print the letter with a criteria of page break by Holder field. Kindly advice, thanks.:)

Reply | Quote

Thanks. It worked. Many thanks.

Reply | Quote

Hi Autumncicada,

Your mailmerge main document isn’t constructed anything like the examples in the tutorial, for which you need to use field coding based on the ‘Calculating Group and Sub-Group Totals’ topic and after which you can run the macro in the ‘Merging to Tables’ topic.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi, I tried to copy the exact code as per the tutorial and paste it to mailmerge document, but I am unable to get the result as shown in tutorial (attached with screen shots). May I know is there any setting I have overlooked? :confused:
Thanks.

Reply | Quote

If you navigate to the ‘Calculating Group and Sub-Group Totals’ topic, select the field and press Alt-F9, you’ll see the complete field code. That’s what you need to copy & paste, then modify to suit your specific needs. It doesn’t seem you’d done that for the mailmerge main document you attached to your previous post.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi, can you advice how to determine which is sub-group? As I am only plan to use Holder column as the main key. Many thanks

Reply | Quote

Hi, can you advice how to determine which is sub-group? As I am only plan to use Holder column as the main key. Many thanks

See attached. I’ve stripped out everything from the tutorial that you don’t need.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi, thanks a lot for the help. I tried to run the merge and check the result, notice that the contents of the last page does not showing the respective paragraph. May I know why? Attached with the result. Thanks

Reply | Quote

As described in the tutorial, you need to add a dummy data row with a ‘0’ for the last row of the Holder column.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

When I open the Mailmerge Main Document.doc it asks to updated. I say yes and change the source to the saved Catalogue Merge Data.xls.
I cannot see the code on the Mailmerge Main Document.doc but the message “If you haven’t read the tutorial document, please do so before using this document“.

I am using Word 2007 and made sure the settings are according to your suggestion.

Reply | Quote

When I open the Mailmerge Main Document.doc it asks to updated. I say yes and change the source to the saved Catalogue Merge Data.xls.
I cannot see the code on the Mailmerge Main Document.doc but the message “If you haven’t read the tutorial document, please do so before using this document“.

I am using Word 2007 and made sure the settings are according to your suggestion.

There are no field codes in the mailmerge main document – they’re in the tutorial, which is why the mailmerge main document has that message.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the reply Paul,

Yes the first link is broken and here is the link I wanted to show.

http://lis.dickinson.edu/technology/training/Tutorials/ms2007/word/word_directory_merge.pdf

The second link is the one I mentioned as a similar problem. I couldn’t find a fix to it and hence asked you in this forum.

The main thing to look at is the repeating text for each record. I needed to just output text and then have the categorization done separately on a single document.

Reply | Quote

The main thing to look at is the repeating text for each record. I needed to just output text and then have the categorization done separately on a single document.

The tutorial shows how to do the categorisation. From what you’ve described, it seems the field under the topic ‘Merge Records By Category’ would serve as the basis for your project. If you want to output some content only when the first record is merged, simply modify this part of the field:
{IF{MERGESEQ}= 1 {SET Key ” “}}
to, for example:
{IF{MERGESEQ}= 1 “{SET Key ” “}Record 1”}

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Paul,

In fact I wanted to output some content before any record is met and then have a list of the records.

for example.

in the document, I would like to see the following.

“This is a test specification for the application that serves your company.

List of <> and <>

If there are any issues in the test please report the following department for the fix.”

The out put should look something like

“This is a test specification for the application that serves your company.

List of Test1 and Specification_content1
List of Test2 and Specification_content2
List of Test3 and Specification_content3
List of Test4 and Specification_content4
List of Test5 and Specification_content5
List of Test6 and Specification_content6

If there are any issues in the test please report the following department for the fix.

I hope you understand my situation.

Cheers

Reply | Quote

Hi Paul,

I also found this post of yours which is a bit similar to my problem.

http://windowssecrets.com/forums/showthread//144391-Word-2007-Directory-mail-merge-with-cover-page

Instead of having a single cover page, I wanted a kind of text which remains non redundant for each record.

Cheers

Reply | Quote

Simply make the adjustment mentioned in my last post to whichever of the tutorial’s examples otherwise suits your needs.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Paul.

Reply | Quote

Hi- I am in desperate need of help with a mail merge. I have the recipient list in an Excel spreadsheet. What I am trying to make is a phone book directory in Microsoft Word. I want it to automatically move to the next recipient but the “update label” button is greyed out. I am not sure if I should set this up as a Word doc, directory or labels. Either way, it seems impossible to have it automatically move to the next record. I am trying to use a template in Office 2010 called Membership Directory and turn it into two columns, also don’t know if I should do this in a table?

Any advice?
Thank you- Suzanne

Reply | Quote

Hi Suzanne,

The ‘Update Labels’ option is only applicable to a labels mailmerge. From what you’ve described, it seems all you need is a simply directory merge in a document with a two-column page layout. In this case, all your mailmerge main document would contain, in terms of mergefields, is a single mergefield for each of the data fields that you want to output from the data source.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Excellent tutorial, I got the merge to work, but there’s still one thing I can’t figure out.

Here are my Merge Results:

——————————————–
Room: Lunchroom – February 11, 2013

Interview Candidates
10:00AM – 11:00AM

Mid Year Review
11:00PM – 2:00PM

Goals Meeting
2:00PM – 6:00PM

——————————————–
Room: Boardroom – February 11, 2013

Mid Year Review
10:00AM – 11:00AM

Interview Candidates
11:00PM – 2:00PM

Strategy Meeting
2:00PM – 6:00PM

——————————————–
Room: Rooftop – February 11, 2013

New Hire Meeting
11:00AM – 11:30AM

Asset Meeting
3:00PM – 3:30PM

Award Meeting
4:00PM – 6:00PM

I would like to place the results in separate cells on a table (so we could print them as labels). I’m having trouble figuring out how to put them in different cells of a table. For example, I created a mockup of what I envision:

Here is the code that I am using:

Any ideas?

Reply | Quote

Unless your data are structured in a way that requires a catalog/directory merge, I don’t understand why you’d use anything other than a label merge for this.

If your data are structured in a way that requires a catalog/directory merge, what you could do is use a two-column page layout, with a single cell for the merge output. make sure the cell is also configured to disallow breaking across page breaks. With that approach, each successive cell will be added below the previous one and, once a column is filled up, the next cell will begin a new column.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi amlong,

When developing the field code, I hadn’t anticipated the data having consecutive records with different state names but the same city names. To address that, change the field code that appears as:
{MERGEFIELD City} {Key2}
to:
{={IF{MERGEFIELD City}= {Key2} 1 0}*{IF{MERGEFIELD State}= {Key1} 1 0}}= 0
I’ll update the tutorial shortly.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

HI Paul

That worked perfectly!! I would have never gotten this without your help. Thanks!
Andrea

Reply | Quote

Hi again

I figured out a trick to make the last State show properly.

Click on Mailings > Edit Recipient List. This will pull up the Mail Merge Recipients box. Under Refine recipient list, pick Sort. This will pull up the Filter and Sort box. In Sort Records, go to Sort by: and select State in Ascending order.

Once you have all your letters complete and need the last letter, return to the Sort Records box, but now sort State in Descending order. This will put that last State in the first letter position and it will behave properly.

Thanks!

Reply | Quote

I figured out a trick to make the last State show properly.

Click on Mailings > Edit Recipient List. This will pull up the Mail Merge Recipients box. Under Refine recipient list, pick Sort. This will pull up the Filter and Sort box. In Sort Records, go to Sort by: and select State in Ascending order.

Once you have all your letters complete and need the last letter, return to the Sort Records box, but now sort State in Descending order. This will put that last State in the first letter position and it will behave properly.

I don’t really see why you’re doing that. Did you read the ‘Grouping the Records‘ portion of the tutorial?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yes, but I did not want to have to modify the excel data source.

Reply | Quote

Hi,
Thanks a lot for the wonderful tutorial. Need some help in refining my code.
This is the code I am using for sending emails to my customers. The issues i need to sort out
33308-code
1. The information needs to go in a tabular format.

2. The mail sent doesnt have a copy in my sent items ..so it is difficult to check whtehr the mail has gone and also need it for our reference

3. is there any other way to disable the notification we receive every time a mail is sent. It is very painful to press “Allow” button everytime a mail is sent to a cutomer and my daya would have atleast 50-60 customers. You have mentioned about an add in to be downloaded..not sure if our IT would permit that.

Thanks a lot

Reply | Quote

Hi irpriya

1. The information needs to go in a tabular format.

That would require the mailmerge document be sent as an attachment. There is some code for doing that here: http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm but it would take some work to mary that with what the tutorial does, as the code in the tutorial inserts the content directly into the email body.

2. The mail sent doesnt have a copy in my sent items ..so it is difficult to check whtehr the mail has gone and also need it for our reference

When testing the code, I find that the sent emails appear in my email ‘sent items’. I use Windows live mail but, as indicated in the tutorial, have Outlook installed.

3. is there any other way to disable the notification we receive every time a mail is sent. It is very painful to press “Allow” button everytime a mail is sent to a cutomer and my daya would have atleast 50-60 customers. You have mentioned about an add in to be downloaded..not sure if our IT would permit that.

If you use Outlook as you email program, you can send the emails in HTML format and not get the prompts. To do that, you’d change:
.MailFormat = wdMailFormatPlainText
in the code to:
.MailFormat =wdMailFormatHTML
That will also allow your email’s body to be formatted.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul,

Thanks for the quick reply.
Point 2 and 3 have been taken care of…thank you so much

For point 1- plz let me know the better way of taking care of formatting part of the contents if it is not possible to send it in a tabular format
this is how the mail goes

“Hi,

Please find attached the details of your order:

Global Vendors Order Chasis Comments
30581
WR122
Team development and communication
38419
WR227
Awareness of fit to future role
31535
PSY202
Leadership development
30550
PSY201
Relationship with stakeholders
33388
PSY202
Conflict management

Please get in touch with us for any queries.”

This is not readable and the vendor may not understand the details. However, would like to send it in this format. 33309-mailformat

Is there a workaround?

Thanks for your time and support

Regards,
Priya

Reply | Quote

When sending the emails as plain text, you have no control over the layout, and all the fields for one row from your table should appear on the same line, with a single space between them, not wrapped as per your post. Adding tabs to the ‘Email Merge Main Document’ won’t make any difference in this case.

If you want full control over the layout, you will need to send the tables in documents attached to the emails (per the link I posted) or, if HTML emails provide adequate support for tabbed layouts and that meets your needs, in a tabular format, rather than as a table, in your email.

As an alternative, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at https://skydrive.live.com/?cid=5aedcb43615e886b#!/?cid=5AEDCB43615E886B!cid=5AEDCB43615E886B&id=5AEDCB43615E886B%21566
I don’t know whether or how well those utilties might meet your needs, though.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I tried doing changes in the tabs in the Email Merge Main Document but as you have mentioned ..it doesnt work.

Would you please let me know why all the fields from one row is not appearing on the same line… if I can achieve that i think that wouild be good enough to start with..

Thanks
Priya

Reply | Quote

Would you please let me know why all the fields from one row is not appearing on the same line… if I can achieve that i think that wouild be good enough to start with..

Hi Priya,

I can only conclude there’s a problem with your mailmerge main document. Does the ‘EmailDataSource’ document produced by the process have the same format as the one in the tutorial?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yes.. it is in the wrap format…attaching all the test files for your reference

Thanks
Priya

Reply | Quote

Your data are not formatted as per the tutorial’s example. In your ‘Data’ columns, you have paragraph breaks instead of tabs between the field entries for ‘Order’, ‘Chasis’ and Comments’. You also have nothing for the ‘Global Vendors’ field.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul,

Sorry to trouble u again …….I have changed the formatting and it was going properly. But once started working on the real data, encountered one more issue
I have a description column in my data sheet which is very long so it is screwing the formatting again and the data is not going in one line.

If I remove the description column, it goes in a proper line.

Plz suggest… attached files for your reference.

Regards,
Priya

Reply | Quote

In that case, especially given that the descriptions alone require more than one line, I suggest you:
1. Delete the ‘Description’ column
2. Insert a paragraph break followed by the ‘Description: ‘ and your Description mergefield into the ‘Need by date’ column.
This will cause the output to show ‘Description: my very long item description’ below the other details for each record. You could even include an extra paragraph break after the Description mergefield in the ‘Need by date’ column, to create a space between each record’s description and the next record.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Have tried different ways to get the rite format but dont know where i am going wrong…

changed my file as you suggested but the “Description” column is still not well placed…
attaching updated files for your review…

thanks
Priya

Reply | Quote

See attached.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks a lot Paul. It is looking much better now

Regards,
Priya

Reply | Quote