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

[SerenityNetworks]

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]

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.

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]

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.

    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]

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]

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]

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]

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

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

with

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]

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]

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

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

to

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

[SerenityNetworks]

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]

This works perfectly for my needs. Thank you!

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