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:
|OptionExplicit PrivateSub Application_ItemSend(ByVal Item AsObject, Cancel AsBoolean) If Item.Class = Outlook.olMail ThenIf Item.Attachments.count = 0 ThenIf SearchForAttachWords(Item.Body) ThenDim 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 = TrueEndIf EndIfEndIfEndIfEndSub Function SearchForAttachWords(ByVal s AsString) AsBooleanDim positionMailTo AsLong 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) EndIfEndIf Dim v AsVariant ForEach v In Array("attach", "enclosed", "bijgevoegd", "bijlage") Dim tempPos AsLong 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 = TrueExitFunctionEndIfNextEndFunction|
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