If you’re still using snail mail or a fax machine to mail Access reports, you have to know that your users have more important things to do than baby-sit a fax, and I’m sure they don’t like waiting on the postal system either. There’s an easier and quicker way to get information to the people who need it: Send the reports from inside Access using your default e-mail client. As I’ll explain in this article, the process is almost instantaneous and easy to automate. I’ll use Access 2002 and Outlook Express 6 in my examples.

Sharing information manually
Access provides a few menu commands for sending objects manually, so you don’t have to automate the process. If your need is infrequent, it might be just as easy to use the Access interface options as follows:

  1. ·        Select the object you want to send in the Access Database window. Keep in mind that you can’t send a macro object.
  2. ·        Select Send To from the File menu.
  3. ·        The resulting Send dialog box offers two options: Mail Recipient and Mail Recipient (As Attachment). Most of the time you’ll choose the latter.
  4. ·        Choose a format and click OK. Depending on your choice, Access may prompt you for more information (see Table A).
  5. ·        Once Access has all the information it needs, it will open a New Message window using your default mail client. That window will include the Access object you selected in step one as an attachment or embedded in the body of the message.
  6. ·        Identify the recipient.
  7. ·        Send the e-mail.

Table A

Option Explanation
HTML Sends the selected object as a Web-ready file.
Text Sends the selected object as a text file
(in tabular format).
Microsoft Active Server Pages Sends the select object as an .asp file.
Excel Sends the selected object in Excel format.
Microsoft IIS Sends the selected object as an ICD or HTX file.
Rich Text Format Sends the selected object as an .rtf file.
Data Access Page Sends the selected object as a data access page.
XML Sends the selected object as an .xml file.

Sharing information programmatically
Sending e-mail via the interface is a simple enough process. However, that doesn’t mean you’ll want to give that capability, unchecked, to your users. If you limit access to the menus or you just want to guide users through the process, automate the process using Visual Basic for Application’s (VBA’s) SendObject method.

The SendObject is a method of the DoCmd object and accepts a number of arguments—all are optional, although you must specify at least one—in the form:
DoCmd.SendObject objecttype, objectname, outputformat, to, cc, bcc, subject, messagetext, editmessage, templatefile

Table B defines the SendObject method’s many arguments.
Table B

Argument Description

objecttype
 		One of many constants that define the type of object you’re sending:
acSendTable
acSendQuery
acSendForm
acSendReport
acSendModule
acSendDataAccessPage
acSendNoObject (the default)

objectname
 		The name of the object you’re sending.

outputformat
 		One of many constants that define the object’s format:
acFormatDAP
acFormatHTML
acFormatRTF
acFormatText
acFormatXLS
These constants represent the formats listed in Table A.

to
 		Identifies the recipient or can be a list of recipients. If you enter a literal value, it must be an actual e-mail address or address book entry. Use the semicolon character (;) to separate multiple recipients.

cc
 		Same as to, but sent as a copy to recipient.

bcc
 		Same as to, but sent as a blind copy to recipient.

subject
 		Text that comprises the message’s subject line.

messagetext
 		Text that comprises the message’s main body.

editmessage
 		Determines whether the message is opened for editing purposes or sent immediately. Set to True if you want the default e-mail client to display the message. Set to False if you want the e-mail client to send message immediately with no further input from the user.

templatefile
 		The name (including path) of an HTML template, used with the acFormatHTML constant.

Most of the arguments are self-explanatory, but the acSendNoObject constant is noteworthy. You’ll use this constant when you don’t want to attach an object. That means you can send a text message from inside Access, even if you’re not mailing a report or data. You probably won’t need to use this option often, but it’s there if you need it.

An example
The form shown in Figure A uses a list box control to display all the reports in the Northwind sample database that comes with Access. Simply select a report, and then click the Mail Report command button to e-mail a report.

Figure A
This sample form will e-mail an Access object via the system’s default e-mail client.

To create the form, open a blank form and add a list box and a command button. Table C lists the properties for the form and both controls. Save the form as frmSendMail. After adding the two controls, click the Code button on the Form Design toolbar to open the form’s module and enter the two event procedures shown in Listing A and Listing B. The Form’s load event populates the list box, while the command button executes the SendObject method.
Table C

Control Property Setting
form Scroll Bars Neither
Record Selectors No
Navigation Buttons No
Dividing Lines No
list box Name lstObject
command button Name cmdSendMail
Caption Mail Report

 

 

Using the form
Now you’re ready to actually send some mail. To do so, select a report, such as Catalog, from the list control and click the Mail Report button. Doing so displays the New Message dialog box shown in Figure B. Notice that the report is listed as an attachment. Your window may not resemble ours exactly, and that’s all right. Remember, Access is using your default mail client.

Figure B
The SendObject method opens the client e-mail application’s New Message window.

You’ll notice that many of the fields are blank. Your user must supply a lot of information before sending the mail: the recipient(s) and any subject and message text. If you’d like to provide some of that information for the user, you can do so by passing the appropriate values to the SendObject method.

To test the form, enter your own e-mail address and send it. If you’re not online at the moment, don’t worry. Your mail client will store the message and its attachments until you connect the next time.

Enhancements
I specified as little information as possible in my example on purpose—so you can focus on the SendObject method and not a fancy form that does everything for the user. You could easily solicit more information from your user and pass that input to the SendObject method, reducing the amount of information, and access, the user has to the mail message itself. Or, you could enter the information yourself as literal values in the SendObject statement.

In addition, my example doesn’t contain any error handling, but you’ll certainly want to capture errors. Just a few to prepare for are:

  • ·        The error produced when the object named doesn’t exist.
  • ·        The error produced when the object type is wrong for the object named.
  • ·        The error produced when the SendObject task is cancelled unexpectedly.
  • ·        The error produced when an object’s data source is missing.
  • ·        The error produced when your user enters an object that can’t be mailed (macro).

Try to avoid allowing users to enter an e-mail address manually. There’s a lot of room for error since the entry must follow an exact format and, of course, be a valid e-mail address. You must include code that checks such addresses or you must limit the user to choosing from the e-mail client’s address book.

Mailing Snapshot files
Finally, if you’re in the habit of sending Access reports via e-mail, you probably know about Snapshot Viewer, which comes with Access 2000 and later (Access 97 users will need to download and install the program). This program saves an Access report as a snapshot (.snp) file that looks virtually identical to the original report.

If you have Snapshot Viewer installed, you can specify the snapshot format constant, acFormatSNP, in the SendObject’s outputformat argument. For instance, our previous example would use the statement:
DoCmd.SendObject acSendReport, strReport, _
      acFormatSNP, , , , , , True

Just remember that the report you reference in the objectname argument should be a valid Snapshot file (ending with the .snp extension).

When the report exists in Access format, but not Snapshot, you can save yourself a few steps by exporting the Access report using the SnapshotFormat(*.snp) format in the form:
DoCmd.SendObject acSendReport, strReport, _
      “SnapshotFormat(*.snp)”, , , , , , True

The recipient doesn’t need Access to view a .snp format file, but the recipient will need Snapshot Viewer, which can be downloaded from the Microsoft Knowledge Base article 175274.