Mail Merge - Multiple Rows Into One Document

Question

I am trying to do a Mail Merge where I have 200 Franchise Owners, with approximately 1200 staff members who have taken part in an incentive program. I need to send a letter to the Franchise Owners detailing each of their staff members and how much they have won.

I have used the following tutorials and copied their code with modifications where needand for the various fields.

http://support.microsoft.com/kb/211303 https://www.youtube.com/watch?v=4Ck8KAwAQso

However, no matter what I try, when I go to complete the merge a new document is created for each individual staff member, rather than having a staff list for each of the 200 franchises.

This is the full code I have for the letter

{ MERGEFIELD DPFull }
{ MERGEFIELD Dealer_Name }
{ MERGEFIELD Address }
{ MERGEFIELD Suburb } {MERGEFIELD State } {MERGEFIELD Post_Code }

Congratulations on your Sales Managers and Sales Consultants being winners in the latest incentive program.

Staff Member - Card Value
{ IF { MERGESEQ } = "1" }
{ SET VAR1 { MERGEFIELD DPFull } }
{IF { VAR1} <> { VAR2 }
"{ MERGEFIELD Customer } - ${ MERGEFIELD Card_Value }"
"{ MERGEFIELD Customer } - ${ MERGEFIELD Card_Value }"}
{SET VAR2 { MERGEFIELD DPFull } }

Footer Text of Letter

Any help would be great, as have been tearing my hair out trying to get this to work. Have tried copying the code verbatim from the two tutorials above and only changed the fields around to match mine but still no such luck

Answer 1

The field names in your current code suggests that you may have a "3-level merge", i.e. where you want output like the following:

Dealer A
  Staff member A1
    Customer A11
    Customer A12
  Staff member A2
    Customer A21
    Customer A22

<new page>
Dealer B
  Staff member B1
    Customer B11
    Customer B12
  Staff member B2
    Customer B21
    Customer B22

and so on.

But the structure of your current code suggests a 2-level merge, more like

Dealer A
  Staff member A1
  Staff member A2

<new page>
Dealer B
  Staff member B1
  Staff member B2

If it's 2-level the suggestions below may nearly be enough. If it's 3-level, more will be needed.

You have to think a bit "back to front" to get this right. The second tutorial referenced in the video has a tutorial which helps, but you have to follow it closely.

It's critical your mail merge main document is actually set up as a Directory merge (Catalog merge on Mac Word), otherwise you will always get a page break between every Customer (or Staff) record.

As usual, it's also important that all the { } are actually the special field code braces that you can insert using ctrl-F9 on WIndows WOrd, not the ordinary ones you type on the keyboard

I think you need to start with field coding more like this (compare it with the tutorial if you like, as the tutorial may do it in a more simple way):

{ IF  { MERGESEQ }  = "1" { SET VAR1 "" } { SET VAR1 { MERGEFIELD DPFull  } } }{ IF  { VAR1 } <> { VAR2 }  "{ IF  { MERGESEQ } <> "1" "
Footer Text of Letter
--PB--

"  }
{ MERGEFIELD Dealer_Name   }
{ MERGEFIELD Address  }
{ MERGEFIELD Suburb }
{ MERGEFIELD State  }
{ MERGEFIELD Post_Code  }

Congratulations on your Sales Managers and Sales Consultants being winners in the latest incentive program.

Staff Member - Card Value
" "" }{ MERGEFIELD Customer  } - $ { MERGEFIELD Card_Value }{ SET VAR2  { MERGEFIELD DPFull  } }

Where I have put --PB--, you need to insert a page break instead.