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:
- Check to see if the user already has Outlook open.
- If so, set an Outlook object to the open application.
- If Outlook is not open, then open it and set your object pointer.
- Set a MAPI Namespace object from the Outlook Application object.
- Collect e-mail field info from the user and validate it.
- Create a mail item and set its properties.
- Invoke the Send() method.
- Test for errors and clean-up.
' -- 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.