Excel Workbook to Create Word Document and Auto-Run Mail Merge from Excel Workbook

Question

I've got a bit of a tricky one here. Attempting to simplify an existing process.

Existing Process:

Word Document ("Plan Doc Template") is entirely composed of INCLUDETEXT fields that pull Bookmarked sections from another Word Document ("Source Plan Doc" we'll call it) that includes merge-fields in its bookmarked sections which are from an Excel Workbook ("Mail Merge Workbook").

The current process involves the user copying a Plan Doc Template and a Mail Merge Workbook and pasting it into any folder they choose. The user then fills out the Mail Merge Workbook, saves and closes, and runs a Mail Merge through the Plan Doc Template Word Doc. This pulls in bookmarked sections from the Source Plan Doc depending on the Mail Merge Workbook fields selected. The user then removes all INCLUDETEXT fields with CTRL + SHIFT + F9 to turn fields of Plan Doc Template into workable text.

(Hopeful) Future Process:

The user copies a Mail Merge Workbook and pastes it into their desired folder. Fills out the Mail Merge Workbook. (Manual Step)
Runs VBA Code.
VBA copies the Plan Doc Template and pastes in the Mail Merge Workbook's folder that just ran the VBA code.
VBA renames the Plan Doc Template Word Doc based on fields within the Mail Merge Workbook.
VBA runs a Mail Merge within the Plan Doc Template
VBA highlights entire document and CTRL + SHIFT + F9 to turn Field Codes into workable text.

Is it possible to do all this from an Excel VBA code or would I need a separate code after the Plan Doc has been created to run the mail merge and do the CTRL + SHIFT + F9 steps?

P.S. I use Excel Worksheets via DDE Selection to get the correct formatting from Mail Merge Workbook to Document. Hoping that can be included in the VBA code, as well.

Help would be greatly appreciated on this one, thanks,

Rich

Note that, technically, your question is "too broad" for the site (see the help center). You should have included at least the basic code. But the question intrigued me... — Cindy Meister

Cindy Meister Cindy Meister · Accepted Answer · 2018-04-12T14:36:05

It looks like you can have the whole thing run with one macro from Excel, without the user having to run a second one, using a For loop until wdApp.Documents.Count increases by 1. I did test the following, but with only a very small data set, so it ran very quickly.

Since the user might have more than just the main merge document open, it's important the code can identify and work with the resulting document. Usually, it will have become the ActiveDocument but relying on that is never certain. So I built in a couple of loops to 1) hold the currently open documents in an array then 2) compare those to the currently active document. If the currently active document is not in the array, then the fields are unlinked (that's the equivalent of Ctrl+Shift+F9).

Of course, if you really wanted to identify the new document from all the documents you'd need to loop each document and loop the array, making the comparison. But I've given you the starting point...

Sub MergeWithWord()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim nrDocs As Long
    Dim i As Long, d As Long
    Dim aDocs() As Variant

    Set wdApp = GetObject(, "Word.Application")
    nrDocs = wdApp.documents.Count

    'Get all opened documents so can compare whether a new one
    ReDim Preserve aDocs(nrDocs - 1)
    Set wdDoc = wdApp.activedocument
    For i = 0 To nrDocs - 1
        Set aDocs(i) = wdApp.documents(i + 1)
    Next

    If wdDoc.MailMerge.MainDocumentType <> -1 Then
        wdDoc.MailMerge.Destination = 0
        wdDoc.MailMerge.Execute False
        Do Until wdApp.documents.Count > nrDocs Or i > 1000
            i = i + 1
        Loop
        Set wdDoc = wdApp.activedocument
        For d = 0 To UBound(aDocs)
            If wdDoc Is aDocs(d) Then
                Debug.Print "Not a new doc"
            Else
                Debug.Print wdDoc.FullName
                wdDoc.Fields.Unlink
                Exit For
            End If
        Next
    End If

    Debug.Print nrDocs, i
    MsgBox "Done"

End Sub

Excel Workbook to Create Word Document and Auto-Run Mail Merge from Excel Workbook

2 Answers