Automating Word Mailmerge not working as expected

Question

I'm having a problem with some mail merge code that is supposed to produce letters within our application. I'm aware that this code is a bit rough at the moment, but we're in the "Get something working" phase before we tidy it up.

Now the way this is supposed to work, and the way it works when we do it manually, is we have a file (the fileOut variable + ".template") which is a template for the letter. We open that template, merge it, and then save it as the filename in the fileOut variable.

However, what it is doing is saving a copy of the template file to the fileout filename, instead of the output of the merge.

I've searched, and I seem to be banging my head against a brick wall.

datafile is the datafile that contains the merge data.

Using the same files, it all works if you do it manually.

Public Function processFile(ByVal datafile As String, ByVal fileOut As String) As String
    Dim ans As String = String.Empty

    errorLog = "C:\Temp\Template_error.log"

    If (File.Exists(datafile)) Then

        Try

            ' Create an instance of Word  and make it invisible.'

            wrdApp = CreateObject("Word.Application")
            wrdApp.Visible = False

            ' Add a new document.'

            wrdDoc = wrdApp.Documents.Add(fileOut & ".template")
            wrdDoc.Select()

            Dim wrdSelection As Word.Selection
            Dim wrdMailMerge As Word.MailMerge


            wrdDoc.MailMerge.OpenDataSource(datafile)

            wrdSelection = wrdApp.Selection()
            wrdMailMerge = wrdDoc.MailMerge()
            With wrdMailMerge
                .Execute()
            End With

            wrdDoc.SaveAs(fileOut)

            wrdApp.Quit(False)

            ' Release References.'
            wrdSelection = Nothing
            wrdMailMerge = Nothing
            wrdDoc = Nothing
            wrdApp = Nothing


            ans = "Merged OK"

            Call writeToLogFile(errorLog, "This worked, written to  " & fileOut)

        Catch ex As Exception
            ans = "error : exception thrown " & ex.ToString
            Call writeToLogFile(errorLog, ans)
        End Try

    Else
        ans = "error ; unable to open Date File : " & datafile
        If (logErrors) Then
            Call writeToLogFile(errorLog, "The specified source csv file does not exist. Unable " & _
               "to process it. Filename provided: " & datafile)
        End If
    End If

    Return ans

End Function

Answer 1

I think the problem is this line:

wrdDoc.MailMerge.OpenDataSource(templateName)

So templateName is something like 'C:\My Template.doc', right?

It looks like you are supplying the file path of a the Word document itself instead of a data source (the Excel or CSV file, Access Table, etc.) linked to the ".template" document.

Try:

data = "C:\My Data.xls"
wrdDoc.MailMerge.OpenDataSource(data)

Answer 2

One way to cheat is to record a macro while manually doing all the steps you mention. Once you're done, adjust the macro to be more flexible.

That's what I did last time I couldn't figure it out while writing my own macro's :)

Answer 3

I've found the solution. When you merge a document, it creates a new document with the merge results in it. Code fragment below explains.

wrdDoc = wrdApp.Documents.Add(TemplateFileName)
wrdDoc.Select()
Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge


wrdDoc.MailMerge.OpenDataSource(DataFileName)

wrdSelection = wrdApp.Selection()
wrdMailMerge = wrdDoc.MailMerge()
With wrdMailMerge
    .Execute()
End With

' This is the wrong thing to do. It just re-saves the template file you opened. '
'wrdDoc.SaveAs(OutputFileName) '

' The resulting merged document is actually stored '
' in the MailMerge object, so you have to save that '
wrdMailMerge.Application.ActiveDocument.SaveAs(OutputFileName)

wrdApp.Quit(False)