VBA Regular Expression

Question

I am trying to cycle through an email body and forward an email on to a different inbox. I have my Regex matching any 4-6 character number with white spaces before and after, But the problem is the date is included in the email so it is picking up the four character number "2017" also. Is what is the Regex to omit "2017" and just take all the other 4-6 character numbers. Here is my code.

Option Explicit
Public Sub Forward(Item As Outlook.MailItem)
    Dim M1 As MatchCollection
    Dim M As Match
    Dim Reg1 As Object
    Dim myForward As Object

    Set Reg1 = New RegExp

    With Reg1
        .Pattern = "(\s[0-9]{4,6}\s)"
        .Global = True
    End With

    If Reg1.Test(Item.Body) Then
        Set M1 = Reg1.Execute(Item.Body)
        For Each M In M1
            Debug.Print M.SubMatches(0) ' Immediate Window

            '// allows for multiple matches in the message body
            Item.Subject = M.SubMatches(0) & "; " & Item.Subject

        Next
    End If

    Item.Save

    Set myForward = Item.Forward
    myForward.Recipients.Add "xxxxx@gmail.com"
    myForward.Display
End Sub

Here is my output in the subject of my new email I am forwarding.
SPORT - , 2017 SPORT - , 2017 SPORT - 5556 I just want to be able to capture the "SPORT - 5556"

Answer 1

You can use a negative lookahead (see MSDN: "Regular Expression Syntax (Scripting)") to exclude certain strings from a match.

Here is how I would write this:

Option Explicit

Public Sub Forward(Item As Outlook.MailItem)
    Dim DigitsExp As New RegExp
    Dim Matches As MatchCollection, Match As Match

    If Item Is Nothing Then Exit Sub

    DigitsExp.Pattern = "\s(?!2017\s)([0-9]{4,6})\s"
    DigitsExp.Global = True

    Set Matches = DigitsExp.Execute(Item.Body)
    For Each Match in Matches
        Debug.Print Match.SubMatches(0)
        Item.Subject = Match.SubMatches(0) & "; " & Item.Subject
    Next
    If Not Item.Saved Then Item.Save

    With Item.Forward
        .Recipients.Add "xxxxx@gmail.com"
        .Display
    End With
End Sub

Where \s(?!2017\s)([0-9]{4,6})\s matches any 4-6 digit substring that is not 2017 and (?!2017\s) is the negative lookahead that excludes it.