Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4396
  • Last Modified:

VBA Code for Advanced Find in Outlook

I'm currently using Outlook 2010, but my question should work for Outlook 2003.

I need the VBA code for an ADVANCED FIND in Outlook for TASKS where SUBJECT CONTAINS "(Corp".  See attachment for a screenshot of my search.

I'm thinking I would use the following and I believe I can cover the scope, but I don't know how to enter the FILTER part.  Also I don't think I would need to search sub folders.

Set objSearch = Application.AdvancedSearch(Scope, Filter, SearchSubFolders, Tag)

Can anyone help me with this code.

Thanks in advance!
AdvanceFind.jpg
0
Senniger1
Asked:
Senniger1
  • 8
  • 5
  • 5
  • +1
2 Solutions
 
wshark83Commented:
try something like the code below from --> http://msdn.microsoft.com/en-us/library/aa220071(v=office.11).aspx:

Public sch As Outlook.Search

Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
    Dim rsts As Outlook.Results
    If (SearchObject.Tag = "Search1") Then
        Set rsts = sch.Results
        MsgBox "Search1 returned " & rsts.Count & " items"
   
    End If
End Sub

Sub TestAdvancedSearchComplete()
    Dim rsts As Outlook.Results
    Dim i As Integer
    Const strF As String = "urn:schemas:httpmail:subject = 'Corp'"
    Const strS As String = "Tasks"
    Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
End Sub

alternatively and app is already developed here http://soroush.secproject.com/blog/projects/exceladvancedsearchapplication/
0
 
Senniger1Author Commented:
Forgive me for asking, but what part of your code covers the CONTAINS part of my request.

I need where SUBJECT CONTAINS the text (Corp

Thanks!
0
 
wshark83Commented:
Const strF As String = "urn:schemas:httpmail:subject = 'Corp'"

will do that you can change it to '(Corp'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Senniger1Author Commented:
I admit I'm limited in my knowledge here, but you codes looks like you have the subject equaling  '(Corp' and I need the subject to contain  '(Corp'.

Doesn't the part "subject =  '(Corp' indicate equals or does this also cover CONTAINS.
0
 
wshark83Commented:
apologies my bad - change the code to be soemthing like this:

Sub TestAdvancedSearchComplete()
    Dim rsts As Outlook.Results
    Dim i As Integer
    Const strF As String
    Const strS As String = "Tasks"
    strF = InStr(lcase(urn:schemas:httpmail:subject), "(corp")
    Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
End Sub
0
 
Senniger1Author Commented:
Thanks for your help so far.  I think we're heading in the right direction.

In running your code I get a SYNTAX ERROR on lines 4 and 6 below.

Line 1  Sub TestAdvancedSearchComplete()
Line 2    Dim rsts As Outlook.Results
Line 3    Dim i As Integer
Line 4    Const strF As String   (SYNTAX ERROR)
Line 5    Const strS As String = "Tasks"
Line 6    strF = InStr(lcase(urn:schemas:httpmail:subject), "(corp")   (SYNTAX ERROR)
Line 7    Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
Line 8  End Sub

Please advise, thanks!
0
 
wshark83Commented:
ok try changing line 4 to:

Const strF As String  = InStr(lcase(urn:schemas:httpmail:subject), "(corp")

and commenting out line 6
0
 
Senniger1Author Commented:
Now I get errors on Line 4 and Line 6 (see below).

Line 1   Sub TestAdvancedSearchComplete()
Line 2     Dim rsts As Outlook.Results
Line 3     Dim i As Integer
Line 4     Const strF As String = InStr(LCase$("urn:schemas:httpmail:subject"), "(corp") (COMPILE ERROR: CONSTANT EXPRESSION REQUIRED)
Line 5     Const strS As String = "Tasks"
Line 6     Set sch = Application.AdvancedSearch(strS, strS, , "Search1")  (SCH - Variable not defined)
Line 7   End Sub

Thanks!
0
 
wshark83Commented:
try this.. can you provide me with your sample file?

Sub TestAdvancedSearchComplete()
    Dim rsts As Outlook.Results
    Dim i As Integer
    Const strF As String = "InStr(lcase(urn:schemas:httpmail:subject)),'(corp'"
    Const strS As String = "Tasks"
    Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
End Sub
0
 
Senniger1Author Commented:
I've provided the files you requested.  Here's everything you've given me so far including the changes.

I put this in a Class Module...
  Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
    Dim rsts As Outlook.Results
    If (SearchObject.Tag = "Search1") Then
        Set rsts = sch.Results
        MsgBox "Search1 returned " & rsts.Count & " items"
    End If
  End Sub

This part is the macro I run...
  Sub TestAdvancedSearchComplete()
    Dim sch As Outlook.Search
    Dim rsts As Outlook.Results
    Dim i As Integer
    Const strF As String = "InStr(lcase(urn:schemas:httpmail:subject)),'(corp'"
    Const strS As String = "Tasks"
    Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
  End Sub

I'm getting a "Variable not defined on the following line of your code...
   Set sch = Application.AdvancedSearch(strS, strF, , "Search1")
So I added adding the following:
   Dim sch As Outlook.Search

When running "TestAdvancedSearchComplete" I am still getting a Run-time error:  The operation failed.  When I debug it and hover over the "sch" it indicated "sch = Nothing".

I sincerely appreciate your help!
Module1.bas
Class1.cls
0
 
Chris BottomleyCommented:
You specifically asked for advanced search but the following may be helpful ... it is a filter on items and the collection filteredItems has every item that matches the wildcard corp, ("%") being the wildcard for filter queries.

Sub taskFilterCorp()
Dim olFolder As Object
Dim filteredItems As Object
Dim folderItems As Object
Dim sorteditems As Object
Dim strFilter As String
Dim itm As Object

    Set olFolder = Application.Session.GetDefaultFolder(olFolderTasks)
    Set folderItems = olFolder.Items
    strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like " & "'%" & "corp" & "%'"
    Set filteredItems = olFolder.Items.Restrict(strFilter)
    
    If filteredItems.Count = 0 Then
        MsgBox "No such tasks found"
    Else
        For Each itm In filteredItems
            Debug.Print itm.Subject
        Next
    End If

End Sub

Open in new window


Chris
0
 
Senniger1Author Commented:
I'm open for another way to handle my request so I tried your filter.  

There were no errors and I believe it worked, but nothing really happened.  I didn't get a results window with the results of my search.

What should be happening after I run this code?

Thanks so much!
0
 
Chris BottomleyCommented:
For the moment, (for the simplicity) the outputs are directed to the immediate window of the VBE, (ctrl + G) to display it.

Once you are happy with the functionality we can normally redirect the data in VBA wherever you want it.

Chris
0
 
Chris BottomleyCommented:
The same code but this time to use a msgbox is ...

Sub taskFilterCorp()
Dim olFolder As Object
Dim filteredItems As Object
Dim folderItems As Object
Dim sorteditems As Object
Dim strFilter As String
Dim itm As Object
Dim str As String

    Set olFolder = Application.Session.GetDefaultFolder(olFolderTasks)
    Set folderItems = olFolder.Items
    strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like " & "'%" & "corp" & "%'"
    Set filteredItems = olFolder.Items.Restrict(strFilter)
    
    If filteredItems.Count = 0 Then
        str = "No such tasks found"
    Else
        For Each itm In filteredItems
            str = str & itm.Subject & vbCrLf
        Next
    End If
    MsgBox str

End Sub

Open in new window

0
 
Senniger1Author Commented:
Okay the output is absulutely perfect.  I just now need the output to be in a filtered tasklist or something so they can open the tasks as they need.

Again, thank you so much!
0
 
Chris BottomleyCommented:
Okay, what I didn't make clear is this code makes a selection for use within VBA rather than a displayed collection in the explorer ... that brings you back to the advanced search and I apologise for the distraction.

Chris
0
 
Senniger1Author Commented:
Thank you anyway.  I do appreciate your time.

Can anyone else assist me in this matter?
0
 
Chris BottomleyCommented:
Hmmm, I have assumed the help you were receiving on  advanced search would meet your needs.  Given the lack of progress there I implemented it on a pc and found that like filters that I went on about the advanced search returns a collection for dynamic analysis BUT does not modify the displayed items in the explorer.  The wildcard element was via:

    urn:schemas:httpmail:subject" LIKE '%corp%'

If you do require the explorer display to reflect the selected items then it can be done via the filter mechanism within outlook ... with a bit of work as it isn't quite so straightforward as a collection within VBA.

For an idea of where we will have to go to implement the DASL filter then see the solution to:

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_25904435.html

Chris
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 8
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now