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:
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
And the code for the ToWord module:
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")
'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.
.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 & "$]"
Set objApp = Nothing
MsgBox "Some error occured !! " & vbNewLine & Err.Number & " : " & Err.Description
DoCmd.Hourglass False 'Cursor back to normal
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?