Run Script rule not running for newly arriving messages

Discussion in 'Outlook VBA and Custom Forms' started by Bri the Tech Guy, Aug 11, 2017.

  1. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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:
    VBA_Error_Message.jpg

    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
     
  2. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    I decided it really might be helpful if folks were able to take a gander at the actual code involved, so here it is:
    Code (Text):
    Copy Source
    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
     
  3. Diane Poremsky

    Diane Poremsky

    Senior Member
    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
     
  4. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.
     
  5. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.,:
    OutlookRule_W_Sound.jpg

    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:
    Code (Text):
    Copy Source
    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.
     
  6. Diane Poremsky

    Diane Poremsky

    Senior Member
    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.
     
  7. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.
     
  8. Diane Poremsky

    Diane Poremsky

    Senior Member
    click Insert menu > Module. Then select all, cut, paste the macro into it.
     
  9. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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:

    Code (Text):
    Copy Source
    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
     
  10. Diane Poremsky

    Diane Poremsky

    Senior Member
    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 //.
     
  11. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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
     
  12. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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:

    Code (Text):
    Copy Source
    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:

    Body_Debug_Msg.jpg

    Body_Debug_Msg.jpg HTMLBody_Debug_Message.jpg

    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 Body_Debug_Msg.jpg HTMLBody_Debug_Message.jpg ow if I use TestLaunchURL, for EXACTLY THE SAME MESSAGE once it's in my inbox, I get:
    Body_Debug_from_TestLaunchURL.jpg

    HTMLBody_Debug_from_Testlaunch.jpg

    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
     
  13. Bri the Tech Guy

    Bri the Tech Guy

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

    Diane Poremsky

    Senior Member
    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.
     
  15. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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:

    Code (Text):
    Copy Source
    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.
     
  16. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.
     
  17. Diane Poremsky

    Diane Poremsky

    Senior Member
    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.


    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.
     
  18. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.
     
  19. Diane Poremsky

    Diane Poremsky

    Senior Member
    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.
     
  20. Bri the Tech Guy

    Bri the Tech Guy

    New Member
    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.
     
Loading...

Share This Page