Send Access Query as email Attachment 1

[dtay1132]

I want to start by apologizing for posting this same info in the Access Forms forum......sorry all.

I know there is a ton of info in this forum on sending attachments to email. In fact I have most of my email stuff being generated as SNP reports and attached because of you guys. I'm stuck though on sending the output of a query as an excel spreadsheet.

I have a form that users pick a report from through a combo box. There are 4 columns in the query that feed the combo box, only 1 displays.

Column 1 (0)= Report type (this is what the user sees and selects)
Column 2 (1)= Report Name - used to define the string for the object being run and attached to email
Column 3 (2)= Message - Provides user info about the report selected
Column 4 (3)= Query Name - This is the query that drives the report selected. this is what I want to output to excel and attach to an email.

The numbers in parenthesis are the column references for the combobox.

So here is what I've done so far:

The user selects the report from the combo box, selects a distribution option (option control) to pick print preview, report email or query to excel. Clicking OK runs a macro that looks at which option and then runs another macro that runs code (function). The preview and email report functions work perfectly. I can't get the query to excel to work at all. Here is the code:

Public Function Email_Excel()

    On Error Resume Next
    
    Dim stDocName As String
    Dim stTo As String
    Dim stSubject As String
    Dim stBody As String
    Dim stDate As String
    Dim stBatch As String
    Dim stCC As String
        
       
    stDocName = [Form_F_Reports Selection]![ReportSelect].Column(3)
    stTo = ""
    stCC = ""
    stSubject = "MyReportName " & ([Form_F_Reports Selection]![ReportSelect].Column(0)) & " Report"
    stDate = ""
    stBody = "Attached is the XXX " & ([Form_F_Reports Selection]![ReportSelect].Column(0)) & " report for the date range " & ([Form_F_Reports Selection]![Start_Date]) & " - " & ([Form_F_Reports Selection]![End_Date])
    
    
    DoCmd.SendObject acSendQuery, stDocName, acFormatXLS, , stTo, stCC, , stSubject, stBody, True

    
    
End Function

I can't take credit for the code above as it came straight from this forum and it works perfectly....until I changed the Object type and Object format and tried to read a different column in the combo box. Which of these three are the culprit?

 

[Remou]

Hi
You seem to have ended up with an extra comma here:
[tt]acFormatXLS, , stTo[/tt]
Try:
[tt]DoCmd.SendObject acSendQuery, stDocName, acFormatXLS, , stTo, stCC, stSubject, stBody, True[/tt]

 

[dtay1132]

Worked like a charm. My debugging skills aren't too great. Thanks for the help!