Mail Merge from Access to Word 2010 with VBA

Mail Merge from Access to Word 2010 with VBA.

I need a working VBA procedure that opens word 2007/2010/2013 using VBA from Access 2010, executes the merge from a given SQL query and displays the merged document.

Please help ASAP with some code suggestions.

Thanks, Iosif.
iosifrnProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
0
iosifrnProgrammerAuthor Commented:
Thanks for the links but I would like something with late object biding so it can be used in any version of Access(2007/2010/2013) and not depend on a specific Access/Word library. I am currently developing in Access 2010.
0
Rey Obrero (Capricorn1)Commented:
have a read on this

Using early binding and late binding in Automation
http://support.microsoft.com/kb/245115
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

iosifrnProgrammerAuthor Commented:
Because of different versions of Word on my client computers Late Binding makes more sense. What code recommendations do you have for Access 2010 VBA to merge with Word 2007/2010/2013 template?

Thanks, Iosif.
0
iosifrnProgrammerAuthor Commented:
I increased the points to 500 so I can get a working sample for this. Thanks
0
Rey Obrero (Capricorn1)Commented:
here is a sample late binding codes


Sub MergeIt()
   Dim objWord As Object, strFile As String
   strFile = "C:\Folder\Documents\MyWordDocument.docx"
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   objWord.MailMerge.OpenDataSource _
      Name:="C:\Program Files\Microsoft " & _
    "Office\Office\Samples\Northwind.mdb", _
      LinkToSource:=True, _
      Connection:="TABLE Customers", _
      SQLStatement:="SELECT * FROM [Customers]"
   ' Execute the mail merge.
   objWord.MailMerge.Execute
End Sub
0
iosifrnProgrammerAuthor Commented:
It opens the word document with the following code:
Dim objWord As Object, strFile As String
   strFile = MyStr
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   objWord.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Northwind.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.MailMerge.Execute

But after that gives me an error : "Object does not support this property or method"
I believe fails on this line - objWord.MailMerge.OpenDataSource ...
0
Rey Obrero (Capricorn1)Commented:
try changing thi line

 objWord.MailMerge.OpenDataSource

with

 objWord.Documents.MailMerge.OpenDataSource
0
iosifrnProgrammerAuthor Commented:
I still get the error:

Dim objWord As Object, strFile As String
   strFile = MyStr
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   
   'objWord.MailMerge.OpenDataSource
   objWord.Documents.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Northwind.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.MailMerge.Execute
0
Rey Obrero (Capricorn1)Commented:
try this one


Dim objWord As Object, strFile As String
   strFile = MyStr
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   

   objWord.ActiveDocument.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Northwind.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.ActiveDocument.MailMerge.Execute






.
0
iosifrnProgrammerAuthor Commented:
One more thing in this code and I will mark this as a solution.

This above works but it opens the template and the new merged document.
How do I close the template from VBA so the user only sees the merged document?

Thank you. We are so close I really appreciate this.
0
Rey Obrero (Capricorn1)Commented:
if you are creating a new document from template, change

objWord.Documents.Open strFile

with

objWord.Documents.Add strFile

here is the complete syntax
 objWord.Documents.Add(Template, NewTemplate, DocumentType, Visible)
0
iosifrnProgrammerAuthor Commented:
I tried the: objWord.Documents.Add strFile and getting the same result.

I don't mind if both documents (the template and merged document) are opening but I want to close the template automatically from Access so the user only has on the screen the merged document.

Thanks.
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the template...?


try this, add a line to save the open document before doing the merge

   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile


objWord.ActiveDocument.saveas  "c:\folderName\myDoc.doc"   'save the document

   objWord.ActiveDocument.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Northwind.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.ActiveDocument.MailMerge.Execute
0
iosifrnProgrammerAuthor Commented:
I tried but still leaves me with two documents open:

Private Sub cmd_Open_Report_Click()
On Error GoTo ErrTrap

MyStr = My_Path & Me.lst_Templates & ".docx"
   
   Dim objWord As Object, strFile As String
   strFile = MyStr
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
       objWord.ActiveDocument.saveas My_Path & "tempDoc.doc"    'save the document
       'objWord.Documents.Add strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   
   objWord.ActiveDocument.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Temp.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.ActiveDocument.MailMerge.Execute
   'objWord.Close True
   Set objWord = Nothing
   
Exit Sub
ErrTrap:
    MsgBox Err.Description, vbCritical
End Sub
0
Rey Obrero (Capricorn1)Commented:
<I tried but still leaves me with two documents open:>
do you see two open word documents?

try closing all open word documents,
close access, then open and run the codes again

before you do, change this part of your codes

   objWord.ActiveDocument.MailMerge.Execute
  objWord.Quit
   Set objWord = Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iosifrnProgrammerAuthor Commented:
Is prompting me to save the documents and then if I select no it close one by one.

Maybe I should just close both and reopen the new merged one. What do you think?
Any suggestions on that?
0
iosifrnProgrammerAuthor Commented:
This seems do to the job but if have a more elegant way to do it please let me know.

Private Sub cmd_Open_Report_Click()
On Error GoTo ErrTrap

MyStr = My_Path & Me.lst_Templates & ".docx"
   
   Dim objWord As Object, strFile As String
   strFile = MyStr
   Set objWord = CreateObject("Word.Application")
   'open word file
       objWord.Documents.Open strFile
       objWord.ActiveDocument.saveas My_Path & "tempDoc.doc"    'save the document
       'objWord.Documents.Add strFile
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   
   objWord.ActiveDocument.MailMerge.OpenDataSource _
      Name:="C:\EVO_CRM\Temp.mdb", _
      LinkToSource:=True, _
      Connection:="Query qry_Rpt_Word", _
      SQLStatement:="SELECT * FROM [qry_Rpt_Word]"
   ' Execute the mail merge.
   objWord.ActiveDocument.MailMerge.Execute
   objWord.ActiveDocument.saveas My_Path & "tempDoc.docx"
   objWord.Quit 0
   
   'reopen document
   Set objWord = CreateObject("Word.Application")
   objWord.Documents.Open My_Path & "tempDoc.docx"
   objWord.Application.Visible = True
   Set objWord = Nothing
   
Exit Sub
ErrTrap:
    MsgBox Err.Description, vbCritical
End Sub


Thanks, Iosif.
0
Rey Obrero (Capricorn1)Commented:
that will work..
0
iosifrnProgrammerAuthor Commented:
Thanks for your hard work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.