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 "[email protected]"
    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"

Saying "compile error: Expected: = "

BoostedMonkey
– BoostedMonkey

2017-01-20 13:42:40 +00:00
Commented Jan 20, 2017 at 13:42 — BoostedMonkey
– BoostedMonkey, Commented Jan 20, 2017 at 13:42

Tomalak · Accepted Answer · 2017-01-20 14:11:48Z

2

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 "[email protected]"
        .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.

edited Jan 20, 2017 at 14:11

answered Jan 20, 2017 at 13:56

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Regular Expression

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related