Solved: Question - Excel to Word mail merge

[yogin]

hi, Sorry i have stuck and i think i am not able to thing in right direction. Is there a way using mail merge to print 2 different type of document from one excel file.

I have attached the 2 different templates, which i need it to print, depending on the condition if its a standard invoice or Capex invoice.

is this possible?

[macropod]

Hi yogin,

Neither of the documents in your attachment is set up as a mailmerge document - they're both Word forms using formfields. Such documents are not compatible with mailmerge.

To answer your basic question, though, yes it is quite possible to generate different documents from the same mailmerge that depend on the kind of information to be included. That's usually done by embedding IF fields into the mailmerge main document that test a value in the mailmerge data source and varying the relevant portions of the output accordingly. Those variations can be embedded into the IF field itself or held in an external document that's referenced via an INCLUDETEXT field.

For your invoices, I imagine that the number of items to be included for each recipient varies, so you'd probably need to use a Catalogue/Directory merge. To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

[yogin]

thanks paul, but sorry i dont think this is what i am looking for. I have reattached the files along with the excel file where I am getting the data from. Basically i would like to get the details in ".IA Standard.doc" if its say "STANDARD INVOICE AUTHORISATION" as template name and "2.Capex IA.doc" if it says "CAPEX INVOICE AUTHORISATION".

[macropod]

Hi yogin,

It seems to me that the only substantive differences between the two forms are the 'STANDARD' vs 'CAPEX' words in the titles and the presence of some extra lines in the 'Tax Codes' section of the Standard invoice.

Handling the first of these is as simple as replacing the 'INVOICE AUTHROSATION' heading with a mergefield pointing to your workbook's 'Template_name' field.

As indicated in my previous post, the 'Tax Codes' differences are easily handled by embedding an IF field into the mailmerge main document to test the value of the 'Template name' field in your workbook and varying the output according to that field's contents. In this case, the IF field could be coded along the lines of:
{IF«Template_name» = "S*" "Tax Codes Table for Standard" "Tax Codes Table for Capex"}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likewise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar.

Both documents still have numerous text formfields and, as I also said in my previous post, these are incompatible with a mailmerge. When the mailmerge executes, the formfields will be deleted!

Finally, unless all your invoices will have exactly the same number of data rows in your workbook, you will indeed need to use a Catalogue/Directory merge. The reason is that there is no other way a Word mailmerge can tell whether all records for the current client/invoice combo have been output. The only alternative would be to forget about using mailmerge altogether and drive the whole process via vba - in which case, you can keep the formfields if you really want them and manipulate the 'STANDARD'/'CAPEX' titles and the extra lines in the 'Tax Codes' section of the Standard invoice via your vba code.

[yogin]

Thanks Paul, I think I have got it now. Thanks heaps