Used advanced search from Outlook

M

Michael

I am trying to get Outlook to search through our email for a subject
based on what is in certain cells in an outlook spreadsheet. Here is
the code I am using:

Sub SearchEmail()

dim "OrderNumber" As string

Dim objSch As Outlook.Search
Dim rsts As Outlook.Results
Dim i As Integer
blnSearchComp = False
Const strF1 As String = "urn:schemas:mailheader:subject =
'OrderNumber'"
Const strS1 As String = "Inbox"
Set objSch = _
Application.AdvancedSearch(Scope:=strS1, Filter:=strF1,
SearchSubFolders:=True, Tag:="SubjectSearch")
While blnSearchComp = False
DoEvents
Wend
Set rsts = objSch.Results
For i = 1 To rsts.Count
MsgBox rsts.Item(i).SenderName
Next



End Sub

The error that I am getting says, "Object doesn't support this property
or method."

And the line

Set objSch = _
Application.AdvancedSearch(Scope:=strS1, Filter:=strF1,
SearchSubFolders:=True, Tag:="SubjectSearch")

Is highlighted.

ANY HELP AT ALL IS GREATLY APPRECIATED. THANK YOU.
 
S

Sue Mosher [MVP-Outlook]

Is this code running in Outlook VBA? What version of Outlook?

Why not use Restrict if you're searching for an exact subject?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
M

Michael

It is running from excel.

Michael
Is this code running in Outlook VBA? What version of Outlook?

Why not use Restrict if you're searching for an exact subject?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
Click to expand...
 
M

Michael

I'm not searching for an exact subject. Just emails that contain the
same text present in certain cells in the spreadsheet.

Michael
 
S

Sue Mosher [MVP-Outlook]

Since this code is running from Excel, Application refers to the Excel.Application object intrinsic to Excel VBA. You need to instantiate an Outlook.Application object and use it instead.

Furthermore, if you want to look for messages that contain text that comes from some other source, you don't use

Const strF1 As String = "urn:schemas:mailheader:subject = 'OrderNumber'"

which searches for the literal string 'OrderNumber' (and won't work anyway because it's missing the required quotation marks around the schema property).

Instead, you build a search string like this:

strF1 = Chr(34) & "urn:schemas:httpmail:textdescription" & _
Chr(34) & " LIKE " & Chr(39) & "%" & _
strExcelText & "%" & Chr(39)

where strExcelText is the text you extracted from the worksheet.

Did you ever say what Outlook version you're using? You know, I hope, that AdvancedSearch was added in Outlook 2002.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
S

Sue Mosher [MVP-Outlook]

So did you try what I suggested with regard to the Application object?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
M

Michael

Ok, I have edited the code to look like this:

Sub SearchEmail()

Dim myolApp As Outlook.Application
Set myolApp = CreateObject("Outlook.Application")
Set myNameSpace = myolApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
'myFolder.Display


Dim objSch As Outlook.Search
Dim rsts As Outlook.Results
Dim i As Integer
blnSearchComp = False

Dim strExcelText As String
strExcelText = "Backlog"


'Const strF1 As String = "urn:schemas:mailheader:subject =
'backlog'"
strF1 = Chr(34) & "urn:schemas:httpmail:textdesc­ription" & _
Chr(34) & " LIKE " & Chr(39) & "%" & _
strExcelText & "%" & Chr(39)


Const strS1 As String = "Inbox"

Set objSch = _
myolApp.Application.AdvancedSearch(Scope:=strS1, Filter:=strF1,
SearchSubFolders:=True, Tag:="SubjectSearch")
While blnSearchComp = False
DoEvents
Wend
Set rsts = objSch.Results
For i = 1 To rsts.Count
MsgBox rsts.Item(i).SenderName
Next



End Sub

I get an error that says "Operation Failed." Can anyone help?
 
M

Michael

I thought I did???
So did you try what I suggested with regard to the Application object?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

Which statement raises that error? Is Outlook already running when you run this code?

Note that this loop will never end:

While blnSearchComp = False
DoEvents
Wend

because nowhere do you set blnSearchComp to True. The more conventional way of handling searches (detailed in the MSDN article on the subject) is to build event handlers for Application.AdvancedSearchComplete and Application.AdvancedSearchStopped.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Ok, I have edited the code to look like this:

Sub SearchEmail()

Dim myolApp As Outlook.Application
Set myolApp = CreateObject("Outlook.Application")
Set myNameSpace = myolApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
'myFolder.Display


Dim objSch As Outlook.Search
Dim rsts As Outlook.Results
Dim i As Integer
blnSearchComp = False

Dim strExcelText As String
strExcelText = "Backlog"


'Const strF1 As String = "urn:schemas:mailheader:subject =
'backlog'"
strF1 = Chr(34) & "urn:schemas:httpmail:textdesc­ription" & _
Chr(34) & " LIKE " & Chr(39) & "%" & _
strExcelText & "%" & Chr(39)


Const strS1 As String = "Inbox"

Set objSch = _
myolApp.Application.AdvancedSearch(Scope:=strS1, Filter:=strF1,
SearchSubFolders:=True, Tag:="SubjectSearch")
While blnSearchComp = False
DoEvents
Wend
Set rsts = objSch.Results
For i = 1 To rsts.Count
MsgBox rsts.Item(i).SenderName
Next



End Sub

I get an error that says "Operation Failed." Can anyone help?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search folders 0
Problem with AdvancedSearch method 15
Explanation on weird VB bahaviour W/O a comment 2
Advanced Search Behavior 1
search folder in other pst 3
AdvancedSearch behavior 6
Use of AdvancedSearch with wildcards in Outlook 15
Search, Results and modifying subject 2

Top