Mail Merge Repeat Record a certain number of times

macropod · #2 04-25-2014, 05:59 PM

You can do it via fields coded as:
{IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "{MERGEFIELD FIRST_NAME} {MERGEFIELD LAST_NAME}"}{SET QTY {MERGEFIELD Quantity}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0}
or:
{IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "«FIRST_NAME» «LAST_NAME»"}{SET QTY {MERGEFIELD Quantity}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0}

The bold portion is where you insert your mergefields, together with whatever paragraphs and other formatting you require. The above example assumes data fields named 'FIRST_NAME' and 'LAST_NAME'.

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 mailmerge toolbar. The spaces represented in the field construction are all required. Furthermore, the field codes as depicted above need to be copied to each cell. If you use Word's 'update labels' function, that will insert an unwanted «NextRecord» field at the start of all labels after the first.

In addition, having constructed the first label page, you need to copy the entire label table and paste onto its end however many copies you need to ensure all of the required labels are catered for. If you don't do this, one set of labels will be skipped at each page break. Once you've done the first page setup, the following macro automates the addition of however many pages of labels might be needed, executes the merge, then restores the document to its original state:

Code:

Sub RunMultiLabelMerge()
Dim r As Single, c As Single, h As Single, v As Single
Dim i As Long, j As Long, x As Long, y As Long, z As Long
' Turn Off Screen Updating
Application.ScreenUpdating = False
With ActiveDocument
  'Temporarily delete the fields (for processing speed)
  .Fields.Unlink
  With .MailMerge.DataSource
    'Get the label count
    For i = 1 To .RecordCount
      .ActiveRecord = i
      j = j + .DataFields("Quantity").Value
    Next
    .ActiveRecord = 1
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
  End With
  'Restore the deleted the fields
  .Undo
  'Calculate the # labels per page
  With .Tables(1)
    With .Cell(1, 1)
      r = .Height: c = .Width
    End With
    If .Cell(1, 2).Width = .Cell(1, 1).Width Then
      h = c
      x = .Columns.Count
    Else
      h = c + .Cell(1, 2).Width
      x = -Int(-.Columns.Count / 2)
    End If
    If .Cell(2, 1).Height = .Cell(1, 1).Height Then
      v = r
      y = .Rows.Count
    Else
      v = v + .Cell(2, 1).Height
      y = -Int(-.Rows.Count / 2)
    End If
    'Calculate the # label pages required
    z = Int(j / (x * y))
    y = .Rows.Count
    'Add the required # label pages
    If z > 0 Then .Range.Copy
    For i = 1 To z
      ActiveDocument.Paragraphs.Last.Range.Paste
    Next
  End With
  'Execute the mailmerge
  .MailMerge.Execute
  'Restore the document to its original state
  If z > 0 Then .Range.Paste: .UndoClear: .Saved = True
End With
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub

For a macro to convert the first of the above field text strings to working field codes, see: http://www.gmayor.com/export_field.htm#TextToField

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Repeat formula 5 times and repeat?	Jenny345	Excel	4	06-14-2013 04:37 PM
mail merge skips 16th record only - of 23 total.	passenger	Mail Merge	1	07-24-2012 04:03 PM
Mail merge to print each record as it is created	PNGento	Mail Merge	1	01-26-2012 02:00 PM
avoid duplicete record and merge the record with the existed record	hemant.behere	Excel	0	01-10-2012 02:53 AM
Extraneous record in Word Mail Merge	aldo13	Mail Merge	0	01-23-2006 01:58 PM