Mail Merge File Name 1

[pluto1415]

SO - Here's my code: (much thanks to the FAQ writer!)

Option Compare Database
Private Sub CmdMergeIt_Click()
On Error GoTo ErrorHandler

Dim strDocName As String
strDocName = "s:\document preparation\sales\offer letter.dot"

Call OpenMergedDoc(strDocName)

Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub OpenMergedDoc(strDocName As String)
On Error GoTo WordError

Dim objWord As New Word.Application
Dim objDoc As Word.Document
Dim strCurrentPrinter As String

strCurrentPrinter = objWord.ActivePrinter 'obtains name of current default printer

Set objDoc = objWord.Documents.Open(strDocName)

objDoc.MailMerge.Destination = wdSendToNewDocument
objDoc.MailMerge.Execute

objWord.Application.Documents(2).Close wdDoNotSaveChanges
objWord.Application.ActivePrinter = "Hyland Software Virtual Printer" 'changes printer to hyland
objWord.Application.PrintOut 'prints document
objWord.Application.ActivePrinter = strCurrentPrinter 'changes printer back to default

objWord.Application.Documents(1).Close wdDoNotSaveChanges

objWord.Application.Quit

Set objWord = Nothing
Set objDoc = Nothing
'Application.Quit
Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Quit
End Sub

I have multiple documents (around 30) that could be merged, depending on 2 variables. Those 2 variables are in my DB, and I have a query that queries a big table for the correct document name. The correct document name would be found at queries!qrydocselect.pa so, I really want it to do more like:
strDocName = "queries![qrydocselect].[pa]" but it keeps telling me it can't find that file. Obviously it's not resolving, but I can't figure it out. Any ideas?

 

[PHV]

Use either the DLookUp function or a Recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pluto1415]

Thanks! I couldn't get recordset to work, but DLookup worked like a charm! (wish I would have started with it instead of recordset)