Sending E-Mails via Outlook - One Developer's Approach

[twinnyfo]

When using MS Access to manage data, it is often helpful to use the automation features of Access to generate e-mails and send them to your customers. If you happen to be sending an Access Object (like an MS Access-generated Report), you can use the built-in .SendObject Method--but many times, we just want (or need) to generate related correspondence, based upon the work you are doing in the Database.

Below, I will describe the method I use for sending e-mail via MS Outlook. Since that is the e-mail application we use at work, and since it is a standard desktop application for many of us, this is a great place to start. If you use a different e-mail application (not a web-mail service) there may be ways to modify this article's code in order to accommodate, but my focus will be on MS Outlook as the e-mail application.

First, it is possible to add lines of code that generate an e-mail to all your Forms, add attachments as necessary and then properly format things all nice and neat. However, if we do this, we find ourselves "re-inventing the wheel" every time we need to send an e-mail. From a developer's perspective, this is a waste of bits and bytes. A "better" approach may be to standardize the e-mail-sending procedure in a globally accessible Function which does all that work for you. Then, all you have to do is call that Funciton, sending the necessary arguments to it.

Here's how we do it:

First, in a separate, standalone VBA Module, create your e-mail function:

Option Compare Database
Option Explicit

Public Function SendAnEMail(olSendTo As String, _
                            olSubject As String, _
                            olEMailBody As String, _
                            olDisplay As Boolean, _
                   Optional olCCLine As String, _
                   Optional olBCCLine As String, _
                   Optional olOnBehalfOf As String, _
                   Optional olAtchs As String, _
                   Optional SendAsHTML As Boolean) As Boolean
On Error GoTo EH
    Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim strArray()  As String
    Dim intAtch     As Integer

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
        .To = olSendTo
        .Subject = olSubject

        If SendAsHTML Then
            .BodyFormat = olFormatHTML
            .HTMLBody = olEMailBody
        Else
            .Body = olEMailBody
        End If

        .CC = olCCLine
        .BCC = olBCCLine
        .SentOnBehalfOfName = olOnBehalfOf
        strArray = Split(olAtchs, "%Atch")

        For intAtch = 0 To UBound(strArray)
            If FileExists(strArray(intAtch)) Then _
                .Attachments.Add strArray(intAtch)
        Next intAtch

        If olDisplay Then
            .Display
        Else
            .Send
        End If

    End With
    Set olMail = Nothing
    Set olApp = Nothing

    SendAnEMail = True

    Exit Function
EH:
    MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    SendAnEMail = False
    Exit Function
End Function

You will notice that it is not very complicated. We are just creating an e-mail in the same way that you would anywhere else. However, now this function is accessible from anywhere in your project and all you need to do is call it in order to create the e-mail. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.

You may notice that there is one (and only one) argument for attachments. However, you can add multiple attachments from the calling code. Just keep in mind that you need to separate each attached file with the text "%Atch".

You may also notice a reference to another public function named FileExists(). This ensures you are not trying to attach a file that doesn't exist. I think there are about as many versions of such a function as there are programmers, but here is my version (that I originally stole from Allen Browne--props where props are due).

Public Function FileExists(ByVal strFile As String, _
                           Optional bFindFolders As Boolean) _
                           As Boolean
'Purpose:   Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched if
'                    no path included.
'           bFindFolders: If strFile is a folder, FileExists() returns False
'                         unless this argument is True.
'Note:      Does not look inside subdirectories for the file.
'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    If Not (IsNull(strFile) Or strFile = "") Then
        'Include read-only files, hidden files, system files.
        lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

        If bFindFolders Then
            'Include folders as well.
            lngAttributes = (lngAttributes Or vbDirectory)
        Else
            'Strip any trailing slash, so Dir does not look inside the folder.
            Do While Right$(strFile, 1) = "\"
                strFile = Left$(strFile, Len(strFile) - 1)
            Loop
        End If
        'If Dir() returns something, the file exists.
        On Error Resume Next
        FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
    Else
        FileExists = False
    End If

End Function

This function can go in the same module, if you wish.

Now, you are ready to "Rock 'n' Roll"!

In your Form, if you want to send an e-mail via clicking a button, here is all you have to do:

Private Sub SendMail_Click()
On Error GoTo EH
    Dim strSendTo     As String
    Dim strSubject    As String
    Dim strEMailBody  As String
    Dim strCCLine     As String
    Dim strBCCLine    As String
    Dim strOnBehalfOf As String
    Dim strAtchs      As String

    strSendTo = "Orders@PizzaGuy.biz"
    strSubject = "I Want a Pizza"
    strEMailBody = "I want a pizza <B>NOW</B>!!!"
    strCCLine = "MyBuddy@email.net"
    strBCCLine = "MyEnemy@email.net"
    strOnBehalfOf = "CEO@BigBusiness.org"
    strAtchs = "C:\File.pdf" & _
               "%Atch" & _
               "C:\Another File.pdf"

    'Generate and Display the E-Mail
    Call SendAnEMail(olSendTo:=strsendto, _
                     olSubject:=strSubject, _
                     olEMailBody:=strEMailBody, _
                     olDisplay:=True, _
                     olCCLine:=strCCLine, _
                     olBCCLine:=strBCCLine, _
                     olOnBehalfOf:=strOnBehalfOf, _
                     olAtchs:=strAtchs, _
                     SendAsHTML:=True)

    Exit Sub
EH:
    MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub

Obviously, the "Send of Behalf of" feature will only work if you are on a MS Exchange server that allows such things. Additionally, many corporate e-mail servers don't allow for sending directly from outside of Outlook; hence, I just default to displaying the e-mail.

And.....

That's about it. It makes sending e-mail a breeze--especially in our office, in which we send out approximately 1,000 various e-mails for each of our six annual projects. Yeah, this saves me a ton of programming time.

If any o' all y'all have any recommended improvements, I am all ears!

hope this hepps!

[zmbd]

. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.

One can also use late-binding to avoid setting the reference. This is most useful when distributing your application to other clients or when upgrading from one version of Office to another (or downgrading).

For a further discussion on Application Automation:
home > topics > microsoft access / vba > insights > application automation

[ADezii]

zmbd brings up a good point regarding Late Binding, but why not have the best of both worlds by using Runtime Conditional Compilation Constants?

#Const conEARLY_BIND = True

Public Function SendAnEMail(olSendTo As String, _
                             olSubject As String, _
                             olEMailBody As String, _
                             olDisplay As Boolean, _
                    Optional olCCLine As String, _
                    Optional olBCCLine As String, _
                    Optional olOnBehalfOf As String, _
                    Optional olAtchs As String, _
                    Optional SendAsHTML As Boolean) As Boolean
On Error GoTo EH
Dim strArray()  As String
Dim intAtch     As Integer
Const conEMAIL_ITEM = 0     'If using Late Binding cannot use olMailItem Intrinsic Constant

#If conEARLY_BIND Then
  Dim olApp As Outlook.Application
  Set olApp = New Outlook.Application
  Dim olMail As Outlook.MailItem
  Set olMail = olApp.CreateItem(olMailItem)
#Else
  Dim olApp  As Object
  Dim olMail As Object
  Set olApp = CreateObject("Outlook.Application")
  Set olMail = olApp.CreateItem(0)
#End If
'************************ CODE HAS INTENTIONALLY BEEN OMITTED BELOW ************************

[DJRhino1175]

I like this approach, the only other upgrade would be to have it pull the email address from a table so you can send to more than one person with out listing them all in the code itself. I have this in one of my database that I put together from the help in the group of awesome programmers. I will post the code if anyone would like to see it tomorrow.

[twinnyfo]

DJ,

Keep in mind that this procedure simply shows the execution of the process. So, it doesn’t matter how you generate the string for the addressees. As long as you build the strings properly, you can send an e-mail from anywhere at any time. Your list of addressees can be quite large, too, as long as each addressee is separated by a semi colon (“;”)—this is my default in Outlook.

The heart of this procedure is to allow you to simply build strings and send an e-mail using a User-Defined procedure, rather than re-creating the wheel every time you have to send a message. This cuts down on the clutter of your projects and allows you to focus more time on development.

Thanks for the input!