0

I get thousands of e-mail alerts in my inbox daily, but many of them are actually trivial. I want to check whether the text of these alerts contains numbers below a certain threshold; if the numbers are lower than that threshold, alert me to the message and display a message box.

I'm using Outlook 2010 and have found several tutorials on writing Outlook macros in VB, including one about programmatically creating a rule to move messages to different folders.

But I don't want to check for keywords, I want to check if a number in the message (subject field) text is = to or less than a threshold value. For example, if the text of a message contains the following, id be alerted to it and a message box is displayed:-

enter image description here

The bit I need help with is there any way of writing a code that will only call the message box if the value in the e-mail subject field is below 45kohm? I can do this in the rule but I’d have to include all values below i.e. 39.99, 39.98, 39.97 and that’s far too long!

Mike G
  • 4,232
  • 9
  • 40
  • 66
ER101
  • 1
  • 1

1 Answers1

0

You could use a VBA macro similar to the following:

Sub SubjectCheckerMessageRule(newMail As Outlook.mailItem)
    '  "script" routine to be called for incoming mail messages
    '  This subroutine has to be linked to this mail type using Outlook's rule assistant
    Dim EntryID As String
    Dim StoreID As Variant
    Dim mi As Outlook.mailItem
    Dim s As String
    Dim x As Double
    Const Prefix = "Resistance,"
    Const Threshold = 40.0  ' or is it 45.0

    '  http://www.vboffice.net/en/developers/security-model-part-1/
    '  In 2003, not 2007 or later
    '  we have to access the new mail via an application reference
    '  to avoid security warnings
    ' 
    EntryID = newMail.EntryID
    StoreID = newMail.Parent.StoreID

    Set mi = Application.Session.GetItemFromID(EntryID, StoreID)

    If InStr(mi.Subject, Prefix) = 1 Then
        s = Mid(mi.Subject, Len(Prefix) + 1)
        If IsNumeric(s) Then
            x = CDbl(s)

            If x <= Threshold Then
                MsgBox x & " <= " & Threshold, vbInformation, "Attention!"
            End If
        End If
    End If

End Sub

Use Outlook's Rule Assistant to define this macro as Script for the incoming mails of interest. Define keywords for the subject like "Resistance," to make sure that the macro is only called for the relevant mails. Add some error checking.

Community
  • 1
  • 1
Axel Kemper
  • 10,544
  • 2
  • 31
  • 54
  • Axel, many thanks for this. I'm still new to VBA and must admit I'm a bit confused with the above, but I will give it a shot and see how it goes. Thank you. – ER101 Jan 25 '16 at 23:10
  • There is no need for mi. You could use newMail.Subject directly instead. It will not trigger a security message as the code is inside Outlook not outside. – niton Jan 26 '16 at 00:19
  • The Prefix is near the end of the subject not in position 1. This line has to be revised - If InStr(mi.Subject, Prefix) = 1 – niton Jan 26 '16 at 00:24
  • Hi niton thanks for the additional info. How would I edit the line of code you've highlighted? The subjects are not always the same either, the only thing that remains constant in the subject field is the feeder value always follows 'Resistance'. I.e. Resistance, 30.99. Any help much appreciated, – ER101 Jan 26 '16 at 00:41