?
Solved

access 2010 and word 2010 mailmerge using vba

Posted on 2015-02-17
6
Medium Priority
?
368 Views
Last Modified: 2016-02-10
I am trying to initiate a Word 2010 mailmerge from Access 2010 that would:
- Run query in Access that run a make table
- Use a specific Word template
- Execute mailmerge that opens document based on template and links to my table
- Keep only the resulting mailmerge document open and close other docs based on the template

When I run the code below, the query seems to run and Word opens, but Word runs very slow -- almost as if there is some merging or activity taking place in the background. This happens even if I am merging only one record. It seems almost as if the code is not releasing the word document so I can click on the File menu to save, etc. It's literally stuck and the only way for me to exit it to click the close "X" at the top right corner of the Word doc to close the document.

What am I doing wrong?

Thanks for any insights. Code used is below.

Private Sub cmdMergeIT_Click()

DoCmd.SetWarnings False

DoCmd.OpenQuery "Art Docket Recommendations Word", acViewNormal

DoCmd.SetWarnings True

   'For use as TEMPLATE
        Dim objApp As Word.Application
   'For use as TEMPLATE
        Dim objWord As Word.Document
        '
        Dim ObjWord2 As Word.Document
        '
   'For use as TEMPLATE
        Set objApp = New Word.Application
   'For use as TEMPLATE on GRD Server - added from EE
        Set objWord = objApp.Documents.Add("K:\DUG\Templates\docketcharttemplate_newlayout_portrait.dot")
   ' Make Word visible.
        objWord.Application.Visible = True
   
   ' Set the mail merge data source as the DUG Access Database

   objWord.MailMerge.OpenDataSource _
     Name:="C:\Users\HT\My Documents\Gifts\DUG.mde", _
     LinkToSource:=True, _
       Connection:="TABLE tblDocketRecommendations", _
       SQLStatement:="SELECT * FROM [tblDocketRecommendations]"

   'Execute the mail merge.
   objWord.MailMerge.Execute
'=========================================================
' code  to close mail merge doc based on template
  For Each ObjWord2 In objApp.Documents
    If ObjWord2.Name = "Document1" Then
      ObjWord2.Close False
    End If
  Next

End Sub

Open in new window

0
Comment
Question by:htamraz1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40614454
The most obvious thing is that you seem to be leaving the Word application that you have just created. There is a risk that you will have multiple Word applications open. This can create conflicts in Word. The modification below uses any existing instance in preference to creating a new one.
'...
   'For use as TEMPLATE
        On Error Resume Next
            Set objApp = GetObject(, "Word.Application")
        On Error GoTo 0
        If objApp Is Nothing Then
            Set objApp = CreateObject("Word.Application")
        End If
   'For use as TEMPLATE on GRD Server - added from EE
'...

Open in new window

0
 

Author Comment

by:htamraz1
ID: 40614539
Thanks GrahamSkan

Would you please include your code snippet with my previously sent code and resend the entire update (mine plus yours)
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40614639
I tried to indicate the position of the code by bracketing it with your comments, but here it is in place.
Private Sub cmdMergeIT_Click()

DoCmd.SetWarnings False

DoCmd.OpenQuery "Art Docket Recommendations Word", acViewNormal

DoCmd.SetWarnings True

   'For use as TEMPLATE
        Dim objApp As Word.Application
   'For use as TEMPLATE
        Dim objWord As Word.Document
        '
        Dim ObjWord2 As Word.Document
        '
   'For use as TEMPLATE
        On Error Resume Next
            Set objApp = GetObject(, "Word.Application")
        On Error GoTo 0
        If objApp Is Nothing Then
            Set objApp = CreateObject("Word.Application")
        End If
   'For use as TEMPLATE on GRD Server - added from EE
        Set objWord = objApp.Documents.Add("K:\DUG\Templates\docketcharttemplate_newlayout_portrait.dot")
   ' Make Word visible.
        objWord.Application.Visible = True
   
   ' Set the mail merge data source as the DUG Access Database

   objWord.MailMerge.OpenDataSource _
     Name:="C:\Users\HT\My Documents\Gifts\DUG.mde", _
     LinkToSource:=True, _
       Connection:="TABLE tblDocketRecommendations", _
       SQLStatement:="SELECT * FROM [tblDocketRecommendations]"

   'Execute the mail merge.
   objWord.MailMerge.Execute
'=========================================================
' code  to close mail merge doc based on template
  For Each ObjWord2 In objApp.Documents
    If ObjWord2.Name = "Document1" Then
      ObjWord2.Close False
    End If
  Next

End Sub

Open in new window

The Mail Merge main document is not usually a template, because merge execution doesn't modify the document so it can simply be closed.
You can use a select query as an input, so if you create a Select version of your query, you can set it as the datasource at the document  design time. That way you can get away with little or no code
Private Sub cmdMergeIT2_Click()

   'For use as TEMPLATE
        Dim objApp As Word.Application
   'For use as TEMPLATE
        Dim objWord As Word.Document
        '
   'For use as TEMPLATE
        On Error Resume Next
            Set objApp = GetObject(, "Word.Application")
        On Error GoTo 0
        If objApp Is Nothing Then
            Set objApp = CreateObject("Word.Application")
        End If
   'Open the main document
        Set objWord = objApp.Documents.Open("K:\DUG\Templates\docketcharttemplate_newlayout_portrait.doc")
   ' Make Word visible.
    objWord.Application.Visible = True
   'execute h
    objWord.MailMerge.Execute
'=========================================================
' code  to close mail merge main doc
    objWord.Close False

End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:htamraz1
ID: 40615029
Thank you. This is helpful, but did not quite resolve the issue. I have gotten a little closer though. Perhaps you shed some light here.

I notice a strange and unexplained behavior that when Word 2010 opens in MAXIMIZED screen mode, I cannot click on any of the top menus (e.g. File, Home, Insert, etc.). However, when I restore the screen size, I can access the menus. This behavior is bizarre and unexplained. Have you ever heard of this?

It would be a plus to make sure the Word instance that opens is not in MAXIMIZED mode. If we can resolve this, I would be happy to put this post to rest.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 40615067
I don't immediately know why your Word opens with what sounds like full-screen mode that is available in some other applications. In those, the {Esc} key usually puts it back to 'normal'
However this code:
objApp.WindowState = wdWindowStateNormal

Open in new window

0
 

Author Comment

by:htamraz1
ID: 40617307
In my case objApp.WindowState = wdWindowStateMinimize did the trick. The issue no longer happens after I added this to the code.

Thanks for your help and pointing me in the right direction.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question