www.GuidoDiepen.nl

Who has never encountered the following problem:

You want to send somebody an email with an attachment. You create the message, hit send and the second you let go of the send button, you think “%$@!%!^, I forgot to attach the file”

One of my colleagues had a possible work around for this problem: prevent outlook from sending messages directly when you hit send, but have outlook send all messages in the outbox every given number of minutes. This way, on average you have some time to figure out you forgot to attach something

I have to say that I did not like this work around that much, so I started looking on the internet what I can find as other means to tackle this problem. I found aBlog with some VBA macros that will give an additional warning when it appears you forgot an attachment while sending the email.

The author of the original article did have one other complaint, and that was that he was getting a security warning every time the macro was run. This can be solved in two ways: you can either set the security level for macros in Outlook to Low (i.e. every macro can be executed, bad bad idea ). The other way is to self-sign your VBA project, for which you can also find instructions on the internet.

One other thing that I did not like about the code as presented on ablog, is that it does not try to be clever about false positives: If I send an email to somebody with the word attach in it, and that person replies to my email, this reply contains the word attach (because of my original message). If you now reply to this reply again, the macro will detect the word “attach” from the original message, and trigger a warning when you try to send the email. I don’t want to be bothered with warning messages if they are most probably not needed, so the macro almost fit my needs, but not 100% yet

To overcome this last problem, I have modified the original code just a little bit. Because I hope that somebody else might have some advantage with this modification, I have put it below:

Option Explicit
 
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
  If Item.Class = Outlook.olMail Then
    If Item.Attachments.count = 0 Then
      If SearchForAttachWords(Item.Body) Then
        Dim userReply As VBA.VbMsgBoxResult
 
        userReply = VBA.MsgBox("It appears you are sending the email without attachments while " & _
                           "the body/subject contains possible references to an attachment." & _
                           VBA.vbnewline & "Do you want to send the message without attachments?", _
                           VBA.vbYesNo + VBA.vbDefaultButton2 + VBA.vbQuestion + VBA.vbSystemModal, _
                           "Possibly missing attachment...")
 
        If userReply = VBA.vbNo Then
          Cancel = True
        End If
 
      End If
    End If
  End If
End Sub
 
Function SearchForAttachWords(ByVal s As String) As Boolean
  Dim positionMailTo As Long
 
  positionMailTo = VBA.InStr(1, s, "mailto:", VBA.vbTextCompare)
  If positionMailTo = 0 Then
    positionMailTo = VBA.InStr(1, s, "From:", VBA.vbTextCompare)
    If positionMailTo = 0 Then
      positionMailTo = VBA.Len(s)
    End If
  End If
 
  Dim v As Variant
 
  For Each v In Array("attach", "enclosed", "bijgevoegd", "bijlage")
    Dim tempPos As Long
    tempPos = VBA.InStr(1, s, v, VBA.vbTextCompare)
 
    'Only interested in the keywords when they are before mailto:,
    'which would be the original message we are replying to

    If (tempPos <> 0) And (tempPos < positionMailTo) Then
      SearchForAttachWords = True
      Exit Function
    End If
  Next
End Function

To get everything working, copy the above to your clipboard, fire up outlook and press ALT-F11 to get to the visual basic editor. In the visual basic editor, go to the ThisOutlookSession which can be found under Project1->Microsoft Outlook Objects. After you copied this and you don’t want to be annoyed with security warnings of outlook, please follow the earlier mentioned link regarding signing the project with a self-signed certificate.

Some comments regarding the above code:

• I prefix all functions and values with their respective origin (e.g. VBA.InStr(…), VBA.MsgBox(…), and VBA.vbYesNo, etc ). The reason for this is that at my work, I have outlook 2007 installed on my computer. I have created quite a bunch of macros that are now distributed via copying the VbaProject.otm file. Unfortunately, when other users have outlook 2002 or outlook 2003, they get error messages about things that cannot be found when I do not prefix everything. After the prefixing, there are no problems at all.
• bijgevoegd / bijlage are the dutch words for “attached” and “attachment”. Of course if you want to have the warning trigger on other words, you can put the additional words on this line.

Please let me know if you have any additions to the macro that I did not think of yet, or drop me a note in case the macro has saved you from some embarrassing resend after you just sent a message without attachment