Automate the closing of Word from Access after MailMerge

[laurajchambers]

I'm trying to adapt and use code provided by 'Leigh P' to automate a mailmerge from Access, but it is not working.

I'll paste my code below. I have the code saved in a module, and I'm calling the function from a command button on a form.

The error I'm currently getting is a Microsoft Office Word error: "The Microsoft Office Access database engine cannot find the input table or query 'ailmerge_Source_Safeguardin'. Make sure it exists and that its name is spelled correctly."

The query definitely exists, and I know I've spelled it correctly as I copied and pasted the name into the code. Why when it executes is it omitting the first and last characters of the query name?

Any help much appreciated. Thank you.

Function MergeIt()
 
On Error GoTo ErrHandling
 
Dim objDoc As Word.Document
Dim objWord As Word.Application
Dim blnCreated As Boolean
Dim strFilename As String
Dim strQueryName As String
Dim strDBpath As String
 
strFilename = "C:\Users\Laura\Documents\Safeguarding_Certificate.docx"
strQueryName = "Mailmerge_Source_Safeguarding"
strDBpath = "C:\Users\Laura\Documents\Absences.accdb" 'Or probably just CurrentDb.Name
 
On Error Resume Next
 
Set objWord = GetObject(, "Word.Application")
 
If Err Then
Set objWord = CreateObject("Word.Application")
blnCreated = True
End If
 
On Error GoTo ErrHandling
 
Set objDoc = objWord.Documents.Open(strFilename)
 
'Make Word Visible
objWord.Visible = True
 
'Execute the MailMerge
With objDoc.MailMerge
'Set Merge Data Source
objDoc.MailMerge.OpenDataSource Name:=strDBpath, _
LinktoSource:=True, _
Connection:="QUERY " & strQueryName, _
SQLStatement:="SELECT * FROM " & strQueryName
.Destination = wdSendToNewDocument
.Execute
objWord.ActiveDocument.PrintOut False
objWord.ActiveDocument.Close wdDoNotSaveChanges
End With
 
'Close The form files and the merged document
objDoc.Close wdDoNotSaveChanges
 
If blnCreated Then
objWord.Quit
End If
 
Set objDoc = Nothing
Set objWord = Nothing
 
Exit Function
 
ErrHandling:
MsgBox "Whoops" 'Better error handling of course
 
End Function

[jimatqsi]

Laura,
Why does the description of your error differ from the title of the post?

That's very odd about leaving out the first and last characters. Just to start somewhere, have you tried modifying the name in the code, like adding a space to the beginning and end, just to see what happens?

Jim

[laurajchambers]

That was the title of the post from 'Leigh P's discussion, but it also describes what I'm hoping to achieve.

When I change the name of the query to include underscores at the start and end of the name, it then appears to skip past that error and then the next issue I get is an ODBC Microsoft Access Driver Login Failed error message: Could not find the file 'C:\Users\Laura\Documents.mdb'. I can't see why it's trying to find that file - I haven't declared it anywhere.

Is there a wider issue causing all these problems? I have added Object 12 References to Word and Access - are there any other references I should add?

Thanks for your help.

[NeoPa]

Laura,
It would be very helpful (now the code is posted with visible line numbers) if you could indicate exactly which line the error occurs on.

Let's start with the first error first. Get that understood and resolved and then think about moving on to the second.

Please start by checking that your code compiles correctly (See Before Posting (VBA or SQL) Code).