Mail Merge from query to word in access vba struggle

[sashapixie]

Hello There

This is probably the third time I have attempted this process and it fails every time.

I have the following code within a button on my form in access

Private Sub Command283_Click()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Dim fld As Word.FormField
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Tender Single Record Information Client Details")

rs.MoveFirst


'Open Word
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

'Print Tender Letter
TenderLetter = "H:\ESTIMATING - 4000\Tender Letter2.docx"
Set wrdDoc = wrdApp.Documents.Open(TenderLetter)

For Each fld In wrdDoc.FormFields
fld.Result = rs.Fields(fld.Name).Value
Next fld

Set wrdDoc = Nothing
rs.MoveNext
Wend
Set fld = Nothing
Set wrdApp = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I have fields set in a word document called Tender Letter2 and a query call Tender Single Record Information Client Details where the field names match those in the word template.

I can create bookmarks to merge singular information from a record into word no problem, this is not using field forms and does not refer to a query.

I have one record which has many clients so I have created the query to produce the addresses of each client, I need these to merge with the one singular document to create more than one letter.

I hope this makes sense.

Any help would be appreciated.

Thanks

 

[ranman256]

this is not a mail merge. Lookup mail merge in word help.
record a macro in word, run the real mail merge, stop the macro.
use that code and put it in Access to run the merge.

 

[sashapixie]

Hello Ranman256

I have taken your advice and now have the following code, which opens the word document but does not merge any information.

I have tried this angle before and I always seem to end up at the same point, it opens the file but that's as far as it goes.

Private Sub Command283_Click()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document


'Open Word
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

'Print Tender Letter
TenderLetter = "C:\Users\CherylHughes\Desktop\Priddy Database\Tender Letter3.docx"
Set wrdDoc = wrdApp.Documents.Open(TenderLetter)

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Users\CherylHughes\Desktop\Priddy Database\Priddy.accdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"QUERY Tender Single Record Information Client Details", SQLStatement:= _
"SELECT * FROM [Tender Single Record Information Client Details]", _
SQLStatement1:="", SubType:=wdMergeSubTypeOther
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

End Sub

 

[Micron]

With respect to your original code, what was the failure and where did it occur? I have to admit I've never done this, but wonder about a couple of things that don't look 100% kosher. Like a Wend withou a While, a variable TenderLetter that is not dimensioned (do you have Option Explicit set for all your modules?), you set the Word object to nothing but I don't see how you re-create it for the next record (assuming that it should after the rs.movenext)? Sorry if I'm just blowing smoke here.

 

[sashapixie]

I have found a work around with the following.

I have the code below in my access database:

Private Sub Command283_Click()

DoCmd.OutputTo acOutputQuery, "Tender Single Record Information Client Details", "ExcelBinaryWorkbook(*.xlsb)", "Z:\Priddy Database\Tender Letter DataSource2.xlsb", False, "", , acExportQualityScreen

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

'Open Word
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

'Print Tender Letter
TenderLetter = "H:\ESTIMATING - 4000\Tender Letter.docm"
Set wrdDoc = wrdApp.Documents.Open(TenderLetter)

wrdApp.Run "MergeWord"

End Sub

This exports the query into Excel and opens the word document and merges the information and view the document as preview results which allows my colleagues to amend the document as necessary.

The MergeWord code which is located in my word document is below:

Private Sub MergeWord()

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"Z:\Priddy Database\Tender Letter DataSource2.xlsb", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=Z:\Priddy Database\Tender Letter DataSource2.xlsb;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Databa" _
, SQLStatement:="SELECT * FROM `'Tender Single Record Informatio$'`", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle


End Sub


Thanks for all your help and advice.

 

[HiTechCoach]

Glad to see you got it working.

I have found a work around with the following.

Your solution is really not a work around the a Best Practice method for Mail merge.

You NEVER (one of the only times I use NEVER) want to perform a Word Mail Merge with an Access database that is already opened.

It is ALWAYS best to export the Access data to CSV (text), Excel, Word Data document, or if you must a separate/external Access back end database.