htamraz1
asked on
access 2010 and word 2010 mailmerge using vba
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.
- 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
ASKER
Thanks GrahamSkan
Would you please include your code snippet with my previously sent code and resend the entire update (mine plus yours)
Would you please include your code snippet with my previously sent code and resend the entire update (mine plus yours)
I tried to indicate the position of the code by bracketing it with your comments, but here it is in place.
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 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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks for your help and pointing me in the right direction.
Open in new window