Mail Merge using Multiple Rows

[BruceH]

Excel 2010 & Word 2010


I have a spreadsheet with 600 records (Rows). I am trying to populate a table in a word document from multiple records in spreadsheet as part of my mail merge.

As an example:

The first 40 rows need to be in the first document, the next 7 rows go into another document etc.

I have a column in the spreadsheet that could be used to identify which records need to go into each merge document, but I am stumped on how to get the merge process to populate my table (Or create a table) and also how to get it to generate the multiple documents.

I understand basic mail merges, but I am way over my level of expertise on this, do you have any ideas?

 

[John_w]

You could try a 'Directory' mail merge. See http://cornell.veplan.net/article.aspx?&a=3815 and http://support.microsoft.com/kb/294686/.

With this method, your data must be sorted so that all the records for a merge document are listed together in the worksheet.

 

[psamu]

You could try a 'Directory' mail merge. See Veplan.net and How to use mail merge to create a list sorted by category in Word.

With this method, your data must be sorted so that all the records for a merge document are listed together in the worksheet.

I have also same data in excel with multiple line information for same person. Is there any way to create mail merge those rows in same person letter? I tried the link, but it is not showing . Thanks

 

[John_w]

The Microsoft link still works - all the steps are in there. If you need more help search for something like 'mail merge directory excel word'.

 

[psamu]

I have similar to below excel data. I am trying to mail merge and Customer I has to be in one mail merge document, Customer B has to be in another doucument and so on. If the item in multiple line then show in the document as it is. Thanks for the help



Customer Site Item Charge1 Charge2 Charge3
------------ ----- ------ ---------- ---------- ----------
Customer 1 Little Place Widget 45.00 75.00 95.00
Customer 1 Medium Road Widget 40.00 70.00 98.00
Customer 1 Medium Road Thing 48.00 76.00 92.00




Customer B Red Street Widget 35.00 72.00 90.00
Customer B Blue Avenue Widget 42.00 78.00 87.00
Customer B Blue Avenue Thing 41.00 68.00 85.00
Customer B Green Drive Widget 38.00 65.00 91.00






Customer $ Square Crescent Widget 39.00 62.00 90.00
Customer $ Circle Way Widget 34.00 60.00 96.00

 

[psamu]

Thanks. I think I can figure it out now. Link is working.

 

[psamu]

Thanks. I think I can figure it out now. Link is working.

I am trying to do below, I think I am doing somethign wrong, it is not working. I do not know how to add these. Please help thank

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

 

[John_w]

Pressing Ctrl+F9 inserts a pair of braces {}. You then type the words IF, MERGESEQ, etc. inside them as necessary, or move the cursor outside the closing brace and press Ctrl+F9 for another pair of {}. Where you see ¶ you press the Return key. Spaces are critical otherwise you get errors when you do the merge. You can see spaces better if you copy and paste the whole thing from the MS web page into Notepad and work from that instead of the MS web page. Also, the final ¶ isn't needed.

If you still can't add the merge definition or get it to work I'll record a macro and post the code here to automatically create the merge definition.

 

[psamu]

Pressing Ctrl+F9 inserts a pair of braces {}. You then type the words IF, MERGESEQ, etc. inside them as necessary, or move the cursor outside the closing brace and press Ctrl+F9 for another pair of {}. Where you see ¶ you press the Return key. Spaces are critical otherwise you get errors when you do the merge. You can see spaces better if you copy and paste the whole thing from the MS web page into Notepad and work from that instead of the MS web page. Also, the final ¶ isn't needed.

If you still can't add the merge definition or get it to work I'll record a macro and post the code here to automatically create the merge definition.

Thanks.

I tried several ways working but not grouping the list in one page. It goes into different pages. I would appreciate if you can create macro.
I have below fields. if the VendorID is same then, all items goes into one list. Thanks

DocNo PayDate ID VendorID InvoiceNo Payment EmailAddress

 

[John_w]

DocNo PayDate ID VendorID InvoiceNo Payment EmailAddress

Your data seems different to what you posted in #5. Before I spend any more time on this, which data/layout is correct?

Also, with the macro recorder I was referring to the MS web page merge definition because I can see what the layout is meant to be. It will be difficult to create a merge definition manually or automatically without knowing exactly what you want your merged data layout to look like, so you need to tell me. Do you want something like the following?

VendorID1
DocNo1 PayDate ID InvoiceNo Payment EmailAddress
DocNo2 PayDate ID InvoiceNo Payment EmailAddress

-------- new page ------------

VendorID2
DocNo3 PayDate ID InvoiceNo Payment EmailAddress
DocNo4 PayDate ID InvoiceNo Payment EmailAddress
DocNo5 PayDate ID InvoiceNo Payment EmailAddress


Also, are DocNo PayDate ID VendorID InvoiceNo Payment EmailAddress the exact column headings (A-G) in row 1 of your Excel sheet?