SendObject Command in Microsoft Access: Features and Limitations for Sending Emails

Provided by: Luke Chung

Sending emails from Microsoft Access databases is a very powerful feature. Disseminating data and reports to your contacts can significantly improve your service and communications, and save paper and postage.

Microsoft Access lets you send emails using VBA module code or a macro with the DoCmd.SendObject method. This works for simple emails, but it also has significant limitations.

This paper discusses:

• Purpose of the SendObject Method
• Syntax of the SendObject Method
• Limitations of the SendObject Command
• Using Total Access Emailer vs. SendObject

Discuss this further in our blog.

---

Purpose of the SendObject Method

The SendObject command:

• Lets you email a message with an option to attach a table, query, form, report, or module data. The attachment can be in text, rich text, Excel, HTML, PDF and XPS formats.
• Uses Microsoft Outlook or another electronic mail application that uses the Mail Applications Programming Interface (MAPI) to broadcast your email. This can be an issue because Windows security intercepts each MAPI message and prompts the user for confirmation.

Syntax of the SendObject Method

The syntax information comes from the Microsoft VBA online help system with additional edits for clarification. VBA Syntax:

Macro Syntax:

Select SendObject as the action and set its arguments

Parameters

These are the definitions of each parameter. All of the parameters are optional.

Name	Description
ObjectType	An AcSendObjectType constant for the type of object to send: Name Value Description acSendNoObject -1 (Default) Don't send a database object acSendTable 0 Send a Table acSendQuery 1 Send a Query acSendForm 2 Send a Form acSendReport 3 Send a Report acSendModule 5 Send a Module
ObjectName	Name of an object of the objecttype argument. To use the active object, specify the object's type with the objecttype argument and leave this argument blank. If you leave both the objecttype and objectname arguments blank, Microsoft Access sends a message to the electronic mail application without a database object. If you run Visual Basic code containing the SendObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
OutputFormat	The output format of the object being sent: Name Value Description acFormatHTML HTML (*.html) HTML format acFormatRTF Rich Text Format (*.rtf) Rich text format (RTF) acFormatSNP Snapshot Format (*.snp) Access report snapshot acFormatTXT MS-DOS Text (*.txt) Plain text acFormatXLS Microsoft Excel (*.xls) Excel 2000-2003 acFormatXLSB Microsoft Excel Binary Workbook (*.xlsb) Excel binary acFormatXLSX Microsoft Excel Workbook (*.xlsx) Excel 2007 acFormatXPS XPS Format (*.xps) * XPS format acFormatPDF PDF Format (*.pdf) * PDF report * Requires Microsoft Access 2007 and the Microsoft Office 2007 Add-in: Save as PDF or XPS installed. Modules can only be sent in text format, so if you specify acSendModule for the objecttype argument, you must specify acFormatTXT for outputformat.
To	The email address of the recipient or list of recipients for the To line in the mail message. Separate the recipient names with a semicolon (;). If the recipient names aren't recognized by the mail application, the message isn't sent and an error occurs. If you leave this argument blank, Microsoft Access prompts you for the recipients.
Cc	The email address(es) for the CC line in the mail message, if any.
Bcc	The email address(es) for the BCC line in the mail message, if any.
Subject	Subject line for the mail message.
MessageText	The text for the body of the mail message, after the object. If blank, the object is all that's included in the body of the mail message.
EditMessage	Use True (–1) to open the electronic mail application immediately with the message loaded, so the message can be edited. Use False (0) to send the message without editing it. If you leave this argument blank, the default (True) is assumed.
TemplateFile	The full name, including the path, of the file to use as a template for an HTML file.

Remarks

The following rules apply when you use the SendObject action to include a database object in a mail message:

• You can send table, query, and form datasheets. In the included object, all fields in the datasheet look as they do in Access, except fields containing OLE objects. The columns for these fields are included in the object, but the fields are blank.
• For a control bound to a Yes/No field (a toggle button, option button, or check box), the output file displays the value –1 (Yes) or 0 (No).
• For a text box  bound to a Hyperlink  field, the output file displays the hyperlink for all output formats except MS-DOS text (in this case, the hyperlink is just displayed as normal text).
• If you send a form in Form view, the included object contains the form's Datasheet view.
• If you send a report, the only controls that are included in the object are text boxes (for .xls files), or text boxes and labels (for .rtf, .txt, and .html files). All other controls are ignored. Header and footer information is also not included. The only exception to this is that when you send a report in Excel format, a text box in a group footer containing an expression with the Sum function is included in the object. No other control in a header or footer (and no aggregate function other than Sum) is included in the object.
• When you send a datasheet or form in HTML format, one .html file is created. When you send a report in HTML format, one .html file is created for each page in the report.

Example

The following example includes the Employees table in a mail message in Microsoft Excel format and specifies To, Cc, and Subject lines in the mail message. The email is sent immediately, without editing.

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
    "Nancy Davolio; Andrew Fuller", "Joan Weber", , _
    "Current Spreadsheet of Employees", , False

Limitations of the SendObject Command

While the SendObject command is useful for sending an object or short message, it has several significant limitations:

• Messages must be 255 characters or less
• Messages are plain text and cannot be HTML format
• Cannot attach multiple files (limited to one attachment)
• Cannot attach a file on disk
• Cannot filter the data source or report to just the data you need to send
• Cannot specify the FROM address
• Cannot specify settings such as priority, sensitivity, and read receipt
• MAPI security prompts the user for each email to verify its okay
• Doesn't always work with email programs if it's not Outlook, Outlook Express, or Exchange

---

Using Total Access Emailer vs. SendObject

The SendObject method is fairly limited in the types of emails you can send. If you need more sophisticated emails, learn about our Total Access Emailer program. Total Access Emailer is the most popular email program for Microsoft Access and lets you easily create personalized emails to everyone in your list using your Access data and reports.

Email Messages of Unlimited Length

Unlike SendObject's 255 character limitation for the message, Total Access Emailer lets you send messages of unlimited length. The body of your message can be from text you enter, data from a memo field in your data source, or a file on disk. You can also send messages in text or HTML format.

Personalized Emails

Total Access Emailer lets you personalize each email by:

• Specifying a data source (table or query) with an email field and sending emails to everyone in the list
• Referencing fields from your data source directly in your subject and message text via the [FieldName] syntax
• Filtering data and reports for each recipient and attaching them to your message so recipients only see their own data
• Specifying the FROM address, which doesn't have to be your email address
• Using fields from your data source for the Subject and Message text, and attachments
• Setting emails with priorities, sensitivity, read receipt, etc.

HTML Emails using Access Reports and Embedded Graphics

Total Access Emailer has sophisticated HTML email capabilities so you can distribute professional quality emails. Your HTML emails can be created by:

• HTML text you enter
• HTML text stored in a field in your data source (could be different for each recipient)
• HTML from an Access report
  • Use the power of the Access report generator to combine and summarize data to create a different email for each recipient. This is not the same as attaching a report. This uses an Access report's HTML output as the body your message.
• HTML from a file on disk
  • If your HTML file references graphics in its folder, Total Access Emailer automatically includes them in the message so your users don't need to download the graphics after opening your message.

Lots of File Attachment Options

The SendObject command only sends one attachment to the message and it must be an Access object. Total Access Emailer lets you send an email with multiple attached files from your Access objects, files on disk, or an attachment field in your data source.

Bypass the Limitation of Outlook and MAPI

The SendObject command relies on Outlook and MAPI to send your messages. Due to security reasons, Windows prompts you for each message you try to send. This is unacceptable if you have more than a few emails to send:

• Total Access Emailer uses an SMTP or SSL server to bypass the limitations of MAPI and the security screens that require confirmation every time you send an email. Easily send an unlimited number of emails.
• If your organization uses Exchange, you already have SMTP. There are also free SMTP servers available from sources like Gmail.

No Programming Required: Interactive Wizard Interface

Total Access Emailer runs as an Access add-in and lets users easily create email specifications by pointing to a data source, and setting all the options. Each specification is automatically saved for reuse. Easily send one or more email blasts interactively.

Programmatic Interface and Royalty-Free Runtime Library

There's also a programmatic interface that can run the saved email blasts so you can launch an email based on an event like a button click. A royalty-free runtime version is available in the Professional Version so VBA developers can create Access applications with email capability for distribution to non-Total Access Emailer customers.

Preview Messages Before Sending Them

It's important to verify your emails are properly formatted and personalized before you send them to your contacts. Total Access Emailer lets you preview your messages by sending them to screen, a table, or an email address you designate. You specify how many messages to preview.

Advanced Features: Audit Trail and Error Handling

An optional audit trail can be maintained to document when an email was sent, and/or complete details including the FROM, TO, CC, BCC, subject, and message. With auditing turned on, if the email blast is interrupted (power, network, or internet failure), Total Access Emailer can restart your email blast where it left off.

Errors such as invalid email addresses are automatically stored in a table for you to review. Total Access email also tracks the times each email blast was sent, the total number of messages sent, the last time it was started, and when it finished.

Additional Resources

So if you're serious about sending emails from Microsoft Access, learn more about emailing from Access with Total Access Emailer. Here specific information about emailing Access reports. There's also a free trial version you can download to email from your Access database.

Discuss this further in our blog.

Good luck!