Access and Mail merge

atrium

Registered User.
Local time
Tomorrow, 03:22
Joined
May 13, 2014
Messages
348
I have been trying to populate a merge document from access. I have the dotx files organised and the recipient file (Query) within my main access project.
The following code opens word, opens the selected file (dotx) but can't find the db file.

I am using access 2010 and .accdb database format. I have temporarily put in MsgBox lines to display the running content of fields and they are producing the right info.

Am I using the right code to do what I want?

------------------------------------------------------------------------------------

Private Sub DocumentsCbo_Click()
' load precedent template as a dotx, merge fields into precedent document, allow user to modify if needed, save it as a pdf
On Error GoTo ErrTrap
Dim MyPath As String ' Path of the source template
Dim DestPath As String ' Path of destination document
'MyPath = Me.FolderPath & Me.TemplateFileName
MyPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\Collection & Recovery Options Pty Ltd\CRO Precedents 2015\CRO3-01.dotx"
dbPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\TOGAS Copy\Development\TOGAS-Development-20141113am.accdb"
DestPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\ClientMergeFiles"
DocName = "\ClientReturnsRetainer.docx"
dbSourcePath = dbPath
MyStr = MyPath
MsgBox "MyPath = " & MyPath
MsgBox "MyStr = " & MyStr
MsgBox "DestPath = " & DestPath
MsgBox "dbPath = " & dbPath
MsgBox "DocName = " & DocName
MsgBox "dbSourcePath = " & dbSourcePath


Dim objWord As Object, strFile As String
strFile = MyStr
Set objWord = CreateObject("Word.Application")
'open word file
objWord.Documents.Open strFile
objWord.ActiveDocument.SaveAs DestPath & DocName ' save the document
'objWord.Documents.Add strFile
'Make word visible.
objWord.Application.Visible = True
'Set mail merge data source as the TOGAS Project
objWord.ActiveDocument.MailMerge.OpenDataSource Name:="Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\TOGAS Copy\Development\TOGAS-Development-20141113am.accdb", LinkToSource:=True, Connection:="Query PrecedentsQry", SQLStatement:="SELECT * FROM [PrecedentsQry]"
'Execute the Mail Merge
objWord.ActiveDocument.MailMerge.Execute
objWord.ActiveDocument.SaveAs DestPath & "filename"
objWord.Quit 0
'Reopen document
Set objWord = CreateObject("Word.Application")
objWord.Documents.Open DestPath & "filename"
obWord.Applicatiom.Visible = True
Set objWord = Nothing
Exit Sub
ErrTrap:
MsgBox Err.Description, vbCritical
End Sub

--------------------------------------------------------------------------

Any help would be greatly appreciated
 
After the merge file has been loaded it then looks for the db file.

I get a message to "Confirm Data Source" with Open Data Source and the only option is 'OLE DB Database Files' is highlighted

I press OK and get the error message 'Word was unable to open the data source'

I'm running this on a network and I'm sure it has something to do with using the full path to the data source as against the relative address. Could that be the reason??
 
Last edited:

Users who are viewing this thread

Back
Top Bottom