Did You Forget Something?
Or, How to Get Outlook To Remind You to Insert a Forgotten Attachment
Introduction
Your boss wants that report by the end of the day. It’s five minutes to five, and you’ve just put on the finishing touches. You draft an email to the boss, saying, “As per your request, please see the attached report.” But in your haste, you forget to attach the report, and you hit Send! How foolish do you feel? Now you have to send a second email that says something like, “Oops! Here’s the attachment.” I’m not going to ask if this has ever happened to you, but how many times this has happened to you. Maybe if you’re using Exchange Server, you can Recall and/or Resend the message before the boss sees it. Otherwise, you’re stuck sending that “Oops” message. Not that you should be too embarrassed, because it does happen to everyone, but wouldn’t it be great if you can avoid having it happen to you, and learn some Outlook VBA programming along the way? Let’s get started!
Getting to the Outlook VBE
From within Outlook, select Tools / Macro / Visual Basic Editor, or simply hit Alt-F11. Simple, eh?
When you first enter the VBE, you’ll be looking at the default project, which is called VbaProject.OTM. You should see something that looks like the figure below. Please note we’ll be using Outlook 2002/XP for this article. There may be some (hopefully) slight differences in other versions of Outlook.
Figure 1 – Visual Basic Editor showing Project1 (VbaProject.OTM) with ThisOutlookSession module opened.
Locating VbaProject.OTM and the APPDATA Environment Variable
Not that you necessarily need to know this, but the VbaProject.OTM file is located in the Application Data\Microsoft\Outlook folder within the user’s profile folder. On my machine, the entire path is “C:\Windows\Profiles\David\Application Data\Microsoft\Outlook\VbaProject.OTM”. On others it may be “C:\Documents and Settings\\Application Data\Microsoft\Outlook\VbaProject.OTM”. To locate the “Application Data” folder for the current user, you can refer to the APPDATA environment variable. You can do this from a Command Prompt, or even from within Windows Explorer. In the Command Prompt, you can type:
ECHO %APPDATA% to see the folder location.CD %APPDATA% to change directories to the folder location.DIR "%APPDATA%" to see the list of files in the folder. Notice the DIR command requires the use of quotation marks here because the APPDATA variable most likely contains embedded spaces.
Within Windows Explorer, you can simply go to the Address Bar, using either the mouse or by hitting Alt-D, and then typing %APPDATA% as the address and pressing Enter or clicking Go.
There are lots of potentially useful environment variables. You can go to the Command Prompt and type the command SET to see all the environment variables. You can also see the environment variables in System Properties, which you can get to by right-clicking on My Computer and selecting Properties, or from within the Control Panel.
Once in System Properties, select the Advanced tab, then click on the Environment Variables button towards the bottom. There you’ll see a bunch of environment variables. However, there are even more displayed in the Command Prompt when you issue the SET command.
Perhaps some sharp reader could explain why you don’t see the complete list of variables in the System Properties window. Perhaps the extra ones you see using the SET command are ones that are set by the system and that the user cannot be allowed to change. Anyone?
Figure 2 – The System Properties Control Panel applet with the Advanced tab displayed.
Getting to the ThisOutlookSession Module
If you haven’t added any code yet, the VbaProject.OTM project will contain a single standard module called ThisOutlookSession. This is where our code will go. If you don’t see ThisOutlookSession immediately, click on the plus sign to the left of “Microsoft Outlook Objects” under “Project1 (VbaProject.OTM)”, and then double-click on ThisOutlookSession. See figure 1 above.
The Application Object Event Handlers
In the ThisOutlookSession module, you can add general Subs and Functions, and you can also add event handlers for the Application object.
There are several interesting events that you can write handlers for here. To see them, click where it says “(General)” above the code window and select “Application” from the drop-down list. Immediately, the VBE will create a skeleton for the Application.ItemSend event for you. This happens to be the event we’re interested in, and we’ll come back to it shortly. However, you can pull down the list of events in the drop-down to the right of the drop-down you just used. It should currently read “Application_ItemSend”. You can use the Application_Startup and Application_Quit events to execute code upon Outlook startup and exit. You can use the Application_NewMail event to execute code upon the receipt of new mail. (Surprising, right?) We’ll stick to the Application_ItemSend event in this article.
Figure 3 – ThisOutlookSession code window showing the Application_ItemSend skeleton and the list of Application events.
Application_ItemSend Event Handler
When you hit Send after composing an email message, Outlook does several things. For example, it runs the spell checker (if the “Always check spelling before sending” option is checked in the Spelling tab of the Tools / Options command). Then it attempts to resolve the recipient names. If any of your recipient names are ambiguous, it will prompt you to select the correct contact. After all that is completed, it will trigger an Application.ItemSend event to give you an opportunity to process the message.
Here’s the Application_ItemSend event handler skeleton:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
End Sub
You can see it sends you a reference to the Item being sent, which should hopefully be a MailItem. You are also sent a parameter called Cancel. If you want to cancel the sending of the Item, then set Cancel to True before you exit the event handler. So, as an example, if you wanted to disable the sending of all email, you could write the event handler like this:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Cancel = True
End Sub
Checking for a Missing Attachment
We’ll check for certain words in the Subject and Body of the user’s email message, such as “attached” or “enclosed”. If any of those words are in the email message, but no attachments were specified, then we’ll alert the user that they may have forgotten an attachment and ask them if they would like to specify one now. If they want to, then we’ll execute the Insert / File… command so they can specify the attachment.
Here’s the code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
If Item.Class <> olMail Then Exit Sub
If Item.Attachments.Count > 0 Then Exit Sub
If Not SearchForAttachWords(Item.Subject & ":" & Item.Body) Then Exit Sub
If UserWantsToAttach Then
ExecuteInsertFileCommand
End If
End Sub
Let’s take it line by line.
- First we check to make sure the Item Class is olMail, basically making sure the Item is a MailItem, as opposed to a ContactItem or PostItem or whatever. This step is probably not necessary, because ItemSend is typically only called when sending a MailItem, but it never hurts to be sure, if only to avoid a possible runtime error.
- Then we check to see if there are already any attachments specified. If there are, then we certainly don’t need to do anything, so we exit.
- Next we call a function which searches for certain key words in the Subject and Body of the message. If the words are not found, then we’ll exit.
- Finally, if we’ve gotten this far, then the Item IS a MailItem, there are no attachments yet, and one of the key words was found, so we now ask the user if they would like an eleventh-hour reprieve to specify an attachment. If they do, then we execute the Insert / File… command so they can specify the attachment.
Checking for Certain Key Words
Here’s the code for the SearchForAttachWords Function:
Function SearchForAttachWords(ByVal s As String) As Boolean
Dim v As Variant
For Each v In Array("attach", "enclos")
If InStr(1, s, v, vbTextCompare) <> 0 Then
SearchForAttachWords = True
Exit Function
End If
Next
End Function
You can see we’re searching for the text strings “attach” and “enclos”. You can add others if you wish. The text search doesn’t care whether it’s matching whole words or not, so we’ll find “attach” just as easily as “attached” or “attachment”, or even “attaché” for that matter. Similarly, we’re searching for “enclos” so we can find “enclose”, “enclosed”, and even “enclosure”.
The use of vbTextCompare makes the search case-insensitive.
Seeing If the User Wants to Correct His or Her Potential Mistake
We don’t want to be presumptuous. That’s why we won’t definitively decide the user made a mistake. Rather, we’ll suggest to them that perhaps they meant to include an attachment. Much more subtle.
Function UserWantsToAttach() As Boolean
If MsgBox("It appears you may have forgotten to specify an attachment." _
& vbCrLf & vbCrLf & _
"Would you like to do this now?", _
vbQuestion + vbYesNo) _
= vbYes Then
UserWantsToAttach = True
End If
End Function
Executing the Insert / File… Command Programmatically
The way to execute a MenuBar or CommandBar command in an Office application is to refer to the correct Control within the correct CommandBar and invoke the Execute method, like this:
Sub ExecuteInsertFileCommand()
Application.ActiveInspector.CommandBars("Standard").Controls("&File...").Execute
End Sub
Tying It All Together
After you enter these four subs and functions into the ThisOutlookSession module, be sure to save the project. Then you can use Alt-Q to quit the VBE and return to Outlook. Start sending some mail and see what happens. You may want to experiment by sending to yourself so you don’t annoy everyone in your contact list!
Wrap Up
In this article, we’ve demonstrated:
- Using the APPDATA environment variable.
- Using the SET command from the Command Prompt.
- Using Alt-D to access the Address Bar in Windows Explorer or Internet Explorer.
- Creating event handlers for the Application object in the ThisOutlookSession module.
- Using the MailItem Class, Attachments.Count, Subject and Body properties.
- Using the VBA Array function to create an array of variants on-the-fly.
- Using the VBA InStr function to search for strings contained within other strings.
- Executing Office commands programmatically using the CommandBars collection.
Please let us know what interesting uses you find for this information. You can send Feedback through the magazine or feel free to drop me a line.
– Dave
This article originally appeared in
Vol 3, Issue 8 of TechTrax.
Contact us if we can assist you.
