MailMerge Main Docs lose Datasource Info when opened using VBA in Access.

Posted on 2004-11-19
Last Modified: 2012-06-21
I have a Word 2002 mailmerge document.  It has been setup with a
datasource which is a simple query inside an Access 2002 database.  When you open the
document from Word it prompts you if you want to run the query and the
mailmerge toolbar has most buttons enabled. You can execute the mailmerge right away.

If I use the following VBA code in the same Access Database to open the same
word document, it opens with nearly all mailmerge buttons Disabled.  You
are forced to relink the datasource.  

      Dim oApp As Word.Application
      Set oApp = CreateObject("Word.Application")
      oApp.Visible = True

      oApp.Documents.Open ("MailMergeMainDocument.DOC")

I have tried taking programatic control of Word with the following code, but the user is still
forced to pick the Access query 's name in a window.

    Set MyDB = CurrentDb()
    Set MyRs = MyDB.OpenRecordset("Test_takers_Mail_Merge_UNIQUE_Letters_QRY", dbOpenDynaset, dbSeeChanges)

    Dim myMerge As Word.MailMerge, MyDoc As Word.Document, ConnectStr As String
    Dim oApp As Word.Application
    If MyRs.BOF And MyRs.EOF Then
        MsgBox "There are no test score records matching your criteria which have the Letter_to_Send field filled."
      Set oApp = CreateObject("Word.Application")
      oApp.Visible = True
      While Not MyRs.EOF
         oApp.Documents.Open (MyRs.Fields("Letter_to_Send"))
         Set MyDoc = oApp.ActiveDocument
         Set myMerge = MyDoc.MailMerge
         ConnectStr = "Query " & DLookup("MailMergeQuery", "RA_Letter_Types", "[Doc Name of letter]=""" & MyRs.Fields("Letter_to_Send") & """")
         With myMerge
             .MainDocumentType = wdFormLetters
             .OpenDataSource Name:="C:\USSAIL\USSAILSQ.MDB", _
             ReadOnly:=True, _
             LinkToSource:=True, AddToRecentFiles:=False, _
             Connection:=ConnectStr, OpenExclusive:=False
             Debug.Print MyDoc.Name, Str(myMerge.MainDocumentType), Str(wdNotAMergeDocument), Str(wdFormLetters)
             If myMerge.State = wdMainAndDataSource Or myMerge.State = wdMainAndSourceAndHeader Then myMerge.Execute
         End With
    End If
Question by:AndyMagruder
    LVL 37

    Expert Comment

    by:Joanne M. Orzech

    Author Comment

    I found the answer to my own problem on Experts Exchange. It's under Visual Basic, Q_20514447.html .  Word now runs the mailmerge straight away with no prompts.

    Refering to Q_20514447.html, the exact line of code that made the difference is:


    In Q_20514447.html there's also some code that lets the VB determine what version of office Word is. My problem seems to happen when using Word 10 (Office XP/ 2002)

    LVL 37

    Expert Comment

    by:Joanne M. Orzech
    No problem

    Accepted Solution

    Closed, 500 points refunded.

    Community Support Moderator
    Experts Exchange

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
    The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now