Outlook rule to not send if attachment name doesn't match criteria

SerenityNetworks · #1 03-16-2016, 08:37 PM

I need a method to prevent the sending of an email or at least present a warning to the user if an attachment doesn't meet certain criteria. In short, I want to safeguard personal information. Here is the scenario:

I have a Word document named "Tom Jones Personal Information". It contains personal information on Tom Jones.
I need to email this document to Tom Jones, plus CC a few others.
I need a check to prevent, or at least warn and cancel, the sending of the email if some criteria is not met.
- One thought is that "Tom Jones" must be present in the Subject line, else the sending is cancelled or the user is warned and can cancel the sending.
- Another thought is that "jones" must be present in the email address, else...

You get the idea. I'm open to strategies other than the ones I described. I was thinking that perhaps an Outlook rule could be created to do this, but I'm not finding a way to make that happen. The crux of the matter is that I need a method that helps assure the correct attachment is going to the correct recipient. Any guidance on this will be greatly appreciated.

I'm using Office 365.

Thanks in advance,
Andrew

gmayor · #2 03-17-2016, 12:30 AM

While you cannot do this with a rule, as rules on outgoing messages only apply after the message has been sent, you can do it with a macro. The following in the ThisOutlookSession module will check all outgoing messages for an attachment with 'Personal Information' in the filename. If it finds such an attachment, it will check if the Subject contains the name that follows Personal Information. If it does not the send will stop and you will see a warning message.

So for it to work, the attachment filename must be "Personal Information name.ext" (where ext is any file type) and the same name must be in the subject.

Code:

Option Explicit

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim olAtt As Attachment
Dim strCheck As String
Dim bCheck As Boolean
    If Item.Attachments.Count > 0 Then
        For Each olAtt In Item.Attachments
            If olAtt.FileName Like "*Personal Information*.*" Then
                strCheck = Replace(olAtt.FileName, _
                                   Right(olAtt.FileName, _
                                         Len(olAtt.FileName) - _
                                         InStrRev(olAtt.FileName, _
                                                  Chr(46)) + 1), "")
                strCheck = Replace(strCheck, " Personal Information", "")
                If InStr(1, Item.Subject, strCheck) = 0 Then
                    MsgBox "Check the attachment!"
                    Cancel = True
                    Exit For
                End If
            End If
        Next olAtt
    End If
lbl_Exit:
    Exit Sub
End Sub

SerenityNetworks · #3 03-17-2016, 07:41 AM

Thank you! This would appear to be perfect except that I'm not getting it to work.

I've added the code to ThisOutlookSession, closed the Visual Basic editor, closed Outlook, and answered "Yes" to saving the session. I also went to Macro Security and enabled all macros. It did not work (I did not receive a prompt and an improperly formatted email was sent). I checked Visual Basic and the code was where it was supposed to be. I then set the Macro Security to "Notifications for all macros" and tried testing again (so sending would fail). I did not receive a prompt for the macro running. This leads me to believe the macro is not being triggered for some reason.

I'm at a loss as to why the macro is not running/working. Any ideas?

Thanks again,
Andrew

UPDATE:
I added the following code to your macro and did not receive a prompt when sending an email without an attachment. It does seem the macro is not being triggered.

Code:

    If Item.Attachments.Count = 0 Then
        MsgBox ("There are no attachments in this email.")
    End If

NEW UPDATE:
I removed my added code. When I closed Outlook I received a new prompt to save the session (I didn't capture the message, but it was not about ThisOutlookSession.) When I reopened Outlook I was prompted to enable macros and now it works! Whoohoo! Now I need to figure out how to make this work the first time on another person's PC.

Thank you!

SerenityNetworks · #4 03-17-2016, 08:08 AM

As a follow-up question...

Would it be possible to also check the name of a distribution list in the "To"?

For example, we have To: Bob (which is a distribution list to robert@hotmail.com and sally@hotmail.com). We have Subject: Personal Information Bob. We have the attachment "Personal Information Bob.txt". Can we add a check that the distribution list is named or contains "Bob"?

Thanks again,
Andrew

UPDATE:
Thinking it through a bit more, it would be good if it could perform the check if the To is a distribution list. If the To is not a distribution list (that is, just an email), then perhaps throw up a prompt that says, "You are attempting so send an email to X with an attachment named Y. Do you want to continue?" And then allowing the user to either send or cancel. Is this possible/feasible?

gmayor · #5 03-17-2016, 11:50 PM

You are going to have to digitally sign the project on each PC that the macro is used on to avoid the macro enabling issues and retain security -http://www.gmayor.com/create_and_employ_a_digital_cert.htm

While you can check if the To is a particular distribution list
e.g. If .To = "Bob"
I don't think you can check if To is any old distribution list without additional software - see http://www.outlookcode.com/d/code/promptdl.htm

SerenityNetworks · #6 03-18-2016, 07:28 AM

For the moment I'm okay without the digital signatures and just allowing the user to manually allow the macro to run. I did a little research on the signatures and the process doesn't seem too onerous.

For my uses checking for a very specific distribution list is what I need. That is "Bob" is the distribution list, and "Personal Information Bob" is the name of the attachment and in the Subject line. I want all three to match up before the email is allowed to be sent.

Thanks,
Andrew

gmayor · #7 03-18-2016, 10:15 PM

OK if it is always a distribution list called BOB then surround the

Code:

If Item.Attachments.Count > 0 Then
    '...............
End If

with

Code:

If Item.To = "Bob" then
    If Item.Attachments.Count > 0 Then
        '...............
    End If
End if

However I don't see why this is necessary, as it will already flag a warning if the subject and the attachment don't match, whoever the message is sent to. Other attachments are unaffected. By adding the sender distribution list to the mix, you increase the danger of sending a message to the wrong person, rather than reduce it.

SerenityNetworks · #8 03-19-2016, 07:38 AM

Quote:

Originally Posted by gmayor

...By adding the sender distribution list to the mix, you increase the danger of sending a message to the wrong person, rather than reduce it.

I'm not following. I would want this to be an "and". That is, the "To" AND "Subject" AND 'attachment name' must all match. Unless all three match up then it doesn't send.

Thanks,
Andrew

gmayor · #9 03-19-2016, 10:33 PM

The macro looks for an outgoing message with Personal Information in the attachment filename. It then strips the Personal Information text from the filename and looks to see if the remaining name is in the subject and then flags a warning no matter who the message is sent to.

The problem with using a distribution list is that it can only be identified as a list by looking at .To, if the list has not been expanded.

To see what I mean, send a message to the outbox (without sending immediately) with the three parameters correct then open and resend the message from the outbox. The message will still go through with the original code, but with the change below it will now not, because the sender is now the members of the group and not the group.

Based on your updated requirement, what I believe you now require would be to change

Code:

If InStr(1, Item.Subject, strCheck) = 0 Then

to

Code:

If InStr(1, Item.Subject, strCheck) = 0 Or Not Item.To = "Bob" Then

SerenityNetworks · #10 03-20-2016, 07:57 AM

Thank you. I'm on my phone right now, but will try to give it a shot later today.

In my case, I'm not concerned with users expanding the distribution list. The more simple and consistent the matching then the more simple the process (usage rules) the users have to follow.

Thanks again. I'll post back with the code I settle on.

Andrew

SerenityNetworks · #11 03-20-2016, 10:56 AM

This works perfectly for my needs. Thank you!

Code:

Option Explicit
    
'https://www.msofficeforums.com/outlook/30477-outlook-rule-not-send-if-attachment-name.html
'https://www.msofficeforums.com/member.php?u=26884

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim olAtt As Attachment
Dim strCheck As String
Dim bCheck As Boolean
    If Item.Attachments.Count > 0 Then
        For Each olAtt In Item.Attachments
            If olAtt.FileName Like "*File*.*" Then
                strCheck = Replace(olAtt.FileName, _
                                   Right(olAtt.FileName, _
                                         Len(olAtt.FileName) - _
                                         InStrRev(olAtt.FileName, _
                                                  Chr(46)) + 1), "")
                strCheck = Replace(strCheck, "File ", "")
                'Use the following line of code to assure Group/Distribution Name, Subject, and Attachment all contain the same keyword.
                'If InStr(1, Item.Subject, strCheck) = 0 Or Not Item.To = strCheck Then
                'Use the following line of code to only check that Subject and Attachment contain the same keyword.
                If InStr(1, Item.Subject, strCheck) = 0 Then
                    MsgBox "Check the attachment(s)!"
                    Cancel = True
                    Exit For
                End If
            End If
        Next olAtt
    End If
lbl_Exit:
    Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using a VLOOKUP - when 2 rows match criteria it returns the first value in the cel only how 2 change	djrobst	Excel	4	10-28-2015 01:32 AM
Count unique values that match 2 or more criteria	caeiro01	Excel	1	10-25-2015 02:34 AM
Outlook macro to check a value of a cell in an attachment and send an email based on that value	ketangarg86	Outlook	13	03-25-2015 07:11 AM
Outlook doesn't send/receive anymore.	Astacus	Outlook	1	12-29-2012 03:40 PM
Vlookup or Index/Match - Multiple Criteria	ruci1225	Excel	1	01-15-2012 07:31 AM