Open Word MailMerge from Access with Data Source loaded

[sparkyrose]

Hi all,

I have a problem very similar to this recent thread. Mine differs in that I don't have a single document or query. Rather the user can select the document.

Basically I have a dashboard in my Db that allows users to open a Windows dialog and select a Word doc to open and run a mail merge. The Db itself contains the data for the merge. When these docs (there are about a dozen) are opened directly from Windows Explorer they prompt the user "opening this document will run the following SQL command" and the data source loads automatically.

When I open Word from Access there is no prompt and the data source is not loaded. I would like the opening from Access to mimic the "regular" process. In the example listed in the thread I mention above, the query is passed to Word from Access (I believe), but since I have a different query for each document that won't work.

I open from Access using the code shown below.

Thanks in advance

Private Sub btnOpenLetters_Click()
  
'Set the filter string (patterns) for the open file dialog
    Dim strFilter As String
    Dim oApp As Object
    strFilter = "All Files (*.*)" & vbNullChar & "*.*" & vbNullChar & vbNullChar

    'Show the open file dialog with the custom title, the filters specified
    ' above, and starting in the root directory of the C: drive.
    Dim strFileToOpen As String
    strFileToOpen = OpenFile("Choose a file to open", strFilter, 0, "My document path")

    'Create an instance of MS Word
      Set oApp = CreateObject(Class:="Word.Application")
      oApp.Visible = True
      
      'Open the Document
      oApp.Documents.Open FileName:=strFileToOpen

        
        
End Sub

This refers to the following module

Option Compare Database

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (ByRef lpofn As OPENFILENAME) As Long
    
        

'This function shows the Windows Open File dialog with the specified
' parameters, and either returns the full path to the selected file,
' or an empty string if the user cancels.
Public Function OpenFile(ByVal Title As String, ByVal Filter As String, _
    ByVal FilterIndex As Integer, ByVal StartPath As String, _
    Optional OwnerForm As Form = Nothing) As String

    'Create and populate an OPENFILENAME structure
    'using the specified parameters
    Dim ofn As OPENFILENAME
    With ofn
        .lStructSize = LenB(ofn)
        If OwnerForm Is Nothing Then
            .hwndOwner = 0
        Else
            .hwndOwner = OwnerForm.Hwnd
        End If
        .lpstrFilter = Filter
        .nFilterIndex = FilterIndex
        .lpstrFile = Space$(1024) & vbNullChar & vbNullChar
        .nMaxFile = Len(ofn.lpstrFile)
        .lpstrFileTitle = vbNullChar & Space$(512) & vbNullChar & vbNullChar
        .nMaxFileTitle = Len(.lpstrFileTitle)
        .lpstrInitialDir = StartPath & vbNullChar & vbNullChar
        .lpstrTitle = Title
        .flags = OFN_FILEMUSTEXIST
        .flags = OFN_CREATEPROMPT
    End With

    'Call the Windows API function to show the dialog
    If GetOpenFileName(ofn) = 0 Then
        'The user pressed cancel, so return an empty string
        OpenFile = vbNullString
    Else
        'The user selected a file, so remove the null-terminators
        ' and return the full path
        OpenFile = Trim$(Left$(ofn.lpstrFile, Len(ofn.lpstrFile) - 2))
    End If
End Function