Sending an email out from Access 2010 - Access Programmers

Sending an email out from Access 2010

12/06/2012

This blog will show you how to send out an email from Outlook using 3 different methods. In my example I will send out an existing report as an attachment to an email.

In my example I have a form with a button which opens the report and emails the report as an attachment using Microsoft Outlook. The button click event procedure contains code to open and then email the report.

  • The first step is to open the report:

DoCmd.OpenReport "rptInvoice", acViewPreview, , "UserID=" & Me.CustomerList

EXPLANATION:

DoCmd.OpenReport: command that opens a report

“rptInvoice”: Object Name (report name)

acViewPreview: View (preview the report)

“UserID=” & Me.CustomerList: the parameter I am taking from the form and passing to the report

  • Email the report:

I will show you three different methods of emailing the report as an attachment

  • Send the report using the SendObject method

The SendObject method is used to include a specific datasheet, form, report, or module from the Access database in an email message. This code will open the report, attach the report, and send the report.

DoCmd.SendObject acSendReport, "rptInvoice", acFormatPDF, "email@company.com", , , "Invoice Report", "Attached please find the Invoice Report"

EXPLANATION:

DoCmd.SendObject: command that sends the database object

acSendReport: Object Type (report)

“rptInvoice”: Object Name (report name)

acFormatPDF: Output Format (PDF file)

email@company.com: Message To (email address to send the email to)

“Invoice Report”: Subject of email

“Attached please find the Invoice Report”: Message Text (body of email)

 

For a more thorough explanation please see the Microsoft website for DoCmd.Send Object Method: http://msdn.microsoft.com/en-us/library/ff197046.aspx

  • Save the report locally then display the Outlook message before sending:

'Save report to local drive
Dim strFile As String
strFile = "C:\TestPDF\InvoiceReport_" & Format(Now, "YYYYMMDD_hms") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFile

EXPLANATION:

DoCmd.OutputTo: command that outputs the report

acOutputReport: Object Type (report)

“rptInvoice”: Object Name (report name)

acFormatPDF: Output Format (PDF file)

strFile: path and file name to save to

'Show user the Outlook message before sending
'Call the function SendEmailDisplayOutlook
Call SendEmailDisplayOutlook("cmendoza@alligatortek.com", "Invoice Report", "Attached please find the Invoice Report", strFile)

Public Function SendEmailDisplayOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
ReportPath As String)

' Get Outlook using the "New" keyword
Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem


' Create a new email object
Set olMailItem = olApp.CreateItem(0)
' Add the To/Subject/Body/Attachments to the message then display the message
With olMailItem
.To = MsgTo
.Subject = MsgSubject
.Body = MsgBody
.Attachments.Add ReportPath
.Display
End With

Set olMailItem = Nothing
Set olApp = Nothing

End Function

  • Save the report locally then send the message using Outlook:

'Save report to local drive
Dim strFile As String
strFile = "C:\TestPDF\InvoiceReport_" & Format(Now, "YYYYMMDD_hms") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFile

EXPLANATION:

DoCmd.OutputTo: command that outputs the report

acOutputReport: Object Type (report)

“rptInvoice”: Object Name (report name)

acFormatPDF: Output Format (PDF file)

strFile: path and file name to save to

‘Do not show user the Outlook message before sending
'Call the function SendEmailOutlook
Call SendEmailOutlook("cmendoza@alligatortek.com", "Invoice Report", "Attached please find the Invoice Report", strFile)

Public Function SendEmailOutlook( _
MsgTo As String, _
MsgSubject As String, _
MsgBody As String, _
ReportPath As String)

' Get Outlook using the "New" keyword
Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem

' Create a new email object
Set olMailItem = olApp.CreateItem(0)

' Add the To/Subject/Body/Attachments to the message then send the message
With olMailItem
.To = MsgTo
.Subject = MsgSubject
.Body = MsgBody
.Attachments.Add ReportPath
.Send
End With

Set olMailItem = Nothing
Set olApp = Nothing

End Function

Summary: Above I have explained 3 different ways to send out a report as an attachment through Outlook. The first 2 methods will create the email and open it in Outlook to display the email to the user before sending the email. The last method creates the email and sends it out automatically; the user never sees the email being sent.

*You may need to reference the Microsoft Outlook object library if your database does not already reference it. To add a reference, open the VBA module you are working on, go to Tools then choose References, check off the Microsoft Outlook object library.