Mail merge started by VBA in Access let Word open Database again

Question

Hi,

I'm working on a Access database which generates some mails with mail merge called from VBA code in the Access database. The problem is that if I open a new Word document and start the mail merge (VBA), Word opens the same Access database (which is already open) to get the data. Is there any way to prevent this? So that the already opened instance of the database is used?

After some testing I get a strange behavior: If I open the Access database holding the SHIFT-Key the mail merge does not open an other Access instance of the same database. If I open the Access database without holding the key, I get the described behavior.

My mail merge VBA code:

On Error GoTo ErrorHandler

    Dim word As word.Application
    Dim Form As word.Document

    Set word = CreateObject("Word.Application")
    Set Form = word.Documents.Open("tpl.doc")

    With word
        word.Visible = True

        With .ActiveDocument.MailMerge
            .MainDocumentType = wdMailingLabels
            .OpenDataSource Name:= CurrentProject.FullName, ConfirmConversions:=False, _
                ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
                PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
                WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
                SQLStatement:="[MY QUERY]", _
                SQLStatement1:="", _
                SubType:=wdMergeSubTypeWord2000, OpenExclusive:=False
            .Destination = wdSendToNewDocument
            .Execute
            .MainDocumentType = wdNotAMergeDocument
        End With
    End With

    Form.Close False
    Set Form = Nothing

    Set word = Nothing

Exit_Error:
    Exit Sub
ErrorHandler:
    word.Quit (False)
    Set word = Nothing
    ' ...
End Sub

The whole thing is done with Access / Word 2003.

Update #1 It would also help if someone could tell me what the exact difference is between opening Access with or without the SHIFT-Key. And if it is possible to write some VBA code to enable the "features" so if the database is opened without the SHIFT-Key, it at least "simulates" it.

Cheers, Gregor

Answer 1

When I do mailmerges, I usually export a .txt file from Access and then set the mail merge datasource to that. That way Access is only involved in exporting the query and then telling the Word document to do the work via automation, roughly as follows:

    Public Function MailMergeLetters() 
           Dim pathMergeTemplate As String
            Dim sql As String
            Dim sqlWhere As String
            Dim sqlOrderBy As String


'Get the word template from the Letters folder  

            pathMergeTemplate = "C:\MyApp\Resources\Letters\"

'This is a sort of "base" query that holds all the mailmerge fields
'Ie, it defines what fields will be merged.

            sql = "SELECT * FROM MailMergeExportQry" 

            With Forms("MyContactsForm")

' Filter and order the records you want
'Very much to do for you

            sqlWhere = GetWhereClause()
            sqlOrderBy = GetOrderByClause()

            End With

' Build the sql string you will use with this mail merge

            sql = sql & sqlWhere & sqlOrderBy & ";"

'Create a temporary QueryDef to hold the query

                Dim qd As DAO.QueryDef
                Set qd = New DAO.QueryDef
                    qd.sql = sql
                    qd.Name = "mmexport"

                    CurrentDb.QueryDefs.Append qd

' Export the data using TransferText

                        DoCmd.TransferText _
                            acExportDelim, , _
                            "mmexport", _
                            pathMergeTemplate & "qryMailMerge.txt", _
                            True
' Clear up
                    CurrentDb.QueryDefs.Delete "mmexport"

                    qd.Close
                Set qd = Nothing

'------------------------------------------------------------------------------
'End Code Block:
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Start Code Block:
'OK. Access has built the .txt file.
'Now the Mail merge doc gets opened...
'------------------------------------------------------------------------------

                Dim appWord As Object
                Dim docWord As Object

                Set appWord = CreateObject("Word.Application")

                    appWord.Application.Visible = True

' Open the template in the Resources\Letters folder:

                    Set docWord = appWord.Documents.Add(Template:=pathMergeTemplate & "MergeLetters.dot")

'Now I can mail merge without involving currentproject of my Access app

                        docWord.MailMerge.OpenDataSource Name:=pathMergeTemplate & "qryMailMerge.txt", LinkToSource:=False

                    Set docWord = Nothing

                Set appWord = Nothing

'------------------------------------------------------------------------------
'End Code Block:
'------------------------------------------------------------------------------

        Finally:
            Exit Function

        Hell:
            MsgBox Err.Description & " " & Err.Number, vbExclamation, APPHELP

        On Error Resume Next
            CurrentDb.QueryDefs.Delete "mmexport"

            qd.Close
            Set qd = Nothing

            Set docWord = Nothing
            Set appWord = Nothing

            Resume Finally

        End Function

To use this, you need to set up your Resources\Letters subfolder and put your mailmerge template word file in there. You also need your "base" query with the field definitions in your Access App (in the example, it is called MailMergeExportQry. But you can call it anything.

You also need to figure out what filtering and sorting you will do. In the example, this is represented by

sqlWhere = GetWhereClause()
sqlOrderBy = GetOrderByClause

Once you have got your head round those things, this is highly reusable.