Need Access 2010 module to export and email an XLS file

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:

Function send_gray_ssoc_mtd()
   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
     Application.Echo False
    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
    Application.Echo True
    Exit Function
    Resume exit_send_click
End Function
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Here is some fairly basic code to do what you have asked here:
export and email an XLS file

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strAttPath As String

strAttPath = "C:\yourFolder\YourFile.xls"

    'Export the query to an Excel File
    DoCmd.OutputTo acOutputQuery, "qryYourQuery", acFormatXLS, strAttPath
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("YourEmailAddressHere")
        objOutlookRecip.Type = olTo

        ' Set the Subject, Body, and Importance of the message.
        .Subject = "Email Excel File"
        .Body = "Here is your Access Query as an Excel file"

        Set objOutlookAttach = .Attachments.Add(strAttPath)

        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients

    End With
Set objOutlook = Nothing

Open in new window

...Based on the popular code here:

When you need to email a file other than an Access object, ( or otherwise control the email) cannot use SendObject, have to create an email with automation.
With this code you will have to add a reference to the MS Outlook object Library (In the VBA editor, click: Tools-->References)


Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.