asked on

Mail merge and dynamic table

I've got a letter including a table and an Excel sheet with address information and elements that have to appear in the table. the point is: the elements vary in their number. some customers have 2, some 4 and some 7 elements to appear in the table on the letter. is there a way to keep the table dynamic? say, 2 appearing rows if there are 2 elements to show, 5 rows if there are 5 elements. thanx for any hint!

Patrick Matthews

webeng,With a straight mail merge, probably not, although it can possibly be accomplished with a little VBA wizardry. Please post sanitized examples of your data file and merge file.Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it. The file may also be indexed by the major search engines.Therefore, be very careful about posting proprietary, confidential, or other sensitive information. If necessary, use "fake" and/or obfuscated data in your sample.Another alternative: you may actually find this easier to do as an Access report instead of a mail merge. Let me know if you are willing to consider that alternative.Patrick

GrahamSkan

If you only have a limited number of the elements, they can be placed in separate columns in your spreadsheet.

Then put a field in the mail merge main document for each of the optional columns

<<CUSTOMER NAME>> <<CUSTOMER NUMBER>>
<<ADDRESS>>

<<COLUMN4>>
<<COLUMN5>>
<<COLUMN6>>
<<COLUMN7>>
<<COLUMN8>>
<<COLUMN9>>
<<COLUMN10>>
<<COLUMN11>>

Blank lines will automatically be deleted in the result

webeng

ASKER

Here's a very quick and dirty merge. The Excel Sheet would be in exact the format i will get it from client. One of course could think of say making two Sheets out of it or normalizing it in another useful way.

Speaking of the Access Report: The result of whole work has to come out in form of a Word Doc. About 1500 Word documents actually. However we get there is another story.
ExcelSample.jpg
Merge1.jpg

Patrick Matthews

webeng,The images you uploaded appear blank. In any event, uploading actual Excel and Word files would be far more helpful.Patrick

webeng

ASKER

oh, you're right. i this time attached a word and an example sample. they the column names both in word and excel are in german. i hope that doesn't confuse too much. however, i will upload an english version on monday. SampleData.xlsx SampleLetter.doc

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

webeng

ASKER

that looks interesting. i will follow it and come back with a feedback next week. thanx!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.