Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mail Merge List by Name 1

Status
Not open for further replies.
supportservice

supportservice

Technical User
Mar 12, 2012
63
I found an article and I can't seem to make it work.

This is the part that I would like to use:

[link]http://support.microsoft.com/kb/211303/en-us[/url]

I've replaced it with my field names here:

Code: 
{ If { MERGESEQ } = "1" "{ MERGEFIELD «VendorName»}" ""}
{ SET Place1 { MERGEFIELD «VendorName»}}
{ If { Place2 } <> { Place1 }"
----------------------------Page Break--------------------------------
{ MERGEFIELD VendorName }

{ InvoiceNo: «PayInvoiceNo»}{ InvoiceDueDate: «InvoiceDueDate»}{ NetInvoice: $«NetInvoice»}" "{ InvoiceNo: «PayInvoiceNo»}{ InvoiceDueDate: «InvoiceDueDate»}{NetInvoice: $110.40000000000001}" }{ SET Place2 { «VendorName»}}

It is not resulting like the example.

This is what I am getting:
Code: 
{ If { MERGESEQ } = "1" "{ CINTAS - Desert}" ""}
{ SET Place1 { CINTAS - Desert}}
{ If { Place2 } <> { Place1 }"
----------------------------Page Break--------------------------------
{ CINTAS - Desert}

{ InvoiceNo: 150679525}{ InvoiceDueDate: 9/10/2012}{ NetInvoice: $110.40000000000001}" "{ InvoiceNo: 150679525}{ InvoiceDueDate: 9/10/2012}{NetInvoice: $110.40000000000001}" }{ SET Place2 { CINTAS - Desert}}[/coce]

The fields laid out in this example produce a catalog listing on separate pages as follows:

It should produce:
ACC Business

12345 9/20/12  $3,000
23456 9/30/12 $50,000
45566 9/27/12 $10,000

----------------------------Page Break-----------------------------
CINTAS - Desert

InvoiceNo: 150679525  InvoiceDueDate: 9/10/2012  NetInvoice: $110.40000000000001
InvoiceNo: 150679587  InvoiceDueDate: 9/10/2012  NetInvoice: $235.00000000000001


What am I doing wrong?
 
Sort by date Sort by votes
To see how to use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version) with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
or

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some recent, worked examples, see the attachment to the posts at:

Alternatively, you may want to try the Many-to-One Mail Merge add-in from Graham Mayor at:

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
Thank you for the information. I'll take a look through them all.
Hopefully it'll resolve what I am looking for :)
 
Upvote 0 Downvote
Ok so first and foremost in applying code is how?

I do not get that part.
I am not getting how to get to the point of applying/using/inserting the necessary codes.
I have no idea how to use code in Word at all. I do not know where to begin. I have never knew you could code like that in Word. I have very limited formula use in Excel and Access.

It's easy enough to do the simple Mail Merge and selecting the fields. Which is an insertion of «VendorName». This is the Key field that I need to use.

With that said, I need assistance on how to even begin applying the code and properly applying it.
I simply copied and pasted the support article and what I got was not what the example showed. The result showed the code :-(

I would want to use the example on page 12:

Calculating Group and Sub-Group Totals
Although the ‘Using an Extra Field to Generate the Trailing Content Per Group’ example (page 10) generates group totals, that is only possible because those totals exist in the source data. With the following field code construction, you can extend the ability of the ‘Using a Dummy Last Record to Generate the Trailing Content Per Group’ example (page 8) to calculate the group and sub-group totals (the additional field coding is highlighted in red), even though they don’t exist in the source data. This example uses tables for the output, though that isn’t necessary – it’s just to show what’s possible
Click to expand...

And send the emails to each Vendor the list of their invoices. Page 23-25.
 
 https://www.dropbox.com/s/h7kss5jg053ji8i/OpenInvoices.zip
Upvote 0 Downvote
Have you read the tutorial's Introduction?

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
It explains the Key field and how it's "set" but I do not know how to 'set' it ... meaning how do you use that code?

I can't simply type and place in the Word doc. It does not work.

Setting up for Mail Merge is simple enough.

The Grouping of Records is done.

I know how to get this one per but not the list as in the examples:

Code: 
Merge Records By Category
This example uses the ‘City’ field sample data from the accompanying Excel workbook as the key field.
«State»	«City»

	«Representative»	«Sales»

The fields laid out in this example produce a sorted list as follows:
ACT	Canberra

	Yang	$2,100

NSW	Coffs Harbour

	Roulston	$4,000

NSW	Dubbo

	Kristiansen	$6,700

NSW	Newcastle

	Avard	$3,400
	James	$8,600

Where I am stuck on is how to apply the Key and Grouping for the list per the Key accomplished.
It's like it's missing at least one step for me to figure out how to go from the simple basic Mail Merge to using code. Like the Key and totaling, etc ...
 
Upvote 0 Downvote
Using the examples in the tutorial requires little more than copying the field codes for a given example into your own document and replacing the various mergefield names with your own. All you need to do in that regard is to take the time to understand which tutorial merfield name gets replaced by a given mergefield name from your own setup. I cannot do that for you as I know nothing of your data.

As for setting the 'key' field, you don't; you simply replace 'City' in {SET Key {MERGEFIELD City}} with whatever field name you want to use for grouping the records. For the 'Calculating Group and Sub-Group Totals' example, which you say you want to use, there are two 'key' fields - 'key1' and 'key2'.

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
Well that is what I did as I submitted in the original post. I replaced the words after MERGEFIELD like City with VendorName as that is my "Key".

The result I got is not what was shown.

Code: 
This is what I am getting:
[code]{ If { MERGESEQ } = "1" "{ CINTAS - Desert}" ""}
{ SET Place1 { CINTAS - Desert}}
{ If { Place2 } <> { Place1 }"
----------------------------Page Break--------------------------------
{ CINTAS - Desert}

{ InvoiceNo: 150679525}{ InvoiceDueDate: 9/10/2012}{ NetInvoice: $110.40000000000001}" "{ InvoiceNo: 150679525}{ InvoiceDueDate: 9/10/2012}{NetInvoice: $110.40000000000001}" }{ SET Place2 { CINTAS - Desert}}[/coce]


It should produce:
[code]ACC Business

12345 9/20/12 $3,000
23456 9/30/12 $50,000
45566 9/27/12 $10,000

----------------------------Page Break-----------------------------
CINTAS - Desert

InvoiceNo: 150679525 InvoiceDueDate: 9/10/2012 NetInvoice: $110.40000000000001
InvoiceNo: 150679587 InvoiceDueDate: 9/10/2012 NetInvoice: $235.00000000000001

That is why I am asking for help. What am I doing wrong that it is not displaying as the example.
I replaced the proper words with my fieldnames as you can see.
 
Upvote 0 Downvote
What you have posted suggests you replaced {SET Key {MERGEFIELD City}} with { CINTAS - Desert}. That is not what the tutorial, or my previous post, says to do. You should only have replaced 'City' with 'VendorName', so that you ended up with:
{SET Key {MERGEFIELD VendorName}}

What you have posted also suggests you may have typed the braces (ie '{}'). You cannot use ordinary braces - you must use field braces, as per the tutorial. If you need to create them, that is done via Ctrl-F9.

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
Yes because when I just replaced MERGEFIELD City with MERGEFIELD VendorName it did not work. It produced MERGEFIELD CINTAS- Desert. So I then went with what I knew and replaced it with VendorName :-(

Yes I found out about Ctrl+F9
Perhaps it's the brackets.

Going by the Tutorial, page 7 states:

Force Each New Category To a New Page
A minor change to the field coding from the previous examples inserts a page break whenever the key field changes. The following field codings show how you might go about this:
• with the ‘City’ field as the key field –
«State» «City»

«Representative» «Sales»

• or, with the ‘State’ field as the key field –
«State»

«City» «Representative» «Sales»

Note: The 'QUOTE 12' field creates the page break. If that seems confusing, you could replace that field with a manual page break, by pressing Ctrl-Enter, or you could replace it with a ‘Next Page’ Section break.
Click to expand...
I do not see anywhere to do with MERGEFIELD.
I have the document set-up with the fields to merge with my perspective fieldnames.
I do not understand what I am doing wrong.

Here's what I get when using the Ctrl+F9

Code: 
 = "1" "ACC BUSINESS
“ “”} 

 
ACC BUSINESS

InvoiceNo: 122243805 DueDate: 8/27/2012  NetInvoice: $513.85000000000002 “ “ InvoiceNo: 122243805 DueDate: 8/27/2012  NetInvoice: $513.85000000000002“ }

I attached what I have from what I found.
It's the closet one but the listing invoices per Vendor is not working.
Which would be the "Merge Records by Category" according to your Tutorial.

This is in Office 2003
 
 https://www.dropbox.com/s/1jr75svj02f2mou/Email-Template.zip
Upvote 0 Downvote
I honestly copied this and used the Ctrl+F9 where possible and the above is what I got :-(

Code: 
Force each new category to a new page
As in the previous example, the key field in this example is { MERGEFIELD City }. When the value of CITY changes in the data file to a different city, a new page is added to the merged results, and the merge is continued at the top of the next page. To insert the field braces, press CTRL+F9.

    { If { MERGESEQ } = "1" "{ MERGEFIELD CITY }¶
    " ""}{ SET Place1 { MERGEFIELD CITY }}¶
    { If { Place2 } <> { Place1 }"¶
    ----------------------------Page Break--------------------------------
    { MERGEFIELD CITY }¶
    ¶
    { MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶ 

Note To insert a page break, either press CTRL+ENTER, or click Break on the Insert menu, select Page Break, and then click OK.

The fields laid out in this example produce a listing on separate pages as follows:
Atlanta

Galos $3,000
Delaney $50,000
Henningsen $10,000

----------------------------Page Break-----------------------------
Houston

Johnson $8,000
Kelly $9,000
Pak $0

Code: 
    { If { MERGESEQ } = "1" "{ MERGEFIELD VendorName}¶
    " ""}{ SET Place1 { MERGEFIELD VendorName}}¶
    { If { Place2 } <> { Place1 }"¶
    ----------------------------Page Break--------------------------------
    { MERGEFIELD VendorName}¶
    ¶
    { MERGEFIELD PayInvoiceNo} { MERGEFIELD InvoiceDueDate} { MERGEFIELD NetInvoice" "{ MERGEFIELD PayInvoiceNo} {MERGEFIELD InvoiceDueDate { MERGEFIELD NetInvoice}" }{ SET Place2 { MERGEFIELD VendorName}}¶

I get:
Code: 
= "1" " ACC Business
" ""}

---Page Break----
ACC Business

123456    9/20/2012       510.20000003 "" 123456   9/20/2012    510.200000003"

Instead of the said example.
 
Upvote 0 Downvote
If you could take a look at the files I've attached.
It's almost there.

Just the Records by Category is where I need help with now.
 
Upvote 0 Downvote
Perhaps you should start with the basics (ie the 'Merge Records By Category' example, seeing how that works with the included mailmerge main document) and getting that to work with your data before you move on to the more advanced fields.

FWIW, I have no idea what's in your documents as none of the links you've posted so far work - they all hang at the download stage.

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
Yes I have it almost done.
The basic mail merge is easy and if it wasn't the issue with listing all the invoices per Vendor in each page I would not be here :-(

The standard way it creates a new document for each invoice even though the same Vendor.

I appreciate your patience.

The link is to a DropbBox file:
I pretty much have it now with summing and getting the total for the invoices (AKA: "Group" total) with a table.
Issue is the same, where I started this ... getting all invoices for the same Vendor in the table as a list of them for each Vendor per page (AKA: Document)

If you're able to download from the dropbox link (perhaps because I was updating the file when you were trying to download it?)

When you click on the link or copy and paste it to your browser I get this:
Dropbox

Share Download

Email-Template.zip
5 mins ago · 14.97 KB
 
Upvote 0 Downvote
The dropbox link still doesn't work. When I click on 'Direct Download', it opens a dialogue with an invalid filename (Email-Template_zip?dl=1) and hangs.
Issue is the same, where I started this ... getting all invoices for the same Vendor in the table as a list of them for each Vendor per page
Click to expand...
That suggests you haven't even done the basic setup correctly - ie you're using a Letter or Email merge instead of a Directory/Catalog merge.

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
The latest link works - and confirms what I said in my previois post:
That suggests you haven't even done the basic setup correctly - ie you're using a Letter or Email merge instead of a Directory/Catalog merge.
Click to expand...

Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0 Downvote
Ok, I do not get the difference in order to implement it.

I've even just watched YouTube video and not getting it
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

OCM
  • Question
Mail merge with details
Replies
2
Views
461
OCM
OCM
arvarr
  • Locked
  • Question
Excel Help - Worksheets by UserID
Replies
2
Views
86
arvarr
arvarr
cae154
  • Locked
  • Question
Removing reference to domain name
Replies
4
Views
106
cae154
cae154
Scott24x7
  • Locked
  • Question
Dynamic Unique List 2
Replies
10
Views
111
Scott24x7
Scott24x7
TheLazyPig
  • Locked
  • Question
Separate name per column in Excel
Replies
5
Views
116
TheLazyPig
TheLazyPig

Part and Inventory Search

Sponsor

Back
Top