Automating Mail Merge

Question

I need to dynamically generate word documents using text from an Access database table. The caveat here is that some of the text coming from the database will need to be modified into MergeFields. I am currently using Interop.Word (VBA Macro) and VB.NET to generate the document.

My steps so far look like this:

1. Pull standard .docx Template
2. Fill In template with pre-defined filler text from table
3. Add MergeFields by replacing pieces of the filler text with actual mergefields
4. Attach Datasource and execute Mail Merge

After testing, I noticed that I cannot simply store MergeFields into the access database as a string, they do not feed over into the actual document. What I am thinking then is creating a table of known strings that should be replaced with MergeFields by the coding.

For Example:

Step 2 will insert "After @INACTIVE_DATE@ your account will no longer be active." which will be in the database text.

Step 3 will find/replace @INACTIVE_DATE@ with a MergeField «INACTIVE_DATE». I am using Word Interop to do this, so theoretically I can loop through the document.

I wasnt able to do a "Find And Replace" from text to MergeField, so how should I go about implementing this?

Tagging VBA additionally as I am seeking a "VBA" style answer (Word Interop).

Answer 1

You've left out quite a lot of detail, so I'll go about answering this in somewhat general terms.

What you want to do is definitely achievable. Two possible solutions immediately come to mind:

1. Replacing ranges using Find
2. Inserting tokens using TypeText

Replacing ranges using Find

Assuming the text has already been inserted, you can search the document for the given pattern and replace it with a merge field. For instance:

Sub FindInsertMerge()
    Dim rng As Range
    Set rng = ActiveDocument.Range

    With rng.Find
        .Text = "(\@*\@)"
        .MatchWildcards = True
        .Execute

        If .Found Then
            ActiveDocument.MailMerge.Fields.Add rng, Mid(rng.Text, 2, Len(rng.Text) - 2)
        End If
    End With
End Sub

Will find the first occurence of text starting with @, matches any string and ends with @. The contents of the found range will then be replaced by a merge field. The code above can easily be extended to loop for all fields.

Inserting tokens using TypeText

While I would normally advice against using Selection to insert data, this solution makes things simple. Say you have a target range, rng, you tokenize the database text to be inserted, select the range, start typing and whenever a designated mail merge field is found, insert a field instead of the text.

For instance:

Private Sub InsertMergeText(rng As Range, txt As String)
    Dim i As Integer
    Dim t As String
    Dim tokens() As String

    tokens = Split(txt, " ")
    rng.Select

    For i = 0 To UBound(tokens)
        t = tokens(i)

        If Left(t, 1) = "@" And Right(t, 1) = "@" Then
            'Insert field if it's a mail merge label.
            ActiveDocument.MailMerge.Fields.Add Selection.Range, Mid(t, 2, Len(t) - 2)
        Else
            'Simply insert text.
            Selection.TypeText t
        End If

        'Insert the whitespace we replaced earlier.
        If i < UBound(tokens) Then Selection.TypeText " "
    Next
End Sub

Call example:

InsertMergeText Selection.Range, "After @INACTIVE_DATE@ your account will no longer be active which will be in the database text."