Sending Email from Access using Docmd.sendobject

Microsoft Access allows us to send any access object as attachment through outlook by using “Docmd.sendobject”

To know about Docmd.sendobject visit the below link
http://msdn.microsoft.com/en-us/library/bb214075(v=office.12).aspx


Syntax
DoCmd.SendObject ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile


ObjectType – acSendTable( to send table), acSendquery(to send query),acsendreport(to send report),ascendform(to send form), acSendModule(to send module), acSendNoObject( do not send any object)

ObjectName – Name of table, query, Report etc.

OutputFormat - acFormatHTML ,acFormatRTF ,acFormatSNP,acFormatTXT ,acFormatXLS,acFormatXLSB, acFormatXLSX ,acFormatXPS,acFormatPDF

To - email address to be added in to field of outlook

Cc- email address to be added in cc field of outlook

Bcc- email address to be added in bcc field of outlook

Subject – subject of email

Message text - “content of email”

Edit message - If you want to display or make any changes in email before sending then set it True, else false

Templatefile - The full name, including the path, of the file to use as a template for an HTML file.

Download Sample Access Database

Example1 Send Table as Attachment

Sub send_table_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = "koul ashish"
ccto = ""
bccto = ""
emailmsg = "Hi Ashish," & vbNewLine & vbNewLine & "Please find the report attached"
mailsub = "Sales Report Dec-2011"
On Error Resume Next
'acFormatXLS will export the table into excel format and will add that excel file as attachment
DoCmd.SendObject acSendTable, "sales_detail", acFormatXLS, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database

Example2 Send Query as attachment

Sub send_query_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = "koul ashish"
ccto = ""
bccto = ""
emailmsg = "Hi Ashish," & vbNewLine & vbNewLine & "Please find the report attached"
mailsub = "Sales Report Dec-2011"
On Error Resume Next
'acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendQuery, "rep_name_a", acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database

Example3 Send report as attachment

Sub send_report_using_send_object()
DoCmd.SetWarnings (False)
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = "koul ashish"
ccto = ""
bccto = ""
emailmsg = "Hi Ashish," & vbNewLine & vbNewLine & "Please find the report attached"
mailsub = "Sales Report Dec-2011"
On Error Resume Next
'acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendReport, "sales_detail", acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
DoCmd.SetWarnings (True)
End Sub

Download Sample Access Database

Example 4 Send a form as attachment

Sub send_form_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = "koul ashish"
ccto = ""
bccto = ""
emailmsg = "Hi Ashish," & vbNewLine & vbNewLine & "Please find the report attached"
mailsub = "Sales Report Dec-2011"
On Error Resume Next
'acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendForm, "sales_detail", acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database


If you like to send the formatted mails using access VBA Check the below link-
http://www.accessvbamacros.com/2011/12/send-access-table-data-as-formatted.html