Search code examples
excelms-wordmailmerge

Merge only part of a Word document from Excel source

As the header suggests, I am trying use the MS Office merge functions to apply to a part of a Word document and not the entire document.

Context: I have a database in Excel with names and email addresses of recipients and use this as a source in MS Word. However, I have several languages in one single Word document. The recipient should only receive her own language and not all others. I had set up the Word document that way to avoid having several word documents named Email_booking_FR.docx, Email_booking_EN.docx, Email_booking_NL.docx, etc. I have to say that the merge functions properly at the moment, with the limitation that the email sent contains all languages.

My Word document looks like this (Minimum Working Example):

EN

Dear Recipient,

Thank you for your email. We will get back to you as soon as possible.

Best,


FR

Cher Recipient,

Merci pour votre e-mail. Nous le traiterons dans les plus brefs délais.

Cordialement,

where Recipient is the field in the Excel worksheet.

Objective: I wuold like the merge function in MS Word to apply to the whole document (in other words, to complete all Recipient fields, but to send only part of the Word document as an email). Is this possible? Is Word capable of doing such things if the EN and FR parts are sections (Title1 tags)

From the following possibilities, which one(s) are (1) possible and (2) adequate?:

  • If...then...else statement within Word based on the language of the recipient in the Excel sheet
  • Separate the languages by a page break (the problem of the Header EN or FR remains, however)
  • Recover the line number of the specific sections and use it in a Macro (but I have no idea how)

Do you have more thoughts on the matter?

Solution

  • Provided you have a field in the mailmerge data source that identifies the language to be used, you could use a series of fields, coded along the lines of:

    {IF{MERGEFIELD Language}= "EN" "Dear «Recipient»,

Thank you for your email. We will get back to you as soon as possible.

Best,"}{IF{MERGEFIELD Language}= "NL" "Beste «Recipient»,

Bedankt voor je email. We nemen zo snel mogelijk contact met u op.

Het beste,"}{IF{MERGEFIELD Language}= "FR" "Cher «Recipient»,

Merci pour votre e-mail. Nous le traiterons dans les plus brefs délais.

Cordialement,"}

    Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required.

    Note also that the output text cannot contain any double quotes; otherwise the output will be truncated at that point. Moreover, you are liable to have the remainder appearing in the other outputs! You can work around that by using paired single quotes.

    You may also find my Mailmerge Tips and Tricks thread at http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html helpful.