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

Posted on 2004-11-19
Medium Priority
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
  • 2
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12631589

Author Comment

ID: 12644481
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
ID: 12647851
No problem

Accepted Solution

modulo earned 0 total points
ID: 12681064
Closed, 500 points refunded.

Community Support Moderator
Experts Exchange

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Suggested Courses

850 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