Access & Email

With internet access possible almost everywhere nowadays, the requirements for database applications quite frequently include the requirement to provide interfaces to internet services.

While requirements for direct connections to webservers via HTTP or FTP are increasing, but not yet ubiquitous, a simple interface to send emails from within an application can be considered fairly standard now.

There are lots of different approaches to send email messages from an application. On this web page I want to try to provide an overview over different options for a developer to integrate the functionality to send email messages from Microsoft Access applications. This will include the advantages and dis advantages of the different approaches.

A very simple but still effective option to send an email from a Microsoft Access application is the DoCmd.SendObject method. This method will generate an email using the default email client with a single line of VBA code right from Access. You can either display the email for further editing or put it right into the default mail client’s outbox for sending.

You can add multiple recipients to the “To”-, “CC”- or “BCC”-Headers of the email by using a string containing several email addresses separated by semicolons.

A simple example:

Sending Access-Objects

You can add most Access-Objects from your Access database as an attachment to the email. Access will not use its own internal format when attaching the object to the email, but they will be exported to more widely used file formats.

While you can send the UI-Access-Objects (Forms, Reports) with the email as an attachment, I would not recommended doing this prior to Access 2010. In earlier Version these objects could be sent in Plain-Text, Rich-Text- and HTML-Format. The visual layout of the Access objects is almost completely lost in the process. In Access 2010 onward you can use the PDF-Format for those objects too. This is suitable to send appealing reports with your email.

In Access 2007 this should be possible as well with the Save as PDF Add-In. - I have never tried that though.

Example:

Sending data

If you want to send data (Tables or Queries) as plain text, they will be exported in a textual representation that tries to mimic the visual appearance of the tabular data. This format is unusable as data exchange format. Only attaching a table or query in Microsoft Excel file format is suitable to send data for further computerized processing with the SendObject-Method. The classic .xls-Format is supported as well as, since Access 2007, the newer.xlsx (XML) and .xlsb (Binary) formats.

Example:

Implementation with a Macro

If you are not proficient in VBA and prefer to use macros rather than code, you can use this approach to send email from Access as well. The SendObject-Action is available as a macro command as well and has all the same options. This macro action has been renamed to EmailDatabaseObject in Access 2010.

Requirements and (dis-) advantages

This approach requires a MAPI (Messaging Application Programming Interface) compatible email client installed on the computer the email should be send on.

Advantages

• Very easy implementation. Only one line of code is required.
• Only very little dependence on other software (email-client)

Disadvantages

• The body text of the email is limited to 255 characters. Only plain text format can be used for the email body.
• If the email should be send directly without further editing in the mail client, some clients will pop up a security warning that has to be confirmed manually.
• Only one single Access-Object can be attached to the email. It is not possible to send files from the files system.

Known issues

Note: In Access 2000 there was a bug that restricted the use of the SendObject method severely. The method will fail if it is called repeatedly or if you try to pass a string longer than 70 characters for the email body to the method. There is the article Q260819 in the Microsoft Knowledgebase, which describes the problem in detail.

Another quite common way to integrate email-functionality in an Access-Application is to use automation of Microsoft Outlook via the MS Outlook object library.

The following code snippet illustrates how simple this can be done for the purpose of sending an single plain text email with an added attachment.

Public Sub sendMail()

    Dim myMail      As Outlook.MailItem
    Dim myOutlApp   As Outlook.Application

    ' Creating an Outlook-Instance and a new Mailitem
    Set myOutlApp = New Outlook.Application
    Set myMail = myOutlApp.CreateItem(olMailItem)

    With myMail
        ' defining the primary recipient
        .To = "recipient@somewhere.invalid"
        ' adding a CC-recipient
        .CC = "other.recipient@somewhere.else.invalid"
        ' defining a subject for the mail
        .Subject = "My first mail sent with Outlook-Automation"
        ' Addimg some body-text to the mail
        .Body = "Hello dear friend, " & vbCrLf & vbCrLf & _
                "This is my first mail produced and sent via Outlook-Automation." & vbCrLf & vbCrLf & _
                "And now I will try add an attachment."
        ' Adding an attachment from filesystem
        .Attachments.Add "c:\path\to\a\file.dat"

        ' sending the mail
        .Send
        ' You can as well display the generated mail by calling the Display-Method
        ' of the Mailitem and let the user send it manually later. 
    End With

    ' terminating the Outlook-Application instance
    myOutlApp.Quit

    ' Destroy the object variables and free the memory
    Set myMail = Nothing
    Set myOutlApp = Nothing

End Sub

To make this sample work, you'll have to add a reference to the Outlook object library.

This way of email integration is very easy to use and offers the developer almost full control over every common property of the mail that is to be created without having to bother about mail-configuration settings on of the target environment of his application.

Obviously this approach has a tremendous limitation. It requires Microsoft Outlook to be installed on every workstation where the email functionality is intended to be used. - Before you ask: No, Microsoft Outlook Express will not work!

Furthermore, with quite similar code as the sample above, many malicious internet-worms and viruses have spread through the web and wreaked havoc upon many naive and uncautious users' data. To prevent this happening again, Microsoft has reduced the automation features of MS Outlook with updates and security fixes to a point near uselessness. With these "fixes" installed the users has to confirm any access to his Outlook address book and any attempt to send email by an automation process. Once confirmed that process may access these auotmation features for a short period of time.

Back to the TOC

Here is an extensive article describing how to create an HTML email with CSS formatting and inline images from within your Access application using VBA and Outlook automation.

Another option to send emails from your Access application is using the CDO library (Collaboration Data Objects). Here is a link to a longer article on how to send emails with VBA and the CDO Library.

If you send an email with VBA and Outlook usually the account configured as standard in Outlook will be used. Sometimes though you need to send an email with a specific sender account.

For this end the Outlook Mailitem object has the property SendUsingAccount. You can set this property to a specific Outlook Account to define the sender account. The easiest way to identify an account in Outlook is by its sender email address.

It is important to understand that this approach does not simply set the sender address of the email. It only uses the email to set the account. So all the other settings configured in that account, like mail server and local storage folder, will be used too for sending the email. And of course it requires that an account for this email address has been configured in Outlook previously.

This small function shows how to find an Outlook Account by using the sender email address.

Now you can combine that function with the code to actually send the email. A sample to send an email with a specific account is this procedure.

This procedure is a shortened excerpt from my example Sending an email with Outlook automation, which you can find on this page too.

If you have got some experience with VBA programming, you should notice that in this example the line to set the account is not

as it usually should be. In this line there is an object reference being set, which requires the keyword Set. I do not understand myself, why you must not use the set keyword here (it would cause a runtime error).

Another very simple, but limited, solution to create emails in an Access Application is to invoke a mailto link with the FollowHyperlink method. Although the options are fairly limited, it works even with no MAPI compatible mail client installed.

Adding Follow-Up information for the recipient of an email and creating a task for the sender can improve the workflow connected to automated emails significantly. This article demonstrates how to set email flags and reminders with VBA-Code.

A detailed start to finish tutorial on how to send a serial email from your database. Beginning with storing email addresses in a table. Then querying them in VBA, concatenating custom, individual email texts and finally creating the emails and sending them off through your Outlook account.

Recursion is a powerful programming technique. In this article I explain the general, basic concept of recursion and show how recursion is implemented in VBA by the example of reading the Microsoft Outlook folder hierarchy. The code was written in Microsoft Access, but it will work all the same in any VBA enabled application.

Showing the Outlook main window from COM automation with VBA is not as trivial, as one would think. So, I figured out the gotchas for you and wrote a text with code samples to show how to display and maximize an Outlook main Window.

Encryption is a key feature of modern communication on the internet. All the more for email communication, as it involves sensitive credential data. The CDO library is a commonly used component in implementing email capabilties in VBA programming. When combining the two, there are some potential problems the documentation does not address sufficiently. So, I wrote a slighly long writeup of my quest to unravel the mystery, rumours and contradictions of using Opportunistic TLS (STARTTLS) in the CDO library.