Mail Merge from Access - Save Merged Document

Question

I am attempting to open a document from access, execute a mail merge, and then save the document output from the merge using VBA.

Here is my current attempt:

Dim templateName as String, tempRoot as String
tempRoot = "C:\report\"
templateName = tempRoot & "template.doc"

Dim objDoc As Word.Document
Dim objWord As New Word.Application
Set objDoc = objWord.Documents.Open(templateName)

objWord.Visible = True   

exportData "AnnualData", tempRoot & "annualData.txt" 'Outputs query to txt file for merge

objDoc.MailMerge.OpenDataSource NAME:= _
    tempRoot & "annualData.txt", ConfirmConversions:=False, ReadOnly _
    :=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:= _
    "", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="", SQLStatement1:="", SubType:= _
    wdMergeSubTypeOther

objDoc.MailMerge.Execute
objDoc.Close False      'Ideally after closing, the new document becomes the active document?

ActiveDocument.SaveAs tempRoot & "testReport.doc"    'And then save?

Set objWord = Nothing
Set objDoc = Nothing

I get the merged document, however, I am unable to save it. I receive an error stating that the command cannot be performed when no document is open.

If anyone can provide any suggestions, it would be appreciated.

Answer 1

I just went through this. Here's what I'm doing and it works well. oDocument is the merge form that the user selects via an open dialog box. The excel file is the query that I've previously exported and stuck in the users temp folder. I tried this technique with Access queries and temp tables, but found that using excel was much more trouble free.

The Sleep command is from an imported system dll function ( Public Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long) ) and gives Word time to run the merge. Actually, that may be all you need. This is using Office 2007.

If Not oDocument Is Nothing Then
                  ' get merge source file
               Set oFSO = New FileSystemObject
               Set oFolder = oFSO.GetSpecialFolder(TemporaryFolder)
               strTempFile = oFolder.Path & "\PTDMergeSource.xls"

                  ' run merge
               With oDocument.MailMerge
                   .MainDocumentType = wdFormLetters
                   .Destination = wdSendToNewDocument
                   .OpenDataSource strTempFile, WdOpenFormat.wdOpenFormatDocument, False, False, False, False, , , , , , , "SELECT * FROM `tblMerge$`", , False, WdMergeSubType.wdMergeSubTypeAccess
                   .Execute True
               End With
               Sleep 2
               oDocument.Close False
           Else
             MsgBox "Action was cancelled, or there was an error opening that document. Please try again, then try opening that document in Word. It may be someone else has locked that document (they are editing it). If the problem persists, email the document to the support contractor."
           End If

Answer 2

Changed ActiveDocument to objWord.ActiveDocument. Ended up with the desired results.

Thanks Remou.