INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automatically save a word mail merge document

Automatically save a word mail merge document

(OP)


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.

CODE -->

'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.


CODE -->

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
      
    '----------------------------
     
   With WordDoc
    .Visible = True
    Set wrdDoc = WordApp.Documents.Open(WDoc)
   .SaveAs ("C:\Users\" & Me.Sitename & "_" & Me.ownernameontitle & ".docx")
    
    End With 




RE: Automatically save a word mail merge document

Hi,

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Automatically save a word mail merge document

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:

CODE

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]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close