Results 1 to 1 of 1
  1. #1
    Join Date
    May 2013
    Posts
    32

    Exclamation Unanswered: Exporting to work in a mail merge

    I am trying to get a database to produce a letter using fields on a form, in a mail merge. Although I've managed to do this, I'm running into quite a few problems.

    I've attached the code here for the form:

    Code:
    Private Sub Print_Click()
        DoCmd.OutputTo acOutputQuery, "RFQ Query", acFormatXLS, "H:\Request for Quotation\Test.xls"
        MergeToWord "H:\Request For Quotation\Test.doc", "H:\Request for quotation\Test.xls"
        DoCmd.Close , "H:\Request for quotation\test.doc", acSaveNo
    End Sub
    And the code for the ToWord module:

    Code:
    Public Sub MergeToWord(strDocName As String, sourceName As String)
        Dim objApp As Word.Application, tmpSheetName As String, saveAsName As String
        Dim splitPos As Long
    
        'Open Mailmerge Document, Start Word
        Set objApp = CreateObject("Word.Application")
        DoCmd.Hourglass False
        
        'This step will obtain the sheet name from the EXCEL file.
        tmpSheetName = Mid(sourceName, InStrRev(sourceName, "\") + 1)
        tmpSheetName = Mid(tmpSheetName, 1, InStr(tmpSheetName, ".") - 1)
        
         'saveAsName = "H:\request for quotation\RFQ.doc" - Removed this, to be put back if mail-merge errors page can be corrected.
        
        With objApp
            .Visible = True                             'Make it visible
            .Documents.Open strDocName                  'Open the Mailmerge Document
            .ActiveDocument.MailMerge.OpenDataSource Name:=sourceName, ReadOnly:=True, LinkToSource:=True, SQLStatement:="SELECT * FROM [" & tmpSheetName & "$]"
            .ActiveDocument.MailMerge.Execute Pause:=False
        End With
    exitOnErr:
        Set objApp = Nothing
        Exit Sub
    ErrorHandler:
        MsgBox "Some error occured !! " & vbNewLine & Err.Number & " : " & Err.Description
        DoCmd.Hourglass False 'Cursor back to normal
        Resume exitOnErr
    End Sub
    When this is run the following happens.

    Screen shot 1:

    A box come up asking for which table type to use and I have to go into options and select system tables too then the query, anything I can add to make this automatic, so a user doesn't have to select this?

    Screen shot 2:

    When the word document opens, it opens the read-only template as well as the merged document. I'd like the template to be closed automatically or only one document to be opened. How can I amend my code to do this?

    Screen shot 3:

    A third word document displays with mail merge errors, even though there are none. How do i stop this from popping up as well?

    Thank you in advance!
    Attached Thumbnails Attached Thumbnails Screen1.JPG   Screen2.JPG   Screen3.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •