Checking Outbound Messages for Attachments and Categorization


This post is primarily for Oliver Chinn (olic87 on Twitter) although anyone who wants to force users to include a category and an attachment on every message will find it useful.

I met Oliver as a result of a tweet he sent asking me “is there a macro for the quick access toolbar in outlook 07 to ask for a category and check for attachment before sending?” The answer, of course, is yes. Both of these tasks are easily accomplished via some scripting. In fact I wrote an article on Experts-Exchange (where I go by the screen name “BlueDevilFan”) that shows how to scan outbound messages for words suggesting an attachment should be included and check then check to see that one is included if any of the words are found. It’s equally simple to check messages for a category and prevent them from being sent until one is selected.

The solution I propose is a routine that checks messages as they are sent. For each outbound message the code will check to see if

  1. Does the message include one of the defined words/phrases which suggest a message should be attached? If it does, then is there an attachment?
  2. Has the message been categorized?

If either condition exists, then the code will display a dialog-box advising the user of the problem and offering them the opportunity to cancel the send and add the attachment and/or categorize the message. Clicking yes on that dialog-box will cancel the send. Clicking no will send the message on its way.

Instructions and Code.

The code comes in two parts. This is part 1. Follow these instructions to add the code.

  1. Start Outlook
  2. Click Tools > Macro > Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select Insert > Class Module
  5. In the Properties panel click on Name and enter AttachAndCategorize
  6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included comments wherever something needs to or can change
  8. Click the diskette icon on the toolbar to save the changes
'On the next line edit the list of keywords as desired.  Be sure to separate each word with a | character.'
Const KEYWORDS = "attached|attachment|attachments|enclosed|enclosure"

'On the next line edit the message that will be displayed when the message should include an attachment as desired.'
Const ATTACHMENT_MSG = "Wording in the message suggests that something is attached, but there are no items attached.  Do you want to cancel the send and add an attachment?"

'On the next line edit the message that will be displayed when the message should include a category as desired.'
Const CATEGORY_MSG = "The message has not been categorized.  Do you want to cancel the send and assign a category?"

'On the next line edit the dialog-box title as desired.'
Const MSG_TITLE = "Attachment and Category Checker"

Private WithEvents olkApp As Outlook.Application
Private objRegEx As Object

Private Sub Class_Initialize()
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Pattern = KEYWORDS
        .Global = True
    End With
    Set olkApp = Outlook.Application
End Sub

Private Sub Class_Terminate()
    Set olkApp = Nothing
    Set objRegEx = Nothing
End Sub

Private Sub olkApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim colMatches As Object, bolAttachment As Boolean, olkAttachment As Outlook.Attachment
    Set colMatches = objRegEx.Execute(Item.Body)
    If colMatches.count > 0 Then
        For Each olkAttachment In Item.Attachments
            If Not IsHiddenAttachment(olkAttachment) Then
                bolAttachment = True
                Exit For
            End If
        Next
        If Not bolAttachment Then
            If msgbox(ATTACHMENT_MSG, vbQuestion + vbYesNo, MSG_TITLE) = vbYes Then
                Cancel = True
            End If
        End If
    End If
    If Item.Categories = "" Then
        If msgbox(CATEGORY_MSG, vbQuestion + vbYesNo, MSG_TITLE) = vbYes Then
            Cancel = True
            Item.ShowCategoriesDialog
        End If
    End If
    Set olkAttachment = Nothing
    Set colMatches = Nothing
End Sub

Private Function IsHiddenAttachment(olkAttachment As Outlook.Attachment) As Boolean
    'Purpose: Determines if an attachment is embedded.'
    'Written: 10/12/2010'
    'Outlook: 2007'
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkAttachment.PropertyAccessor
    IsHiddenAttachment = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x7ffe000b")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This is part 2. Follow these instructions to add the code.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Edit the code as needed. I included comment lines wherever something needs to or can change
  4. Click the diskette icon on the toolbar to save the changes
  5. Close the VB Editor
  6. Click Tools > Trust Center
  7. Click Macro Security
  8. Set Macro Security to “Warnings for all macros”
  9. Click OK
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
Dim clsAttachAndCategorize As AttachAndCategorize

Private Sub Application_Quit()
    Set clsAttachAndCategorize = Nothing
End Sub

Private Sub Application_Startup()
    Set clsAttachAndCategorize = New AttachAndCategorize
End Sub

Notes.

  • This solution is intended for Outlook 2007 or 2010. It will not work in earlier versions.
  • This is a proof-of-concept. I have not thoroughly tested this to ensure that it works as intended. You should test it in your environment before putting it into production.
Advertisements

2 comments on “Checking Outbound Messages for Attachments and Categorization

  1. Unfortunately this will not work in Outlook 2003 because the PropertyAccessor Object was only made available in 2007. Workarounds such as Redemption or CDO will be required.

    • You’re right. I changed the code since the original posting and had neglected to modify my note. I’ll take care of that. Thanks for pointing it out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s