Sending an e-mail from Access

For a long time now, there has been a very easy way to send e-mails from within Access - just use the DoCmd.SendObject command without specifying an object to send! This worked (note the past tense) fine if you just wanted to send basic e-mails using your default, MAPI-compliant mail software.

However, with the introduction of Access 2000, this solution became very buggy - not only would it not always work, sometimes it would fail without any indication which could be a bit of a problem if you were sending mails without editing them first. For full details of the spurious nature of this bug, check out this Microsoft KnowledgeBase article. And bless 'em, our friends at Redmond even went so far as to post a (pretty rudimentary) alternative piece of code, which completists can read here.

Game over - you don't need to read any further, right? Wrong! As I've said, the alternative code supplied by Microsoft is fairly basic but, to be fair, it did form my starting point for the following piece of code. Basically, my e-mail function, posted here for your delectation, uses Outlook to not only send an e-mail but to do so specifying all the following parameters:

• The sender - handy if you have more than one Service set up in Outlook
• The recipient(s) - addressee(s), CC and BCC
• The subject
• The message text
• Attachment path
• Voting options
• Urgency
• Edit before sending - true or false

The sender, voting, urgency and attachment path are all features not unsupported by the old SendObject method of e-mailing, so in many ways the Access 2000 bug has been beneficial.

To use this code, you must first set a reference to the Outlook Object Library (in Access 2000 you can do this from the Tools, References menu option in the Visual Basic Editor) - the file you're looking to reference is an OLB file, e.g. msoutl9.olb for Outlook 2000.

Here's an example of how to call this function. In this example, an e-mail will be sent to Jo Smith and Estelle Jones, CC'ing Bob Downes, with a short subject and title, and an attachment. The message will be sent with high priority, and Yes/No/Maybe voting buttons. Finally, we'll edit the message before it's sent.

fctnOutlook , "Jo Smith;Estelle Jones", "Bob Downes", , "My subject here", "My message here", "c:\temp\att.txt", "Yes;No;Maybe", 2, True

Note that multiple names in the same parameter are separated by semi-colons, as are the options in the voting buttons string.

Finally, you'll see that when you use this code you get prompted to confirm that it's okay for your program to send an e-mail using Outlook. This is a virus-protection method introduced by Microsoft (read their white paper on this for more info) to prevent script-kiddies writing malicious code to send self-propagating mails to everyone in your address book. Using straight Outlook this is largely unavoidable, so you'll have to get used to clicking the “Yes” button (unless you want to consider Outlook Redemption as a freeware solution to this inconvenience, although that means getting away from straight Outlook). At least you get an option to allow your program access to Outlook for up to 10 minutes, which can be handy if you're about to generate a whole load of e-mails. Anyway, here's the code for you to cut and paste.

Function fctnOutlook(Optional FromAddr, Optional Addr, Optional CC, Optional BCC, _
Optional Subject, Optional MessageText, Optional AttachmentPath, Optional Vote As String = vbNullString, _
Optional Urgency As Byte = 1, Optional EditMessage As Boolean = True)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
    If Not IsMissing(FromAddr) Then
        .SentOnBehalfOfName = FromAddr
    End If

    If Not IsMissing(Addr) Then
        Set objOutlookRecip = .Recipients.Add(Addr)
        objOutlookRecip.Type = olTo
    End If

    If Not IsMissing(CC) Then
        Set objOutlookRecip = .Recipients.Add(CC)
        objOutlookRecip.Type = olCC
    End If

    If Not IsMissing(BCC) Then
        Set objOutlookRecip = .Recipients.Add(BCC)
        objOutlookRecip.Type = olBCC
    End If

    If Not IsMissing(Subject) Then
        .Subject = Subject
    End If

    If Not IsMissing(MessageText) Then
        .Body = MessageText
    End If

    If Not IsMissing(AttachmentPath) Then
        'Check file exists before attaching!
        If Len(Dir(AttachmentPath)) > 0 Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        Else
            MsgBox "Attachment not found.", vbExclamation
        End If
    End If

    If IsNull(Vote) = False Then
        .VotingOptions = Vote
    End If

    Select Case Urgency
    Case 2
        .Importance = olImportanceHigh
    Case 0
        .Importance = olImportanceLow
    Case Else
        .Importance = olImportanceNormal
    End Select

    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

    If EditMessage Then
        .Display
    Else
        .Save
        .Send
    End If
End With

Set objOutlook = Nothing

End Function