GenericExportandMailMerge

Function pfGenericExportandMailMerge(sQueryName As String, sExportTopic As String)
'============================================================================
' Name        : pfGenericExportandMailMerge
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call pfGenericExportandMailMerge(sQueryName, sExportTopic)
' Description:  exports to specified template from T:\Document Generator\Templates and saves in T:\In Progress\####\
'============================================================================

Dim sExportedTemplatePath As String, sTemplatePath As String, sOutputPDF As String
Dim sExportInfoCSVPath As String
Dim oWordAppDoc As Object
Dim qdf As QueryDef
Dim rstQuery As DAO.Recordset

sCourtDatesID = Forms![NewMainMenu]![ProcessJobSubformNMM].Form![JobNumberField]
sExportedTemplatePath = "T:\In Progress\" & sCourtDatesID & "\" & sCourtDatesID & "-" & sExportTopic & ".docx"
sExportInfoCSVPath = "T:\In Progress\" & sCourtDatesID & "\WorkingFiles\" & sCourtDatesID & "-Temp-Export-" & sExportTopic & ".xls"
sTemplatePath = "T:\Document Generator\Templates\" & sExportTopic & "-Template.docx"
sOutputPDF = "T:\In Progress\" & sCourtDatesID & "\WorkingFiles\" & sCourtDatesID & "-" & sExportTopic & ".pdf"
Set qdf = CurrentDb.QueryDefs(sQueryName)
qdf.Parameters(0) = sCourtDatesID
Set rstQuery = qdf.OpenRecordset

DoCmd.OutputTo acOutputQuery, sQueryName, acFormatXLS, sExportInfoCSVPath, False

Set oWordAppDoc = GetObject(sTemplatePath, "Word.Document")
oWordAppDoc.Application.Visible = False

oWordAppDoc.MailMerge.OpenDataSource Name:=sExportInfoCSVPath, ReadOnly:=True
oWordAppDoc.MailMerge.Execute
oWordAppDoc.MailMerge.MainDocumentType = wdNotAMergeDocument
oWordAppDoc.Application.ActiveDocument.ExportAsFixedFormat outputFileName:=sOutputPDF, ExportFormat:=wdExportFormatPDF
oWordAppDoc.Application.ActiveDocument.SaveAs filename:=sExportedTemplatePath

oWordAppDoc.Application.ActiveDocument.Close
Set oWordAppDoc = Nothing
rstQuery.Close
Set rstQuery = Nothing
qdf.Close
Set qdf = Nothing

End Function