Automatically save a word mail merge document

[dougjack10]

I am trying to automatically save a word mail merge documents that has been generated from access.

I have generated some code that;

1. Opens the mail merge documents,
2. It then merges the mail merge documents to a new document based on a record in an access database.
3. We then go back to the mailmerge.docx and close it.
4. That leaves the merged documents open, and I am trying to automatically save.

When I try and save the merged document that is open, I get the following error above.
Automaton error, The object invoked was disconnected from the client. I have also received a general object error.

'objWord.ActiveDocument.SaveAs ("C:\Users\" & Me.Sitename & "_" & Me.ownername & ".docx")

When I use my bookmark merge from access it does save; and the code above works. Although it does not work with the mail merge code, the problem appears to be that the word document does not seem to have focus. In the bookmark example I open the document, and then save it. With the mailmerge you have two word documents.

Dim strSQL As String
Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & [Forms]![frm_ladnownerpayments]![IDoptionpayments]
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

Dim stDocName As String
   
On Error GoTo Err_Command235_Click
Dim WordObj As Word.Application
    Set WordObj = CreateObject("Word.Application")
    WordObj.Documents.Open ("C:\Users\ Paymentsinvoice_mailmerge.docx")
    WordObj.Visible = True
       
    With WordObj
    .ActiveDocument.MailMerge.Execute
   
     End With
    
     With WordObj
     Set WordDoc = .Documents.Open("C:\Users\Paymentsinvoice_mailmerge.docx")
     End With
            
    With WordDoc
  .Close savechanges:=False
   End With
      
    '----------------------------
     
 [COLOR=#CC0000]  With WordDoc
    .Visible = True
    Set wrdDoc = WordApp.Documents.Open(WDoc)
   .SaveAs ("C:\Users\" & Me.Sitename & "_" & Me.ownernameontitle & ".docx")[/color]
    
    End With

 

[SkipVought]

Hi,

'
   With [highlight #FCE94F]WordObj[/highlight]
      .Visible = True
      [highlight #FCE94F]With .[/highlight]Documents.Open(WDoc)
        .SaveAs ("C:\Users\" & Me.Sitename & "_" & Me.ownernameontitle & ".docx")
      [highlight #FCE94F]End With[/highlight]
    
   End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[macropod]

When you automate Word to run a mailmerge, the automation code is likely to hang while it waits for the user to answer 'Yes' to Word's mailmerge SQL prompt. You can overcome that by disabling Word's alerts, but then you have to reconfigure the document for mailmerge. And, when saving the mailmerge output document, you need to at least specify the file format as a save parameter. To that end, you need something along the lines of:

Sub RunMerge()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, strSQL As String, strOldSQL As String
Dim wdApp As New Word.Application, wdDoc As Word.Document
StrMMSrc = Me.FullName: StrMMDoc = "C:\Users\Paymentsinvoice_mailmerge.docx"
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & [Forms]![frm_ladnownerpayments]![IDoptionpayments]
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

With wdApp
  .Visible = False
  .DisplayAlerts = wdAlertsNone
  Set wdDoc = .Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
  With wdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
        LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", SQLStatement:=strSQL
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      .Execute Pause:=False
      .MainDocumentType = wdNotAMergeDocument
    End With
    .Close SaveChanges:=False
  End With
  .ActiveDocument.SaveAs2 Filename:="C:\Users\" & Me.Sitename & "_" & Me.ownernameontitle & ".docx", _
    FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
  .DisplayAlerts = wdAlertsAll
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub

Note: For testing, you might want to change '.Visible = False' to '.Visible = True'.

Cheers
Paul Edstein
[MS MVP - Word]