Access Macro 2 : Sending Email from Access using Docmd.sendobject

[ashish koul]

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