none
Outlook script does not run as part of rule? RRS feed

  • Question

  • Hello,

    I have seen this question posted before and have yet to come across an answer for it (at least not one that worked).

    This script is for Outlook 2013.

    The script works both run manually from the VBA editor and when the rule is run manually from the Manage Rules and alerts dialog.

    All macros enabled including add-ins.

    I have seen it suggested that since my script depends on a string in the body of the email I need a pause of some sort to make sure the body has downloaded first.

    I can't seem to get sleep or Application.wait to work and I can seem to wrap my head around SyncObject.SyncEnd Event.

    Any suggestions?

    Thanks in advance.

    Code placed under ThisOutlookSession:

                                                                   
    Public Sub PreBuild_Nomad(Item As Outlook.MailItem)
                Item.Subject = Item.Subject & " " & QueryCustomer(RegExCheck(Item))
                Item.HTMLBody = "<font size = 12px>" & QueryCustomer(RegExCheck(Item)) & "</font><br>" & Item.HTMLBody
                Item.Save
    End Sub
    
    Private Function RegExCheck(msg As Outlook.MailItem) As String
    
        Dim Reg1 As RegExp
        Dim M1 As MatchCollection
        Dim M As Match
        Dim strSubject As String
    
        'Set Regex pattern to look for
        Set Reg1 = New RegExp
        Reg1.Pattern = "(\d{10})"
        Reg1.Global = False
    
            'Check mesage body for 10 digit number and assign the 1st one found to strSubject variable
            If Reg1.Test(msg.Body) Then
                Set M1 = Reg1.Execute(msg.Body)
    
                For Each M In M1
                    strSubject = M.SubMatches(0)
                    strSubject = Replace(strSubject, Chr(13), "")
    
                Next
            End If
    
        Set Reg1 = Nothing
    
        'Return SCN string
        RegExCheck = strSubject
    
    End Function
    
    
    Function QueryCustomer(SCN As String)
    
    Dim x As Integer
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim ConnectionString As String
    Dim StrQuery As String
    
            ConnectionString ="LEFT BLANK FOR SECURITY"
    
    
            cnn.Open ConnectionString
            cnn.CommandTimeout = 900
            StrQuery = "SELECT Companyname from some_table where some_table.scn = '" & SCN & "'"
            StrQuery1 = "SELECT AMSName from some_table where some_table.scn = '" & SCN & "'"
            
        On Error Resume Next
        rst.Open StrQuery, cnn
        CustomerName = rst.GetString(adClipString, 1)
        rst.Close
        rst.Open StrQuery1, cnn
        AMSName = rst.GetString(adClipString, 1)
        rst.Close
     
        QueryCustomer = CustomerName & " | " & AMSName & " "
            
     End Function



    • Edited by J-King Tuesday, May 10, 2016 10:35 PM
    Monday, May 9, 2016 10:55 PM

All replies

  • Hi J-King,

    Since I didn't have the database environment, I modify the code like below for testing:

    Function QueryCustomer(SCN As String)
    QueryCustomer = SCN
    
            
     End Function

    And there is the steps for the testing:
    1. create a new rule to run the script when the message arriving like below:

    2. Create a message, type 10 digits in the body and send it to myself

    3. the script run successfully to add the digits to the subject and insert it to the body

    I am testing in Outlook 2013, would you minding share more detail to help us to reproduce this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 11, 2016 8:31 AM
    Moderator
  • Hi Fei Xue,

    I modified the code same as you did and sent my self test messages. That worked.

    While gathering additional information to post here I found my problem although it doesn't make sense to me.

    Assigning a category as part of the rule prevents the script from running for some reason.

    I'm content with this solution for now; although, I would be interested to know why assigning a category prevents the script from running.

    Thank you for the assistance!

    Wednesday, May 11, 2016 9:01 PM
  • Hi J-King,

    I am glad you have found the solution for your issue.

    >> I would be interested to know why assigning a category prevents the script from running.

    I made a test about creating rule with assigned to Blue Category category, this rule would not fire even through you send to this email with Blue Category. I assume that category in the send to email is different from the send from email event through they used the same name Blue Category. So, I think your code did not run is caused by that the incoming email did not meet the rule.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 16, 2016 7:17 AM
  • Hi Edward-

    The rule I had created would run and the category would be assigned.  The only part of the rule that wasn't working was the script.  My assumption is that if you have a script then you can not have any additional actions.  Although I haven't verified that to be the case.  This is how my rule looked before removing the category assignment.  While it is less than ideal I could always create a second rule to handle assigning the category.

    I apparently can't post pictures yet.

    Rule:

    Apply this rule after the message arrives

    from specific email address

    and with Specific words in the subject

    and on this computer only

    assign it to the Pre-Build category

    and run Project1.ThisOutlookSession.PreBuild Nomad 

    except if the subject contains | or RE:


    Thanks,

    J-King

    Monday, May 16, 2016 9:28 PM
  • Hi J-King,

    Do you mean if your rule has "and run Project1.ThisOutlookSession.PreBuild Nomad ", it will not work, and if you remove "and run Project1.ThisOutlookSession.PreBuild Nomad ", your rule will work correctly?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 17, 2016 7:04 AM
  • Hi Edward,

    Yes, and no.

    With the rule the way I have it above.  The rule runs and applies a category but the script does not run.  Removing the assign category the script does run as part of the rule.  Removing the run script of course also works but since the script is the primary reason to create the rule that would not be useful.

    As I mentioned if I split the rule into 2 rules they will both run without issue.  One will apply the category and the other will run the script that checks the email body, queries a database and appends info returned from the database to the subject and body of the email.

    At this point it is more out of curiosity than necessity to find out why the script and the assign category don't work in a single rule.

    Thanks,

    J-King

    Tuesday, May 17, 2016 4:52 PM
  • Hi J-King,

    >>  Removing the run script of course also works but since the script is the primary reason to create the rule that would not be useful.

    Based on my test, the script also works well when I assign the rule like figure below:

    The Script:

    Public Sub PreBuild_Nomad(Item As Outlook.MailItem)
                Item.Subject = Item.Subject & " " & QueryCustomer(RegExCheck(Item))
                Item.HTMLBody = "<font size = 12px>" & QueryCustomer(RegExCheck(Item)) & "</font><br>" & Item.HTMLBody
                Item.Save
    End Sub
    
    Private Function RegExCheck(msg As Outlook.MailItem) As String
    
        Dim Reg1 As RegExp
        Dim M1 As MatchCollection
        Dim M As Match
        Dim strSubject As String
    
        'Set Regex pattern to look for
        Set Reg1 = New RegExp
        Reg1.Pattern = "(\d{10})"
        Reg1.Global = False
    
            'Check mesage body for 10 digit number and assign the 1st one found to strSubject variable
            If Reg1.test(msg.Body) Then
                Set M1 = Reg1.Execute(msg.Body)
    
                For Each M In M1
                    strSubject = M.SubMatches(0)
                    strSubject = Replace(strSubject, Chr(13), "")
    
                Next
            End If
    
        Set Reg1 = Nothing
    
        'Return SCN string
        RegExCheck = strSubject
    
    End Function
    
    
    Function QueryCustomer(SCN As String)
    QueryCustomer = SCN
    'Dim x As Integer
    'Dim cnn As New ADODB.Connection
    'Dim rst As New ADODB.Recordset
    'Dim ConnectionString As String
    'Dim StrQuery As String
    '
    '        ConnectionString = "LEFT BLANK FOR SECURITY"
    '
    '
    '        cnn.Open ConnectionString
    '        cnn.CommandTimeout = 900
    '        StrQuery = "SELECT Companyname from some_table where some_table.scn = '" & SCN & "'"
    '        StrQuery1 = "SELECT AMSName from some_table where some_table.scn = '" & SCN & "'"
    '
    '    On Error Resume Next
    '    rst.Open StrQuery, cnn
    '    CustomerName = rst.GetString(adClipString, 1)
    '    rst.Close
    '    rst.Open StrQuery1, cnn
    '    AMSName = rst.GetString(adClipString, 1)
    '    rst.Close
    '
    '    QueryCustomer = CustomerName & " | " & AMSName & " "
            
     End Function
    

    Did I misunderstood?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 19, 2016 12:09 PM
    Moderator
  • Hi Fei Xue,

    There must be something about my environment that is the issue.

    The only thing I can see that is different from your rule example is my script is part of 'ThisOutlookSession'.

    When I have a free minute I'll try moving my script and see if that does anything.

    Thanks for the help figuring out how to work around the issue.

    Thursday, May 19, 2016 5:49 PM
  • Hi J-King,

    >>The only thing I can see that is different from your rule example is my script is part of 'ThisOutlookSession'.

    I also test the by coping the script into the 'ThisOutlookSesstion'. It also works well for me. I am testing using the Outlook 2013 15.0.4823.1000.

    I suggest that you try to update/repair the Outlook to see whether the issue wax fixed.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 25, 2016 8:44 AM
    Moderator