Run Script rule not running for newly arriving messages

[Bri the Tech Guy]

Hello All,

First a bit shout out for Diane Poremsky, who's assisted me extensively to get me to this point. A bit of background, just so you have it. I have a visually-impaired client who does freelance medical translation work where job offers come to him, and a variety of other potential takers, via e-mail. In these e-mail messages are accept and reject links for a given job. If the job is in the area he covers he would like to have the accept link automatically clicked-through upon receipt, because even brief delays generally mean losing the job. He uses a screen reader so the logistics of getting to that link involve time he does not have.

I have coded a VBA script that does what is needed, as well as a test version that I can run on my own machine that activates the link to the New York Times homepage when I receive messages from a specific sender that have a .com URL in them. This imitates the precise situation the real script does. I can make the testing script work in both the debugger (with a subroutine that allows me to trigger it for the message I have selected) and when I set up a "Run Script" rule with the correct criteria and run it on messages already in the inbox.

The sticking point here is that the script does not get triggered, or at least not that I can tell (since I have a sound played as well) when messages arrive that I am 100% positive meet the filtering criteria in the rule itself. This is occurring both on my machine under Outlook 2010 and his machine running Outlook 2016.

We've played with signing the script with selfcert, and that seems to be triggering more problems. When the script is signed we are getting the following error message box:

I have been told that this issue is happening commonly, so we tweaked the script "artificially" to get the signature removed by doing a fake modification. Then we changed the Trust Center settings to allow all macros to run just to see if that would make it work. It hasn't.

I am at my wits end trying to figure out what could be wrong here. If anyone would like for me to post the text of the actual script itself I will be happy to do so, but it does not seem to be directly relevant since both the actual version and my slightly modified testing version are both behaving the same way (or not behaving, in this case) on two different machines (both running Windows 10) and two different versions of Outlook.

Any assistance in getting the final piece of the puzzle to fall into place that would allow this script to actually run when a new message with a specific sender's e-mail address arrives would be appreciated more than you can know.

Brian

 

[Bri the Tech Guy]

I decided it really might be helpful if folks were able to take a gander at the actual code involved, so here it is:

Option Explicit

Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long
 
Public Sub OpenLinksMessage(olMail As Outlook.MailItem)

Dim Reg1 As RegExp
Dim AllMatches As MatchCollection
Dim M As Match
Dim strURL As String
Dim RetCode As Long

Set Reg1 = New RegExp

With Reg1
.Pattern = "(https?[:]//([0-9a-z=\?:/\.&-^!#$;_])*)"
.Global = True
.IgnoreCase = True
End With

' If the regular expression test for URLs in the message body finds one or more
If Reg1.test(olMail.Body) Then

'      Use the RegEx to return all instances that match it to the AllMatches group
       Set AllMatches = Reg1.Execute(olMail.Body)
       For Each M In AllMatches
               strURL = M.SubMatches(0)
'              Don't activate any URLs that are for unsubscribing; skip them
               If InStr(strURL, "unsubscribe") Then GoTo NextURL
'              If the URL ends with a > from being enclosed in darts, strip that > off
               If Right(strURL, 1) = ">" Then strURL = Left(strURL, Len(strURL) - 1)
'              The URL to activate to accept must contain both of the substrings in the IF statement
               If InStr(strURL, "tntproviders.onecallcm.com") And InStr(strURL, "rtype=A") Then
'                    Activate that link to accept the job
                     RetCode = ShellExecute(0, "Open", strURL)
                     Set Reg1 = Nothing
                     Exit Sub
               End If

NextURL:
   Next
  
End If

Set Reg1 = Nothing

End Sub

 

[Diane Poremsky]

i disabled the if - If InStr(strURL, "tntproviders.onecallcm.com") And InStr(strURL, "rtype=A") Then - to do a quickie test. My rule was basic - no conditions, so it checks all messages. It worked.

i enabled the if line and used the link in the forum message for this thread -
If InStr(strURL, "forums.slipstick.com") And InStr(strURL, "95631") Then

it did not work. i don't understand why it worked for you with a .com value and didn't for me - could be the missing 1 - which represents the character to start with.

this worked:
If InStr(1, strURL, "forums.slipstick.com") > 0 And InStr(1, strURL, "95631") > 0 Then

and this worked:
If InStr(1, strURL, "com") And InStr(1, strURL, "95631") Then

 

[Bri the Tech Guy]

Diane,

Thank you so very much again for taking the time to assist in debugging and coming up with a workaround. I may have misstated something but the original code does not work for me on new incoming messages, either, when I substitute a straight InStr(strURL, ".com"). I had not been able to get this thing to trigger for anything as far as new incoming messages were concerned yet it would work perfectly if I said to apply the rule to messages already in the inbox. That still makes absolutely no sense to me whatsoever.

I will give the variant you post without the "> 0" first, as I think it's cleaner and it follows the general logic that a non-zero result means "true" in a great many programming languages.

I shall certainly report back with the result.

 

[Bri the Tech Guy]

Diane,

Still "no dice" on the script itself being invoked, which is clearly where the problem lies. When I had my Outlook rule set up to play a sound and invoke the script, e.g.,:


When a new message comes in with either one of the gmail addresses specified for the sender, chord.wav does play, but nothing else happens. So, I say to myself, let's take the "play a sound" out of the rule and build it in to the VBA script itself:

Option Explicit

Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long
 
Private Declare Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long

Sub PlayTheSound(ByVal WhatSound As String)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            Beep            ' Can't find the file. Do a simple Beep.
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If

    sndPlaySound32 WhatSound, 0&    ' Finally, play the sound.
End Sub
 
Public Sub OpenLinksMessage(olMail As Outlook.MailItem)

Dim Reg1 As RegExp
Dim AllMatches As MatchCollection
Dim M As Match
Dim strURL As String
Dim RetCode As Long

Set Reg1 = New RegExp

With Reg1
.Pattern = "(https?[:]//([0-9a-z=\?:/\.&-^!#$;_])*)"
.Global = True
.IgnoreCase = True
End With

PlayTheSound "chimes.wav"

' If the regular expression test for URLs in the message body finds one or more
If Reg1.test(olMail.Body) Then

'      Use the RegEx to return all instances that match it to the AllMatches group
       Set AllMatches = Reg1.Execute(olMail.Body)
       For Each M In AllMatches
               strURL = M.SubMatches(0)
'              Don't activate any URLs that are for unsubscribing; skip them
               If InStr(1, strURL, "unsubscribe") Then GoTo NextURL
'              If the URL ends with a > from being enclosed in darts, strip that > off
               If Right(strURL, 1) = ">" Then strURL = Left(strURL, Len(strURL) - 1)
'              The URL to activate to accept must contain both of the substrings in the IF statement
               If InStr(1, strURL, ".com") Then
                     PlayTheSound "TrainWhistle.wav"
'                    Activate that link to accept the job
                     RetCode = ShellExecute(0, "Open", "http://nytimes.com")
                     Set Reg1 = Nothing
                     Exit Sub
               End If

NextURL:
   Next
  
End If

Set Reg1 = Nothing

End Sub

Private Sub TestLaunchURL()
    Dim currItem As MailItem
    Set currItem = ActiveExplorer.Selection(1)
    OpenLinksMessage currItem
End Sub

If I walk through this in the debugger using the TestLaunch it works perfectly. If I send myself a new message nothing at all happens other than the message showing up in my inbox. Clearly something is going awry with the actual invocation of the script itself, and I have absolutely no idea what and no idea why, since the incoming messages meet the criteria specified and, if I include "Play a sound" in the rule itself the sound always plays when a new message with either one of the two specified sender addresses is present, but no further processing happens.

It seems like macros are somehow disabled entirely, but I know for certain (and just verified) that I have "Enable all macros" as my setting in the Trust Center. The script is simply never being run when a new message arrives or else I should always at a minimum get the chimes sound being played, and I don't.

 

[Diane Poremsky]

When I had my Outlook rule set up to play a sound and invoke the script, e.g.,:

when you do multiple actions, you can have issues. if you need to play a sound, do it from the script.

did you put the macros in thisoutlooksession or in a new module? I tested it in a module - while it shouldn't matter, i have had occasions where run a scripts didn't work in thisoutlooksession.

 

[Bri the Tech Guy]

It's in "ThisOutlookSession." I have also had another assistant suggest, as a debugging step, to create a subroutine that does nothing but pop up a message box with the sender's e-mail address as it's action. If I substitute that subroutine for OpenMessageLinks in the rule, whether I leave the "play a sound" on the rule or remove it, it runs.

I'll do my research on creating a standalone module other than ThisOutlookSession and transplanting the code into it, instead.

 

[Diane Poremsky]

click Insert menu > Module. Then select all, cut, paste the macro into it.

 

[Bri the Tech Guy]

Diane, I haven't gotten to that yet, but, read on . . .

After lots of additional testing I have determined that the OpenLinksMessage subroutine is being called, but if it's being called out of the Outlook rule the IF Reg1.test(olMail.Body) test is coming back false when I absolutely know that it is not false.

I have actually removed the test directly from the IF statement so I can capture the RetCode immediately prior and then present a message box:

RetCode = Reg1.Test(olMail.Body)
MsgBox "The RetCode from Reg1.Test(olMail.Body) equals" + Str(RetCode)
' If the regular expression test for URLs in the message body finds one or more
If RetCode Then

When this script is run via the debugger, and I have selected a message that I just sent to myself, the message box tells me the RetCode is equal to 1. When the same was run on that same message when it has just arrived in the inbox the test consistently comes back as 0.

I'm flummoxed, and more confused than ever, since the same message and the same code is giving two different results depending on whether it's invoked from the rule or the debugger. It really seems like that whatever olmail.Body resolves to when passed by the rule to the subroutine is entirely different than what it resolves to when OpenLinksMessage is called from TestLaunch and where the message value itself has been set to ActiveExplorer.Selection(1). It's as though the message body doesn't even exist when the message is passed by the rule versus being there when passed from the inbox. How can this be?

Brian

 

[Diane Poremsky]

Arrr... tested your last code sample right after opening a test mailbox that i hadn't opened for a few days and its going nuts. I'm getting the chime, not the train whistle - i may not have that sound here, but i recommend you use only one sound.

I'm getting -1 on the retcode sample, although i'm not sure it matters here since the macro works on my samples.

look at the message source code - do the urls begin with http? some will drop that and begin with //.

 

[Bri the Tech Guy]

Diane,

I get the chime, but nothing else, either, when the script is actually being triggered by an Outlook Rule on an incoming message. I get the chime, tada (I've added), and train whistle if I make the rule run against messages in the inbox or use TestLaunchURL to pass a specific message to it.

I also get negative one (-1) when the test passes, but as you say, that really doesn't matter as any non-zero result evaluates to TRUE in an IF statement.

I am positive that the URLs begin with http:// because I've typed them by hand and sent them. I also tweaked the code to open the URL that I have included in the message, and when run through the debugger that part works, too.

It is clear to me that somehow, whatever olMail.Body contains when it comes from the rule triggering for a new, incoming message is NOT the same as when it comes through either with the rule being run on existing messages or a manual invocation through the testing subroutine.

Brian

 

[Bri the Tech Guy]

Final update for the day. Since the issue appears to be with the value of the Body (and I decided to check HTMLBody, too), I added the following code immediately before the regular expression test:

If IsNull(olMail.Body) Then
     MsgBox "The message body is null!!"
Else
     MsgBox "BODY:  " + "|" + olMail.Body + "|"
End If

If IsNull(olMail.HTMLBody) Then
     MsgBox "The message HTMLbody is null!!"
Else
     MsgBox "BODY:  " + "|" + olMail.HTMLBody + "|"
End If

If I send a message whose only content is "britishtoolworks", here are the results when the Outlook rule triggers the script upon arrival of that message:

I cannot imagine what value Body has when there is absolutely nothing shown between the pipe characters, but given the logic of the IF statement it's not null.


Now, if I use TestLaunch

ow if I use TestLaunchURL, for EXACTLY THE SAME MESSAGE once it's in my inbox, I get:

Sorry if the first set of photos is doubled. I did not do this and the formatting of this message around the images at the end is kind of wacko.

Brian

 

[Bri the Tech Guy]

P.S.: I did move the code into a newly created module and it makes no difference in the result.

 

[Diane Poremsky]

You don't want to use HTMLBody - that gets a lot of extra garbage (style declarations etc).

it works here in both rules and manually. Did you remove the chord.wav action from the rule? Multiple actions can affect it. (My test rule only has the script action).

I did notice something weird though - i replaced the open url line with MsgBox olMail.Body - and if i use your test code before the regex, MsgBox olMail.Body returns the htmlbody, if i swap the if's so it shows htmlbody first, then i get the simple body without the extra html garbage. It's as if outlook is storing it in memory.

 

[Bri the Tech Guy]

Diane,

First and foremost I want to again offer my sincerest thanks for all you have done to assist me through debugging this. I have been calling upon you as well as folks on stackoverflow.com and social.technet.microsoft.com and between all of us a solution has been found.

It appears that this has got to do with actual timing of processing under Outlook. Whatever gyrations are going on "under the hood" when a message is passed by a rule to a script the .Body member of the mail item is not immediately available. Some sort of processing has to be going on to get it ready, but it's not there when passed to the rule. So, on the recommendation of someone elsewhere I first played with adding a call to the .Display method to "burn time" and that worked. Since I don't actually want to display the message I used an alternative method to "burn time" and that was essentially to copy the message itself using the .GetInspector property.

I do not know exactly what I'm giving Outlook time to do such that .Body becomes available for examination, but using that .GetInspector line buys enough time to allow it to be available when what is in it is actually to be tested by the script logic. Here is the code, stripped of all the debugging stuff for playing sounds at various steps, that actually works when a message is passed to it by Outlook when that message arrives:

Option Explicit

Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long
 

 
Public Sub OpenLinksMessage(olMail As Outlook.MailItem)

Dim InspectMail As Outlook.MailItem
Dim Reg1 As RegExp
Dim AllMatches As MatchCollection
Dim M As Match
Dim strURL As String
Dim SnaggedBody As String
Dim RetCode As Long

' The purpose of the following Set statement is strictly to "burn time" so that the .Body member of
' olMail is available by the time it is needed below.  Without this statement the .Body is consistently
' showing up as empty.  What's interesting is if you use MsgBox to display InspectMail.Body immediately after
' this Set statement it shows as empty.
Set InspectMail = olMail.GetInspector.CurrentItem

Set Reg1 = New RegExp

With Reg1
.Pattern = "(https?[:]//([0-9a-z=\?:/\.&-^!#$;_])*)"
.Global = True
.IgnoreCase = True
End With

RetCode = Reg1.Test(olMail.Body)
' If the regular expression test for URLs in the message body finds one or more
If RetCode Then
'      Use the RegEx to return all instances that match it to the AllMatches group
       Set AllMatches = Reg1.Execute(olMail.Body)
       For Each M In AllMatches
               strURL = M.SubMatches(0)
'              Don't activate any URLs that are for unsubscribing; skip them
               If InStr(1, strURL, "unsubscribe") Then GoTo NextURL
'              If the URL ends with a > from being enclosed in darts, strip that > off
               If Right(strURL, 1) = ">" Then strURL = Left(strURL, Len(strURL) - 1)
'              The URL to activate to accept must contain both of the substrings in the IF statement
               If InStr(1, strURL, ".com") Then
'                    Activate that link to accept the job
                     RetCode = ShellExecute(0, "Open", strURL)
                     Set InspectMail = Nothing
                     Set Reg1 = Nothing
                     Set AllMatches = Nothing
                     Set M = Nothing
                     Exit Sub
               End If

NextURL:
   Next
  
End If

Set InspectMail = Nothing
Set Reg1 = Nothing
Set AllMatches = Nothing
Set M = Nothing

End Sub

It actually annoys me to no end that Outlook itself is not "holding" a message long enough to ensure that all object members are fully available before it gets handed off to a script for processing. This is not the sort of thing one would expect and it has taken many days and hours (and nearly tearing-out of hair) to finally tease out what the issue actually was, and it wasn't with the script code in any meaningful sense.

 

[Bri the Tech Guy]

By the way, and unrelated to the problem now solved, what is it that defines the variables that one should do the Set X = Nothing statement(s) for prior to exiting a subroutine?

Based on what I've observed in various code samples I presume it's if the variable is for holding an object or collection, but I am not 100% sure about that. This is the reason I added the couple of additional set statements for variables that are objects/collections.

 

[Diane Poremsky]

It actually annoys me to no end that Outlook itself is not "holding" a message long enough to ensure that all object members are fully available before it gets handed off to a script for processing.

Honestly, I have never needed to do anything to make it available - it just works. (IMAP needs time since the body comes after the header. Exchange and POP3 should work ok.) I don't know if its my computer, my server, or something else. However, my preference is to use itemadd macros over rules whenever possible. The only drawback is every message gets checked and you need If statements to filter - or use rules to move to a folder and have itemadd watch that folder.

By the way, and unrelated to the problem now solved, what is it that defines the variables that one should do the Set X = Nothing statement(s) for prior to exiting a subroutine?

objects and collections should be released, although I've been told its not always necessary when you do something - for example, if you send a message, no need to use = nothing because sending it clears it.

 

[Bri the Tech Guy]

So it's here for future readers I am using Gmail via IMAP access to receive the messages that are being checked.

I still find it amazing that Outlook's own code does not work in such a way that it is ensured that all pertinent members of a MailItem object are not prepopulated prior to its being handed off for processing by a rule, regardless of the access method to the e-mail server. This is, in my opinion, a bug, and a serious one.

 

[Diane Poremsky]

I thought you were using exchange... Imap was traditionally "headers only" and bodies came down when the message was selected. (It was designed for access from many computers in the days of tiny drives.) Newer outlooks will download the full message automatially, but still get the header first, then the body. The header has enough info to process the rule and runs as the body comes down.

 

[Bri the Tech Guy]

These days most IMAP users expect that message bodies will be automatically downloaded for offline use for messages newer than some set limit that gets set in most clients, say 14 days.

I do see what you're saying, though. If it's the presumption by Outlook that a message body will not be coming from IMAP (although in my case clearly it is) then there should be some sort of warning when a rule is being created against an IMAP access account that clues you in to that potential issue.

Since rules can, in theory, access any member of a MailItem object it still seems bizarre to me to even think of passing that object to a script prior to all prepopulation that can take place actually taking place. The .Body is clearly being populated asynchronously from the rule processing and the rule gets ahead of the .Body being populated if you don't deliberately slow it down. There is no way any random script writer could or should know this, at least without being warned.