Excel Macro with MSword Mail Merge using 2d Array

gmayor · #2 07-29-2021, 08:40 PM

As you only want the last record in the array, I would be inclined to work from an Excel macro. I would also replace the merge fields in the document with Content Controls, then you should be able to populate the controls directly from the array e.g. something like

Code:

Option Explicit

Sub GetLastInArray(Arr() As Variant)

Dim iRow As Long
Dim wdApp As Object
Dim wdDoc As Object
Dim oCC As Object

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    wdApp.Visible = True

    Set wdDoc = wdApp.Documents.Add(Template:="C:\Path\Mergedoc.docx")    'the merge document

    iRow = UBound(Arr, 2)    'The last row of the array

    For Each oCC In wdDoc.contentcontrols
        Select Case oCC.Title
            Case "Title1"
                oCC.Range.Text = Arr(1, iRow)    'column 1
            Case "Title2"
                oCC.Range.Text = Arr(2, iRow)    'Column 2
                'etc
        End Select
    Next oCC
lbl_Exit:
    Set oCC = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Exit Sub
End Sub

Instead of using an array, you could populate the controls directly from the appropriate worksheet cells.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file	sureshbvs	Word VBA	1	03-12-2021 05:42 AM
Need macro to fill the values in msword based on the excel sheet info	ganesang	Word VBA	50	08-29-2018 12:53 AM
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA	RMerckling	Mail Merge	16	05-17-2018 05:19 PM
Mail Merge Macro	ch1325	Word VBA	2	06-08-2015 06:18 AM
msword email merge doc as pdf	pablo Isl	Mail Merge	3	06-09-2010 07:38 AM