Updating mail merge labels with VBA

Sub LabelMerge()

'Add MS Word Library

Dim oWord As Word.Application, oDoc As Word.Document, sPath As String

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

.Add oWord.Selection.Range, "NAME"

oWord.Selection.TypeParagraph

.Add oWord.Selection.Range, "STREET"

oWord.Selection.TypeParagraph

.Add oWord.Selection.Range, "CITY"

oWord.Selection.TypeText " "

End With

oDoc.MailMerge.OpenDataSource sPath

ActiveDocument.ActiveWindow.View.ShowFieldCodes = False

oDoc.MailMerge.ViewMailMergeFieldCodes = False

oDoc.MailMerge.Destination = wdSendToNewDocument

oDoc.MailMerge.Execute Pause:=False

oWord.Quit False

End Sub

This is the simple code I have in Excel. The problem is that the new document at the end only shows one record in a multi-label list.

I can fix this in the old document by hitting the "Update Labels" button and/or "Preview Results" button. I have tried all kinds of solutions, including Sendkeys Alt M and then B or P but none of these solutions works.

Do you have the final solution? Thanks

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Graham Mayor

There used to be an old WordBasic command that would propagate the labels, but I thought that it no longer worked in Word 2013; however I have checked this morning and it does in fact still work, so you could use

oWord.WordBasic.MailMergePropagateLabel

to do the job of duplication in place of

Set oRng = oTable.Cell(1, 1).Range
    oRng.End = oRng.End - 1
    For i = 2 To oTable.Range.Cells.Count
        If oTable.Range.Cells(i).Width = oTable.Cell(1, 1).Width And _
           oTable.Range.Cells(i).Height = oTable.Cell(1, 1).Height Then
            Set oCell = oTable.Range.Cells(i).Range
            oCell.End = oCell.End - 1
            oCell.FormattedText = oRng
            oCell.Collapse 1
            oCell.Fields.Add oCell, 41, , False
        End If
    Next i

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Word Top Contributors:

Updating mail merge labels with VBA

Replies (4)

Question Info

Updating mail merge labels with VBA

Replies (4) 

Question Info

Replies (4)