Mail Merge from access to word 1

[dougjack10]

I have a fairly simple request and I have been looking all over the internet for an answer.

All I want to do is;

Click on a button in access;

1. It opens a mail merge word document that has already been setup and linked to a query in the access database.
2. The document then merges to one letter, based on the current record in access form when I click the access button. The query in the mail merge document seems to be causing the problem.
3. The original mail merge document closes with no changes to the document.
4. The merged letter with the one record remains open.

I did have this working on a database years ago, using access 2003 and 2007. Although it does not seem to work in access 2010. See the code below. I ideally I am looking for some simple code that effectively has one variable, where I only need to put in the file path to the mail merge document.
If I remove the criteria in the access query, ([forms]![frm_name]![ID feild) that is linked to the merge document, it works and I get all 36 records merged letters into one document, but first of all I had to amend the registry,

https://support.microsoft.com/en-us...ain-document-that-is-linked-to-a-data-source.

This concerns me as I suspect this will need to be done to every machine that uses the database which seems impractical
My query includes criteria, [forms]![frm_name]![ID field] with the correct terms, and this seems to be causing the problem.

The error message is;

The method or property is not available because the document is not a mail merge.

Any ideas would be appreciated?
------------------------------------------------------------------------------------------------------------------------
Private Sub Command172_Click()
Dim stDocName As String

On Error GoTo Err_Command172_Click
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("F:\myfiles\letter.doc")
WordObj.Visible = True
With WordObj
.ActiveDocument.MailMerge.Execute
End With

With WordObj
Set WordDoc = .Documents.Open("F:\myfiles\letter.doc")
End With

With WordDoc
.Close SaveChanges:=False
End With

Exit_Command172_Click:
Exit Sub

Err_Command172_Click:
MsgBox Err.Description
End Sub

 

[Andrzejek]

Initial letter.docx ? You mean: Initial letter.xls, right?
[tt]
ActiveWorkbook.SaveAs fileName:= _
"\\" & Me.Sitename & "_" & Me.ID & "_" & "Initial letter.xls", _
...[/tt]

BTW - Don't you think Duane deserves a star for his help?
Click Great Post to award a star for a helpful post.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Based on a much earlier post, you stated:

With WordDoc
 .Close savechanges:=False
End With

Wouldn't you simply change the middle line? Did you try it?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Yes Duane definitely deserves a star.

The letter will be a docx

Duane suggested changing the middle line. However this is the mail merge document that closes and then the merged document remains open.

So if I replace .Close savechanges:=False, with .SaveAs ("C:\Users\NewDocument.docx"), it does save the document, albeit the wrong one.

If I add in

With WordDoc
WordObj.Visible = True
.SaveAs ("C:\Users\NewDocument.docx")

End With

I get the following error, Automaton error, The object invoked was disconnected from the client.
I suspect I need to identify the mew merged document somehow, that simply opens up as Form Letters1

I have just kept it simple for the moment by naming the file NewDocument.docx, the idea is to name the document based on fields in the form, where the access button is to generate the merge document.


Private Sub Command235_Click()

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
    WordObj.Visible = True
    .SaveAs ("C:\Users\NewDocument.docx")
  
    End With

 

[dhookom]

I'm not sure which line causes the error and why you are opening the same document multiple times.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

It is the last line.

With WordDoc
    WordObj.Visible = True
    .SaveAs ("C:\Users\NewDocument.docx")
  
    End With

1. I open the mailmerge.docx.
2. Then there is command to merge the document (.ActiveDocument.MailMerge.Execute)which opens a new unnamed document. This is the documents we want to leave open and save.
3. We then go back to the mailmerge.docx and close it.
4. I then want to save the merged unnamed document that is open, but I get the following error above.
Automaton error, The object invoked was disconnected from the client. I have also got a general object error.

When I use my bookmark merge it does save; the following code works. Although it does not work with the mail merge code.

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

 

[dhookom]

This is more of a Word question than Access. I'm not about to create a test environment for this. You might want to start a new thread in Forum707.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016