Solved

Search for Email Subject String

Posted on 2009-04-07
7
745 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rsburge
Comment Utility
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
Comment Utility
Thanks again!  I really appreciate all of your help!  Renee
0
 
LVL 50

Expert Comment

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

Author Comment

by:rsburge
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

762 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

11 Experts available now in Live!

Get 1:1 Help Now