Hello,



I'm trying to figure out if this is possible:

I have an excel spreadsheet that I'm trying to make work with mail merge.

I have 20 possible letter bodies, each with variable mail merge fields in them.

Is it possible to create mail merge letters with variable bodies in one foul swoop, or would I have to do a separate process for each of the 20 letter types?

I'm really hoping to be able to have my computer create these letters automatically, as I have to process these twice a week.

Thanks in advance!

Hi, myspoon,

The only way I can think to do it is to put those 20 letters in one document.

Usually when I process these letters, I have 30-40 to do. Not all letter bodies are used every week, and frequently, one letter body is used several times. I have never had it happen that all letters are the same, or where none are repeated.

Would putting all letter bodies in one document work for this scenario?
(I don't think so, but my boss thinks it should be stupid simple)

Can you give me an idea of how different the letters are? Maybe you could use if/then merge fields and limit the number/variety of letters

This is my current list of letters. As you can guess, some letters are very similar, with just the payment type varying. Other letters are hugely different from each other.

CODE DESCRIPTION
D1 DAMAGED CONSUMER USE
D2 DAMAGED FIXED
D3 DAMAGED LAB REPORT
D4 DAMAGED UNFIXED (CASH & CREDIT)
D5 DAMAGED UNFIXED (CASH)
D6 DAMAGED UNFIXED (CREDIT)
D7 DAMAGED MANUFACTURERS DEFECT
D8 DAMAGED TABS LETTER
D9 DAMAGED NO RECORD OVER 90 DAYS
D10 DAMAGED GOOD CUSTOMER (CREDIT)
D11 DAMAGED FIXED AS MUCH AS POSSIBLE
D12 DAMAGED FIXED AS MUCH AS POSSIBLE (CREDIT)
D13 DAMAGED FIXED (CREDIT)
M9 MISSING PAY (CASH & CREDIT)
M10 MISSING PAY (CASH)
M11 MISSING PAY (CREDIT)
M12 MISSING ALL ACCOUNTED
M13 MISSING OVER 60 DAYS
M14 MISSING NO RECORD
M15 MISSING GOOD CUSTOMER (CREDIT)
F19 MISSING WE FOUND IT (CREDIT)
C14 MISSING OR DAMAGED CLAIM FORM
R17 REIMBURSMENT/INCONVENIENCE (CASH & CREDIT)
R18 REIMBURSMENT/INCONVENIENCE (CREDIT)

Thanks for the clarification. With that little peek into your world, I wonder if you could combine some of these or even all the D letters, but let's start small.

I imagine that R17 says something like
"we are sending you a check for (check_amt) and a credit of (credit_amt) and R18 says something like
"we are offering you a credit for (credit_amt)".

One way to make these one letter is to have something like (my syntax isn't perfect but it can get you started):
"we are sending you a check for (check_amt)
<if not blank> and a credit of (credit_amt)<end if>.

If you're not familiar with if statements, I recommend starting small by combining two letters. That would get you down to 15 to 20 letters and cut your work in half.

You can do the conditional output all in a single mailmerge output letter, via IF fields. For an example of what's possible, see the attachments to my post at: http://www.eileenslounge.com/viewtop...p=95092#p95092
The data source is in the zip file at: http://www.eileenslounge.com/viewtop...p=95092#p94974

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

So I finally figured this out, thought I would share my solution.

I had to break all the letters up into little chunks to make this work.

It may not be the best way to go about this, but it works




[return address]


«AddressBlock»

«GreetingLine»
«Paragraph1»

«Paragraph2»«Paid»«Paragraph2a»«Writeoff»

«Paragraph3»

Sincerely,
«Responsible»
«Responsible»

«LetterCode»: «InquiryNumber» - «Store»
CK: «Paid»
DD: «Writeoff»

As was said previously, all the variable content could be handled via field coding within the document. For example, if you have a column named 'Code' with the codes as per post #5, you could use a single field coded as:
{IF{MERGEFIELD Code}= "D1" "Letter body for Damaged Consumer Use"}
or:
{IF«Code»= "D1" "Letter body for Damaged Consumer Use"}
to output all the text for the 'Damaged Consumer Use' letter.

Simply stringing these together, as in:
{IF{MERGEFIELD Code}= "D1" "Letter body for Damaged Consumer Use"}{IF{MERGEFIELD Code}= "D2" "Letter body for Damaged Fixed"}{IF{MERGEFIELD Code}= "D3" "Letter body for Damaged Lab Report"}, etc.
or:
{IF«Code»= "D1" "Letter body for Damaged Consumer Use"}{IF«Code»= "D2" "Letter body for Damaged Fixed"}{IF«Code»= "D3" "Letter body for Damaged Lab Report"}, etc.
is all that would be required to generate all possible letters.

The conditional Letter Body output can include multiple paragraphs, more mergefields, tables, pictures, and so on. Similarly, mergefield field switches can be used to suppress text before and/or after a mergefield when that field is empty. Examples of that are shown in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html


Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the 'Insert Merge Field' dropdown. The spaces represented in the field construction are all required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]