"Send Email" button on Query-Based Report

[athyeh]

Hello Everybody,

The creation of this database has been a great journey and I hope that we are nearing its completion and off to new and interesting challenges.

Aside from cosmetic changes, one of the last things that I need to implement is a "Send Email" button. I have seen lots of documentation on this but have not been able to piece it all together.

Is it possible to create a button to a query-generated Report and then have that button's "Click Event" be the emailing of that report to that person?

If so can someone walk me through the VBA code to do this (I am VBA novice).

I have employee ID information stored in fields: "First Name", "Org ID", "Email" under table: "tblNames"

Thanks so much

[Dal Jeanis]

Were you wanting the button to be on the report, on the ribbon when the report is being displayed, or on the form that launches the report?

[athyeh]

On the ribbon would be the best.

[June7]

Here's how I customize ribbon: http://forums.aspfree.com/microsoft-...at-303789.html

[athyeh]

This seems to be pretty complicated and may be above my understanding right now. I am going through VBA tutorials would putting a button on a report be "easier"?

[Dal Jeanis]

Here's the code that would go behind a button named cmdMailReport on the form. This assumes that the report is an existing report.

Private Sub cmdMailReport_Click()
On Error GoTo Err_cmdMailReport_Click

    Dim stDocName As String
    Dim stToEmail As String
    Dim stSubject As String
    Dim stBody As String

    stDocName = "MyReportName"
    stToEmail = "recipient1@sample.com;recipient2@sample.com"    
    stSubject = "MyReportName For " & Format(Date(), "mm/dd/yyyy")
    stBody = "MyReportName Is Attached for your Review as/of " & Format(Date(), "mm/dd/yyyy")

    DoCmd.SendObject acReport, stDocName, acFormatPDF, stToEmail, , ,stSubject, StBody

Exit_cmdMailReport_Click:
    Exit Sub

Err_cmdMailReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdMailReport_Click
    
End Sub

I've put in a default subject line, body test and recipients, but you don't have to code those, you could let the user put them in themselves.

Check this page for all the options on the SendObject command http://msdn.microsoft.com/en-us/libr.../ff197046.aspx

Here's the general format:
.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

OutputFormat for a report can be either PDF format (acFormatPDF)or RTF format (acFormatRTF)