Mail Merge Macro: How to send data from each row in my Excel sheet to different pages on Word?

Question

I have been working on a macro that adds data to a page on Word in the form of a Mail Merge label. I found a code online that gets all the data into one page on Word. I was wondering if there is a way that the data from each row in my Excel Spreadsheet could go to different pages in Word? The code is given below.

Sub LabelMerge()

   Dim oWord As Word.Application, oDoc As Word.Document
   Dim sPath As String, I As Integer, oHeaders As Range

Application.ScreenUpdating = False

   Set oHeaders = Range("A1").CurrentRegion.Rows(1)
   sPath = ThisWorkbook.FullName
   Set oWord = CreateObject("Word.Application")
   Set oDoc = oWord.Documents.Add
   oWord.Visible = True
   oDoc.MailMerge.MainDocumentType = wdMailingLabels
   oWord.Dialogs(wdDialogLabelOptions).Show
   oDoc.Activate

   With oDoc.MailMerge.Fields

     For I = 1 To oHeaders.Columns.Count
       .Add oWord.Selection.Range, oHeaders.Cells(1, I)
       oWord.Selection.TypeText " "
     Next I

   End With

   oDoc.MailMerge.OpenDataSource sPath
   oWord.WordBasic.mailmergepropagatelabel
   oDoc.MailMerge.ViewMailMergeFieldCodes = False
   oDoc.ActiveWindow.View.ShowFieldCodes = False

   Set oDoc = Nothing
   Set oWord = Nothing

   Application.ScreenUpdating = True

   End Sub

This is how the data looks like before and after the code is run from the VBA Editor. I am fairly new to VBA and would appreciate any help!

Sample Data in Excel

Labels on Word

If you want each record to go to a new page, you wouldn't be using a label merge, which is what your code employs, via oDoc.MailMerge.MainDocumentType = wdMailingLabels. But then, you also wouldn't be using code like oWord.Dialogs(wdDialogLabelOptions).Show or oWord.WordBasic.mailmergepropagatelabel. You need to give a better explanation of what you're trying to do. — macropod
Hi macropod, What my code currently does is transport data from the sheet to one page in the document in the form of a mail merge. Since it's mostly just name labels and addresses, I was hoping if there is a way to put the labels from each row on a different page in the same document. — SSJErito
I am definitely open to new methods of doing this, this was just something I found online and edited a bit. Thanks in advance — SSJErito
Do you want a full sheet of labels for each address? Then try this (no VBA involved): Alt+F9 in the main merge document to display the field codes. Remove the { NEXT } field from the second and all following labels. That should repeat the same address on the one sheet, and the next address on a new sheet, etc. — Cindy Meister

macropod macropod · Accepted Answer · 2018-10-24T21:28:12

What you need to do is create a mailmerge main document with a single label containing all the mergefields required for that label, rather than a sheet of labels, and combine that with an ordinary letter merge. Having created the mailmerge main document with a single label, you can then use code like the following:

Sub RunMerge()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim StrMMSrc As String, StrMMDoc As String
StrMMSrc = ThisWorkbook.FullName
StrMMDoc = ThisWorkbook.Path & "\MailMergeMainDocument.doc"
With wdApp
  .Visible = True
  .DisplayAlerts = wdAlertsNone
  Set wdDoc = .Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
  With wdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
        LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM `Sheet1$`"
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      .Execute Pause:=False
    End With
    .Close SaveChanges:=False
  End With
  .DisplayAlerts = wdAlertsAll
  .Activate
End With
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub

Naturally, you may need to edit the 'StrMMDoc' variable to suit your setup & document name and, perhaps, edit the SQL Statement:

SQLStatement:="SELECT * FROM Sheet1$"

to reference the correct worksheet.

Mail Merge Macro: How to send data from each row in my Excel sheet to different pages on Word?

2 Answers