From Excel macro, Open (but do not run) an existing Mail Merge Doc

I created a mail merge word document and a mail merge Excel data file on my Vista32 bit system using Excel 2003. When I open the Word document manually, I get the 'SQL' message.

I ran an Excel macro to run the 'Mail Merge' and it was successful in that it DID NOT get the 'SQL' message. I am not confident that my solution will work on your computer. Color me skeptical. If this doesn't work for you, I still may have one or two more tricks up my sleeve, but it may take a few days.

My files are attached (which include extras) that do the following (all independent of each other):
a. Run the mail merge from Excel.
b. Open a new instance of Word from Excel.
c. Open a Word file (that has an AutoRun macro) from Excel with AutoRun enabled.
d. Open a Word file (that has an AutoRun macro) from Excel with AutoRun disabled.

Lewis

Code for the mail merge follows:

Option Explicit

Private Const sWordFile = "LJMMailMerge.doc"
Private Const sExcelMailMergeDataFile = "LJMMailMergeData.xls"


Sub OpenMicrosoftWordMailMergeDocument()

  'Enumerated constants reference:
  'http://include.wutils.com/com-dll/constants/constants-Word.htm
  
  Const wdCatalog = 3
  Const wdDefaultFirstRecord = 1
  Const wdDefaultLastRecord = -16
  Const wdDirectory = 3
  Const wdDoNotSaveChanges = 0
  Const wdEMail = 4
  Const wdEnvelopes = 2
  Const wdFax = 5
  Const wdFormLetters = 0
  Const wdMailingLabels = 1
  Const wdNotAMergeDocument = -1
  Const wdOpenFormatAuto = 0
  Const wdSendToNewDocument = 0
   

  Dim WordApp As Object
  Dim WordDoc As Object
  
  Dim sPath As String
  Dim sPathAndWordFile As String
  Dim sPathAndExcelMailMergeDataFile As String
  
  'Assume the word file is in the same file as this file
  sPath = ActiveWorkbook.Path & "\"
  
  'Create the Word file path and file name combination
  sPathAndWordFile = sPath & sWordFile
  
  'Create the Excel Mail Merge data file path and file name combination
  sPathAndExcelMailMergeDataFile = sPath & sExcelMailMergeDataFile
  
  'Make sure the Word file exists
  If LJMFileExists(sPathAndWordFile) = False Then
    MsgBox "Micrsoft Word file DOES NOT EXIST." & vbCrLf & _
           "Folder: " & sPath & vbCrLf & _
           "File:       " & sWordFile
    Exit Sub
  End If
  

  'Make sure the Excel Mail Merge Data file exists
  If LJMFileExists(sPathAndExcelMailMergeDataFile) = False Then
    MsgBox "Micrsoft Word file DOES NOT EXIST." & vbCrLf & _
           "Folder: " & sPath & vbCrLf & _
           "File:       " & sExcelMailMergeDataFile
    Exit Sub
  End If
  

  'Create Word application object
  Set WordApp = CreateObject("Word.Application")

  'Create Word Document Object and Open Word file
  'Make the word application visible
  'Set the focus on the Word document
  Set WordDoc = WordApp.Documents.Open(sPathAndWordFile)
  WordApp.Visible = True
  WordApp.Application.Activate
  
  'Set the document type to 'Form Letters'
  'If some other type see the enumerated constant list above
  WordDoc.MailMerge.MainDocumentType = wdFormLetters

  
  'Open the mail merge data file for mail merge
  WordDoc.MailMerge.OpenDataSource _
    Name:=sPathAndExcelMailMergeDataFile, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Data Source=" & sPathAndExcelMailMergeDataFile & ";Mode=Read", _
    SQLStatement:="SELECT * FROM `Sheet1$`"

  'Execute the mail merge
  With WordDoc.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
  End With

  'Close the original Word document that is the 'Mail Merge' Source
  WordDoc.Close SaveChanges:=wdDoNotSaveChanges
  
  'Close Microsoft Word
  'Word asks about what to do with the Mail Merge OUTPUT FILE
  WordApp.Quit
  
  'Clear object pointers
  Set WordApp = Nothing
  Set WordDoc = Nothing

End Sub

To open a new instance of Word from Excel:

Sub OpenNewInstanceOfWordWithFocus()

  Application.ActivateMicrosoftApp xlMicrosoftWord

End Sub

To open a Word document (with AutoRun enabled):

Sub OpenMicrosoftWordDocumentThatHasAutoStartMacro()
  'This opens a Microsoft Word document that has an Autorun Macro
  'to demonstrate the 'Auto Run' feature

  Const wdDoNotSaveChanges = 0

  Dim WordApp As Object
  Dim WordDoc As Object
  
  Dim sFile As String
  Dim sPath As String
  Dim sPathAndFile As String
  
  'Assume the word file is in the same file as this file
  sPath = ActiveWorkbook.Path & "\"
  
  'Create the Word file path and file name combination
  sFile = "LJMAutoStartMacro.doc"
  sPathAndFile = sPath & sFile
  
  'Make sure the Word file exists
  If LJMFileExists(sPathAndFile) = False Then
    MsgBox "Micrsoft Word file DOES NOT EXIST." & vbCrLf & _
           "Folder: " & sPath & vbCrLf & _
           "File:       " & sFile
    Exit Sub
  End If
  
  
  'Create Word application object
  Set WordApp = CreateObject("Word.Application")

  'Create Word Document Object and Open Word file
  'Make the word application visible
  'Set the focus on the Word document
  Set WordDoc = WordApp.Documents.Open(sPathAndFile)
  WordApp.Visible = True
  WordApp.Application.Activate
  
  'Delay so user can see progress
  'NOTE: sleep in Microsoft Word application destroys Word visibility
  Sleep 4000
  

  'Close Microsoft Word and discard changes
  WordDoc.Close SaveChanges:=wdDoNotSaveChanges
  WordApp.Quit
  
  'Clear object pointers
  Set WordApp = Nothing
  Set WordDoc = Nothing

End Sub

To disable AutoRun place the following line before the line that opens the Word document where 'WordApp' is the Word application object as defined in the previous example:

 'Disable AutoRun Macro
  WordApp.WordBasic.DisableAutoMacros (1)