Electronic Mail

team lib

Electronic mail is ubiquitous nowadays. Everyone has an e-mail account. OK, that's a slight exaggeration. My Mom doesn't have e-mail yet. Or even a computer, actually. My brother builds electrical things. I program them. And Mom looks on in that way that only parents can, saying 'Yes dear, very nice. Pour me another whiskey, would you please ?'

Not only has e-mail brought the world closer, it has opened up lines of communication. No longer do you have to print off a twenty-page report and fax it to your office in Outer Elbonia, only to find out they've run out of fax paper. You can simply pick names from an electronic address book, and with a single click your report is on its way. Having data quicker means decisions can be made in a more timely fashion. Let's face it: it's just less effort all around.

The e-mail facilities in Access are not specific to any e-mail software. I'll show you samples with Microsoft Outlook, because that's what I use, but other packages should work just as well.

To send e-mail you use the SendObject method:

 DoCmd.SendObject [ObjectType], [ObjectName], [OutputFormat],  [To], [CC], [BCC], [Subject],  [MessageText], [EditMessage], [TemplateFile] 

You can probably guess what some of these arguments are, but let's look at them in more detail:




The type of object you wish to send. It must be one of:

acSendDataAccessPage acSendForm

acSendModule acSendNoObject

acSendQuery acSendReport


If this argument is omitted, acSendNoObject is used, which just sends mail, without attaching any objects.


The name of the object you wish to send.


The format the object is to be sent in. It must be one of:

acFormatDAP acFormatHTML

acFormatRTF acFormatText


You will be prompted for a format if you leave this argument blank.


The recipient name, or list of recipient names, to whom the mail should be sent. You will be prompted for names if you leave this argument blank. To include multiple recipients, you just separate their names by a semi- colon . This name should be a valid address book entry, or the actual email address.


The recipient name, or list of recipient names, to whom the mail should be CC'd.


The recipient name, or list of recipient names, to whom the mail should be BCC'd.


The text that comprises the subject line of the message.


The text that comprises the main body of the message.


Set this to True , which is the default, to open your mail application and allow editing of the message before it's sent. Set this to False to send the message straight away.


The full name (including the path ), of an HTML template file, to be used when sending HTML files.

Let's see this in action.


It's important to note that the following code will only work if you have an e-mail program installed on your computer. You don't actually have to have it connected to anything, as long as it is installed and set up to send mail. While writing this, I installed Outlook 2002 and set up a profile, even though I didn't have e-mail on the test machine. Just follow the installation instructions for installing Outlook, and then follow the wizard to set up a service provider. It doesn't matter what you put into the wizard fields, because you're not actually going to be sending mail anyway.

Try It Out-Sending Mail

  1. Open the import and export form in design view and add another button. Name this cmdEmailPriceList , and caption it Email Price List.

  2. Add the following code to the Click event:

       DoCmd.SendObject acSendTable, "tblIceCream", acFormatXLS, _     "Janine Lloyd", "Karen Wake; Jane Donnelly", _     "IceCreamLovers", "Latest Prices", _     "Hot off the press - our latest price list."   

    If you want to really send mail, you should change the names here to some of your own contacts.

  3. Back in Access, switch to Form view and press the e-mail button. Since we left out the EditMessage argument, the default is to show the message before sending:

    click to expand

Notice how the names in the three address fields match those in our code? Also notice that because we specified acFormatXLS the table has been turned into an Excel spreadsheet before being attached to the mail message. If we had set the EditMessage argument to False the message would have been delivered without any user interaction. It's a simple as that. You'll be seeing other ways to send mail in a later chapter.


NOTE: It's important to realize that your application could actually send mail without the user being aware of it. You may consider this a security risk. Depending on the version of Outlook the user may see the following dialog:

click to expand
team lib

Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net