Solved

Search for Email Subject String

Posted on 2009-04-07
7
746 Views
Last Modified: 2013-11-27
Hello - This code is working perfectly, but I have run into a situation where I need to search for specific text in the subject.  I have not been able to figure out how to fit that into this existing code.  Can you please help me update this code to search for a text string.

I need to find "EverBank" within the subject text.
Dim olApp, objFolder, objNameSpace

    Dim objItems, objMess

    Dim strFilter As String

    Dim Regex As Object, regm As Object, IE As Object

    Dim ieLink, i As Long

    strFilter = "[subject] = ""EverBank Rates"""
 

 

    Set olApp = CreateObject("Outlook.Application")

    Set objNameSpace = olApp.GetNamespace("MAPI")

    Set objFolder = objNameSpace.GetDefaultFolder(6)

    objFolder.Items.Sort "[Received]", False

    Set objItems = objFolder.Items

    objItems.Sort "[Received]", True

    Set objMess = objItems.Find(strFilter)

 

    If objMess Is Nothing Then

        MsgBox "No email found!", vbCritical

        Exit Sub

    End If

 

    Set Regex = CreateObject("vbscript.regexp")

    Regex.Pattern = "((file?|ftp|gopher|telnet|file|notes|ms-help):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\.&]*.pdf)"

    Regex.Global = True

    If Regex.test(objMess.body) Then

        Set regm = Regex.Execute(objMess.body)

       Forms("frmPricingLoadExternal")!txtFileLoc.Value = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "*"))  '& "?download=yes&cpw="

    End If

    Set olApp = Nothing

    Set Regex = Nothing

Open in new window

0
Comment
Question by:rsburge
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24092959
Hi again,
So 'EverBank Rates' is only part of the Subject?
As the current code will find 'EverBank Rates' if it is the full subject
Cheers
Dave
0
 

Author Comment

by:rsburge
ID: 24093133
Hi Dave.  Thank you for looking at this question.  Yes, EverBank Rates is only part of the subject and it can be anywhere in the subject (for some reason it is never in the same place).  Some examples are... EverBank Rates for 04072009, Today's EverBank Rates, Your EverBank Rates, Current Pricing - EverBank Rates, Wholesale EverBank Rates for 04/03/09, and tomorrow it might be different again.  :)
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 24093450
try this
Cheers
Dave



Sub take2()

Const strF As String = "urn:schemas:mailheader:subject like '%EverBank Rates%'"

    Dim olApp, objFolder, objNameSpace

    Dim oSrh As Search, rsts As Results

    Dim objItems, objMess As MailItem

    Dim Regex As Object, regm As Object, IE As Object

    Dim ieLink, i As Long
 
 

    Set olApp = CreateObject("Outlook.Application")

    Set objNameSpace = olApp.GetNamespace("MAPI")

    Set objFolder = objNameSpace.GetDefaultFolder(6)

    objFolder.Items.Sort "[Received]", False

    Set objItems = objFolder.Items

    objItems.Sort "[Received]", True

  
 

    Set oSrh = Application.AdvancedSearch("Inbox", strF)

    Set rsts = oSrh.Results
 

    If rsts Is Nothing Then

        MsgBox "No email found!", vbCritical

        Exit Sub

    End If
 

    Set objMess = rsts.GetFirst
 

    Set Regex = CreateObject("vbscript.regexp")

    Regex.Pattern = "((file?|ftp|gopher|telnet|file|notes|ms-help):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\.&]*.pdf)"

    Regex.Global = True

    If Regex.test(objMess.Body) Then

        Set regm = Regex.Execute(objMess.Body)

         Forms("frmPricingLoadExternal")!txtFileLoc.Value = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "*"))  '& "?download=yes&cpw="

    End If

    Set olApp = Nothing

    Set Regex = Nothing

End Sub

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:rsburge
ID: 24093522
Thank you Dave!  This is awesome.  I had to make one minor adjustment...  I changed the following

from
    Set oSrh = Application.AdvancedSearch("Inbox", strF)

to
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
0
 

Author Closing Comment

by:rsburge
ID: 31567817
Thanks again!  I really appreciate all of your help!  Renee
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24093533
thx for the grade Renee :)
Good pick up on the Application naming
Cheers
Dave
0
 

Author Comment

by:rsburge
ID: 24093654
You're welcome; well earned as always.

While I still have a million questions, I am learning.  :)

Have a wonderful day!

Renee
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now