Solved

VBA Code for Advanced Find in Outlook

Posted on 2012-03-15
21
3,597 Views
Last Modified: 2012-05-17
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
Comment
Question by:Senniger1
  • 8
  • 5
  • 5
  • +1
21 Comments
 
LVL 6

Expert Comment

by:wshark83
ID: 37724767
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
 

Author Comment

by:Senniger1
ID: 37725013
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
 
LVL 6

Expert Comment

by:wshark83
ID: 37730025
Const strF As String = "urn:schemas:httpmail:subject = 'Corp'"

will do that you can change it to '(Corp'
0
 

Author Comment

by:Senniger1
ID: 37737270
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
 
LVL 6

Accepted Solution

by:
wshark83 earned 250 total points
ID: 37737883
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
 

Author Comment

by:Senniger1
ID: 37742001
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
 
LVL 6

Expert Comment

by:wshark83
ID: 37742709
ok try changing line 4 to:

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

and commenting out line 6
0
 

Author Comment

by:Senniger1
ID: 37743992
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
 
LVL 6

Expert Comment

by:wshark83
ID: 37746542
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Senniger1
ID: 37746940
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
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 250 total points
ID: 37780287
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
 

Author Comment

by:Senniger1
ID: 37795767
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37795806
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37795890
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
 

Author Comment

by:Senniger1
ID: 37796768
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37797529
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
 

Author Comment

by:Senniger1
ID: 37842562
Thank you anyway.  I do appreciate your time.

Can anyone else assist me in this matter?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37843563
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37979132
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

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

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 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