Help retrieving text after InStr 1

[scooby99]

Hi,

I have the following code for reading and searching for string in saved .msg files:

Set ol = CreateObject("Outlook.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Dim strSearchFor

strSearchFor = "Ex date:"

For Each f In fso.GetFolder("\Test mail").Files
If LCase(fso.GetExtensionName(f)) = "msg" Then
Set msg = ol.CreateItemFromTemplate(f.Path)
If InStr(msg.Body, strSearchFor) <> 0 then
RETRIEVE TEXT AFTER strSearchFor

End If
Else
End If

Next

The code works fine finding the strSearchFor. However, I want to retrieve the following text after that is on the same row.

Example

Ex date: 06/30/2017

I want the code to return the date.

Any help is appreaciated.

 

[guitarzan]

The best way might be a regular expression, but i'm not good with those so I will leave that to others. Several other ways to do it, here is one:

IsStr() will return the position it finds the text at, so you can use that information plus the Mid() function to extract the date:

strSearchFor = "Ex date:" 
...
Dim iPos, iDateStartPos, iDateEndPos, sDate
iPos = InStr(msg.Body, strSearchFor)
If iPos <> 0 Then
   iDateStartPos = iPos + 9 [COLOR=#4E9A06]'the position of the date starts after "Ex date: ", which is 9 characters[/color]
   iDateEndPos = InStr(iDateStartPos, msg.Body, " ") [COLOR=#4E9A06]'The position of the date ends at the 1st space after the date[/color]
   sDate = Mid(msg.Body, iDateStartPos, iDateEndPos - iDateStartPos)
End If

 

[scooby99]

That works great! Thanks for your help