Mail Merge - Open Seperate Word Instance / Close connection to DB

[ordendelfai]

Hi Everyone!

I am trying to solve a small problem that is occuring with mail merge testing that I am doing with the below code. When I mailmerge, Word opens, performs the merge, prints, and closes both the template, and the merged document w/out asking the user if he wants to save. This is exactly how we want it, however, If the user already has a Word instance open (and is working on something), then Word obviously tries to close that document too and a "Do you want to save" box comes up.

I am not overly familiar with VBA, but I am trying to find a line of code that will always open this mail merge in a NEW Word instance, not the existing one already open.

A second problem is I am using a variant of the code below for a few special mail merges that do not automatically print/close word. So if the user closes the merged document and keeps word open (no documents), and tries to merge something else later, an error "Couldn't lock table "tblMailMerge1Employee". currently in use by user 'Admin' on machine 'IS-0047606'. I assume I just need a line of code that closes the connection to the DB (because if the user closes Word altogether, then tries to merge something else, it works fine).

If you can help with either or both these questions I would really appreciate it!! =D


Joel


-----------------------------Code---------------------------

Public Function MergeIt1EmployeePrint()
Dim objWord As Word.Document
Set objWord = GetObject("S:\Delphi\Templates\ExternalCorrespondence\1EmployeeTermGroup.doc", "Word.Document&quot
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Access database.
objWord.MailMerge.OpenDataSource _
Name:="C:\MyDB\Database.mdb", _
LinkToSource:=False, _
Connection:="TABLE tblMailMerge1Employee"
' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word
'can print the merged document.
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
objWord.Application.ActiveDocument.Saved = True
objWord.Saved = True
If WordWasNotRunning = False Then
objWord.Application.Quit
End If
End Function
-----------------------------Code---------------------------

 

[Nikita6003]

Hiya,

use CreateObject instead of GetObject - this'll create a new instance of Word when needed.
Change

Dim objWord As Word.Document
         Set objWord = GetObject("S:\Delphi\Templates\ExternalCorrespondence\1EmployeeTermGroup.doc", "Word.Document")

to

    Dim l_appWord As Word.Application
    Dim l_docWord As Word.Document
    
    Set l_appWord = CreateObject("Word.Application")
    Set l_docWord = l_appWord.Documents.Open("S:\Delphi\Templates\ExternalCorrespondence\1EmployeeTermGroup.doc")
    
    'use if you want your users to see the app during code processing
    l_appWord.Visible = True
    ' ...your mailmerge code here ...
    Set l_appWord = Nothing

HTH

Cheers
Nikki

 

[ordendelfai]

Nikki, thank you so much for helpin out . Below is how I used your suggestion. This worked very well and in the process I switched from connecting to a table, to using the query directly). This mail merge is one of our wierder ones where I don't want it to print/close automatically because the user has to manually make some changes to the document.

However, now that it opens a new instance word, a different problem creeped up. Before in my code the mail merge closed the template it was merging the final document from. The user would hit print after it merged, and then the user would close the document manually, but the template would not be there. With this version of the code, the template is there, and the user has to close it too, and it prompts to save it, which I do not want to happen. I am not sure what changed to cause this?

--------------------------NEW code--------------------------

Public Function MergeIt1EmployeeNOPrint()
Dim l_appWord As Word.Application
Dim objWord As Word.Document

Set l_appWord = CreateObject("Word.Application&quot
Set objWord = l_appWord.Documents.Open("C:\Delphi\Templates\ExternalCorrespondence\1EmployeeTermGroup.doc&quot
' Make Word visible.
objWord.Application.DisplayAlerts = False
objWord.Application.Visible = True
' Set the mail merge data source as the Access database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Delphi\Delphi.mdb", _
LinkToSource:=False, _
Connection:="QUERY qryMailMerge1Employee"
' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word can print the merged document.
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.Saved = True
objWord.Saved = True
End Function
-------------------------End New code-----------------------

 

[ordendelfai]

One other simpler question if I may.

I see that "objWord.Application.Quit" Quits the whole word instance altogether.

I have a few situations where I just need to close the document that just merged, not Word altogether. Is there a command to do that?

Thanks,

Joel