• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8094
  • Last Modified:

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.
0
iosifrn
Asked:
iosifrn
  • 11
  • 9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
0
 
iosifrnAuthor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor 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
 
iosifrnAuthor Commented:
Thanks for your hard work.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now