Need Access 2010 module to export and email an XLS file
Posted on 2015-01-12
I am working with Access 2010. I have a bunch of modules I use to export a report to a PDF and then attach and email this pdf.
I would like a module that exports a query into an XLS file and then attaches and emails the XLS file.
This is the code I am currently using:
On Error GoTo Err_Send_Click
Dim mydb As Database, RS As Recordset
Set mydb = DBEngine.Workspaces(0).Databases(0)
Dim attachPDF As String
Dim docname As String, ctl As Control, strTo As String
Dim path As String, subject As String, body As String
Dim attach As String, blnSuccessful As Boolean
Set RS = mydb.OpenRecordset("qry_email_gray")
path = "c:\temp\"
docname = "gray_ssoc_mtd"
attachPDF = path + docname & ".pdf"
subject = "Gray - SSOC MTD"
body = "Month to Date Report is Attached"
DoCmd.OutputTo acOutputReport, docname, acFormatPDF, attachPDF
Do Until RS.EOF
strTo = RS!Email
blnSuccessful = FnSafeSendEmail(strTo, subject, body, attachPDF, "", "")
Set RS = Nothing
Set mydb = Nothing