Language: Deutsch English















Last Update: 2017 - 07 - 09





Access & Email

Table of Contents

  • Introduction
  • How to send an email with the DoCmd.SendObject method or the EmailDatabaseObject macro action
  • Create an email with Outlook-Automation
  • How to create HTML-emails with Microsoft Access and Outlook automation
  • Send an email with VBA and Outlook using a specific sender account
  • Sending emails with VBA and the CDO Library
  • Create email with a mailto hyperlink
  • How to send Outlook Emails with Flag and Reminder in VBA
  • How to change the RecordSource of an Access 2010 Form in PivotChart-View with VBA
  • Send serial email with Microsoft Access, VBA and Outlook



  • Introduction

    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.



    Back to the TOC

    How to send an email with the DoCmd.SendObject method or the EmailDatabaseObject macro action

    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:

    DoCmd.SendObject acSendNoObject, , , "someone@somewhere.invalid; someone.else@elsewhere.invalid", , , _ "Email without attachment", "This is the email body.", True

    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:

    DoCmd.SendObject acSendReport, "myReport", acFormatPDF, "someone@somewhere.invalid", , , _ "Email with attached report", "This is the email body.", True

    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:

    DoCmd.SendObject acSendQuery, "eineAbfrage", acFormatXLS, "someone@somewhere.invalid", , , _ "Email with attached query in Excel format", "This is the email body.", True

    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. Screenshot of the EmailDatabaseObject Macro-Action

    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.



    Back to the TOC

    Create an email with Outlook-Automation

    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


    How to create HTML-emails with Microsoft Access and Outlook automation

    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.



    Back to the TOC

    Send an email with VBA and Outlook using a specific sender account

    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.

    Public Function GetAccountByEmail(ByRef outlApp As Outlook.Application, ByVal strSenderEmail As String) As Outlook.Account Dim acc As Outlook.Account Dim retVal As Outlook.Account For Each acc In outlApp.Session.Accounts If acc.SmtpAddress = strSenderEmail Then Set retVal = acc Exit For End If Next acc Set GetAccountByEmail = retVal End Function

    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.

    Public Sub SendMail() Dim myMail As Outlook.MailItem Dim myOutlApp As Outlook.Application ' Create an new Outlook instance and an mailitem Set myOutlApp = New Outlook.Application Set myMail = myOutlApp.CreateItem(olMailItem) With myMail ' Here the sender account is being set. myMail.SendUsingAccount = GetAccountByEmail(myOutlApp, "phil@codekabinett.com") ' Please note: Although we are setting an object reference, we do not use the Set keyword ' I do not knwo why though. ' Set the recipient .To = "recipient@somewhere.invalid" ' Define subject and text here ' Send the email .Send End With ' End the Outlook instance myOutlApp.Quit ' Destroy object references and free memory Set myMail = Nothing Set myOutlApp = Nothing End Sub

    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

    Set myMail.SendUsingAccount = GetAccountByEmail(myOutlApp, "phil@codekabinett.com")

    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).



    Back to the TOC

    Sending emails with VBA and the CDO Library

    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.



    Back to the TOC

    Create email with a mailto hyperlink

    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.



    Back to the TOC

    How to send Outlook Emails with Flag and Reminder in VBA

    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.



    Back to the TOC

    How to change the RecordSource of an Access 2010 Form in PivotChart-View with VBA

    Short introduction into how to programm an Access 2010 Form in PivotChart-View with VBA and particularly how to change the RecordSource at runtime with VBA.



    Back to the TOC

    Send serial email with Microsoft Access, VBA and Outlook

    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.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.



    © 1999 - 2017 by Philipp Stiefel