INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help retrieving text after InStr

Help retrieving text after InStr

(OP)
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.

RE: Help retrieving text after InStr

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:

CODE

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

RE: Help retrieving text after InStr

(OP)
That works great! Thanks for your help :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close