How to make this regex work in VBA?

[prabha_friend]

Dim MyRegExp As RegExp
Set MyRegExp = New RegExp
With MyRegExp
    .Pattern = "(?<=Your message wasn't delivered to )(.*)(?= because)"
    Cells.SpecialCells(xlCellTypeLastCell).Offset(1, -1).Value = MyRegExp.Execute(Returned_Mail.Body)
End With

 

[MajP]

The execute method returns a matchcollection which is a collection of the matched strings. Could be more than one. At a minimum you probably need and index, but I would verify that a match was made. Maybe something like

Dim MyRegExp As RegExp
Dim MyMatches as MatchCollection
Set MyRegExp = New RegExp

MyRegExp.Pattern = "(?<=Your message wasn't delivered to )(.*)(?= because)"
Set MyMatches = MyRegExp.Execute(Returned_Mail.Body)

If myMatches.count > 0 then
Cells.SpecialCells(xlCellTypeLastCell).Offset(1, -1).Value = myMatches(1)
end if

 

[prabha_friend]

Hi Maj,
Thanks for your reply. But I am getting the following Error:

Run-time error '5017':
Method 'Execute' of object 'IRegExp2' Failed.

What to do? Please Help.

With Hope,
Prabhakaran

 

[prabha_friend]

Especially on the Below line of Code:

Set MyMatches = MyRegExp.Execute(Returned_Mail.Body)

 

[prabha_friend]

So, Can we conclude that using RegularExpressions we can't do a "between String" Match?

 

[MajP]

PM @arnelgp he seems to be the guru here on regexp. I kind of stumble my way through.