Learn VBA Code to Send Outlook Emails

I’ve noticed that the question of how to e-mail-enable your VB (and VBA) applications comes up quite frequently on the newsgroups. A recent check at comp.databases.ms-access indicated that, on the average, this topic gets one question per day.

Code to automate Outlook isn’t that complicated, but for those who have never explored the Microsoft Outlook object model, it can be kind of confusing. The fact that this question reoccurs so frequently suggests that the programming community wouldn’t be offended by another public snippet of “how to send an e-mail” code.

The script below can simply be cut and pasted into a VB or VBA module (or class if you are comfortable using classes). It was originally written for a Microsoft Access application, so it’s compliant with Visual Basic for Applications.

The process is simple:

1. Check to see if the user already has Outlook open.
2. If so, set an Outlook object to the open application.
3. If Outlook is not open, then open it and set your object pointer.
4. Set a MAPI Namespace object from the Outlook Application object.
5. Collect e-mail field info from the user and validate it.
6. Create a mail item and set its properties.
7. Invoke the Send() method.
8. Test for errors and clean-up.

The question of how to e-mail-enable your VB (and VBA) applications comes up quite frequently. Code to automate Outlook isn’t that complicated, but for those who have never explored the Microsoft Outlook object model, it can be rather confusing. In his latest article, Danny Lesandrini offers tips and a script to help those struggling with this issue.

‘ — Begin Code Here —

Option Compare Database
Option Explicit

‘ Declare module level variables

Dim mOutlookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim mFolder As MAPIFolder
Dim mItem As MailItem
Dim fSuccess As Boolean

‘ Module contains only 2 methods: ‘ 1) GetOutlook() ‘ 2) SendMessage() ‘

Private Function GetOutlook() As Boolean

‘ The GetOutlook() function sets the Outlook Application ‘ and Namespase objects and opens MS Outlook

On Error Resume Next

‘ Assume success

fSuccess = True

Set mOutlookApp = GetObject("", "Outlook.application")

‘ If Outlook is NOT Open, then there will be an error. ‘ Attempt to open Outlook

If Err.Number > 0 Then
    Err.clear
    Set mOutlookApp = CreateObject("Outlook.application")
        
    If Err.Number > 0 Then
        MsgBox "Could not create Outlook object", vbCritical
        fSuccess = False
        Exit Function
    End If
End If

‘ If we’ve made it this far, we have an Outlook App Object ‘ Now, set the NameSpace object to MAPI Namespace

Set mNameSpace = mOutlookApp.GetNamespace("MAPI")
    
If Err.Number > 0 Then
    MsgBox "Could not create NameSpace object", vbCritical
    fSuccess = False
    Exit Function
End If

‘ Return the Success Flag as the value of GetOutlook()

GetOutlook = fSuccess
    
End Function


Public Function SendMessage() As Boolean

‘ The SendMessage() function reads user entered values and ‘ actually sends the message.

On Error Resume Next

Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String

strSubject = Me!txtSubject
strRecip = Me!txtRecipient
strMsg = Me!txtBody
strAttachment = Me!txtAttachment

‘ Any amount of validation could be done at this point, but ‘ at a minimum, you need to verify that the user supplied an ‘ Email address for a recipient.

If Len(strRecip) = 0 Then
    strMsg = "You must designate a recipient."
    MsgBox strMsg, vbExclamation, "Error"
    Exit Function
End If

‘ Assume success

fSuccess = True

‘ Here’s where the real Outlook Automation takes place

If GetOutlook = True Then
    Set mItem = mOutlookApp.CreateItem(olMailItem)
    mItem.Recipients.Add strRecip
    mItem.Subject = strSubject
    mItem.Body = strMsg
    
    

‘ This code allows for 1 attachment, but with slight ‘ modification, you could provide for multiple files.

    If Len(strAttachment) > 0 Then
        mItem.Attachments.Add strAttachment
    End If
    
    mItem.Save
    mItem.Send
End If

‘ Release resources

Set mOutlookApp = Nothing
Set mNameSpace  = Nothing

If Err.Number > 0 Then fSuccess = False
SendMessage = fSuccess

End Function

‘ — End Code Here –>

Once you get started playing with the Microsoft Outlook object model, you will find some really cool things you can do. I’ve got one application that runs all day long on my computer at work, monitoring my Exchange Inbox for new messages and approaching appointments. Instead of just informing me that “I’ve got mail”, as Outlook does, this utility actually pops up the message. While that might be considered irritating to some, I really like to get my messages on a timely basis and I hate keeping Outlook open. If there’s any interest in such a utility, maybe I’ll make that the subject of a future article.

Danny Lesandrini