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

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

instant search through vba/vb in outlook 2007

Is there a way I can utilize the instant search utility from within VBA? I'm looking to do a fast search across all folders and it seems to me to be the easiest and quickest way.  And assuming I can do that somehow, how do I access the search results?

until now, I have been using the entry ID for searching Outlook from a database program, which is very fast and been working well.  I need to find an alternative, though, and was hoping that I can do it through this utility.

Thanks,

Eyal
0
eyalyaari
Asked:
eyalyaari
  • 11
  • 11
1 Solution
 
David LeeCommented:
Hi, Eyal.

I can help with this.  What do you want to search for and what sort of output do you want?
0
 
eyalyaariAuthor Commented:
Great!

the customer would like to be able to click on an e-mail in a database duplicate of Outlook and have the e-mail open up in Outlook so that they can then reply forward etc.

So what I was thinking is to use the combination of the subject and the received date of the e-mail as the unique identifiers for the e-mail. Then, if I could do a fast search using those parameters in Outlook, I could then open up the e-mail in Outlook.  

The customer is completely anal though and stores all of his incoming and outgoing e-mails in 100's of different folders all under his personal folders.  That's why I liked the instant search. It has an option for "all mail items" and it is really fast ( the customer has about 20,000 pieces of e-mail and the search needs to find it within a second or two for it to be realistically practical to use).  The one thing I didn't see in the instant search though is the ability to search by date/time.  The closest thing I saw was a drop down for: today, yesterday, this week, last week, etc. which is not good enough.

Will the advanced search method be able to search across all mail items and do it as quickly as instant search (which uses indexes)?

Thanks,

Eyal
0
 
David LeeCommented:
"the customer would like to be able to click on an e-mail in a database duplicate of Outlook and have the e-mail open up in Outlook"
You'd be better served by storing the EntryID of each item in the database and using it.  That will always be much faster than performing a search.  Is there a reason why you want/need to search instead of using EntryID?

"The one thing I didn't see in the instant search though is the ability to search by date/time."
Correct.  Instant search uses content indexing.  I expect that it could find a date in a message (e.g. in the subject or body) but it won't search a date field.  

"Will the advanced search method be able to search across all mail items and do it as quickly as instant search (which uses indexes)"
I don't know.  Advanced search takes a scope argument that tells it what containers to search.  I'm not aware of a keyword that will tell it to search all containers.  In the absence of such a keyword you'd have to build a list of all the top level containers.  I know from practical experience that there's a limit on the number of characters that can be passed as the scope argument.  Advanced search is the only search method I know of.  Instant search is to the best of my knowledge an advanced search using the content indexing keywords "ci_phrasematch" or "ci_startwith".  The only difference between it and any other advanced search is the use of the index.  
0
Technology Partners: 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!

 
eyalyaariAuthor Commented:
you're up early today :-)

EntryID is just too fragile and a pain in the butt to maintain accurately between a database and Outlook.  For example, when bringing over the PST file from one computer to a new computer with a fresh Outlook installed all of the entry IDs get redone and my database becomes immediately outdated, etc.

What then if I want to do the following:

Search three folders: sent items, inbox, customers
by exact matches for: received date and subject
if the search result is one record than open it and display it

Do you have a code snippet that I can modify which does something close to that?


Thanks,

Eyal
0
 
David LeeCommented:
"you're up early today"
Not as early as you though.

"EntryID is just too fragile and a pain in the butt to maintain accurately between a database and Outlook."
Can't argue that.  What about generating your own unique ID?  

"Do you have a code snippet that I can modify which does something close to that?"
Yes.  I'm concerned that it isn't going to search fast enough for your client.  To combat that I wrote this to search for the subject first, which should be very fast since it uses the index, and then check the output of that search for the date.  

Part 1 of 2

Follow these instructions to use this.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  Right-click on Class Modules, select Insert > Class Module
5.  In the Properties panel click on Name and enter FastSearch
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor


Const CLASSNAME = "Fast Search"
 
Public bolSearchComp As Boolean
 
Private WithEvents olkApp As Outlook.Application
Private strScope As String, _
    strQuery As String, _
    datReceived As Date, _
    bolSubFolders As Boolean, _
    colResults As Collection
    
Private Sub Class_Initialize()
    Set olkApp = Outlook.Application
End Sub
 
Private Sub Class_Terminate()
    Set olkApp = Nothing
End Sub
 
Private Sub olkApp_AdvancedSearchComplete(ByVal SearchObject As Search)
    bolSearchComp = True
End Sub
 
Public Sub Execute()
    Dim olkSearch As Outlook.Search, _
        olkTable As Outlook.Table, _
        olkRow As Outlook.Row
    bolSearchComp = False
    Set colResults = New Collection
    Set olkSearch = Application.AdvancedSearch(strScope, strQuery, bolSubFolders, "FastSearch")
    Do Until bolSearchComp
        DoEvents
    Loop
    Set olkTable = olkSearch.GetTable
    olkTable.Columns.Add "ReceivedTime"
    Do Until olkTable.EndOfTable
        Set olkRow = olkTable.GetNextRow
        If olkRow("ReceivedTime") = datReceived Then
            colResults.Add olkRow("EntryID")
        End If
    Loop
End Sub
 
Public Property Get Results() As Collection
    Set Results = colResults
End Property
 
 
Public Property Let Received(datValue As Date)
    datReceived = datValue
End Property
 
Public Property Let Query(strValue As String)
    If strValue = "" Then
        MsgBox "The query cannot be empty", vbCritical + vbOKOnly, CLASSNAME
    Else
        strQuery = strValue
    End If
End Property
 
Public Property Let Scope(strValue As String)
    If strValue = "" Then
        MsgBox "The scope cannot be empty", vbCritical + vbOKOnly, CLASSNAME
    Else
        strScope = strValue
    End If
End Property
 
Public Property Let SubFolders(bolValue As Boolean)
    bolSubFolders = bolValue
End Property

Open in new window

0
 
David LeeCommented:
Part 2 of 2

Follow these instructions to use this.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

Sub FindItem(strSubject As String, datReceived As Date)
    Dim objSearch As New FastSearch, varItem As Variant
    With objSearch
        .Scope = "'\\Personal Folders\Inbox','\\Personal Folders\Sent Items','\\Personal Folders\Customers'"
        .Query = "http://schemas.microsoft.com/mapi/proptag/0x0037001E ci_phrasematch '" & strSubject & "'"
        .Received = datReceived
        .SubFolders = True
        .Execute
        For Each varItem In .Results
            'Change the code on the next line.  The value returned is the EntryID of the matching item.'
            Debug.Print varItem
        Next
    End With
End Sub

Open in new window

0
 
eyalyaariAuthor Commented:

Hi,

I'm so close I can taste it!

in that part of the code where you return the entry ID, I guess I need to also get the store ID so that I can display the item.  I tried a few different ways but nothing worked.  Do you know how to get the store ID or is there a simpler way to display the mailitem?

also, can I use the received time for a sent items as well since the scope of my query is all mail item folders?

thanks,

Eyal
0
 
David LeeCommented:
"I guess I need to also get the store ID so that I can display the item"
You shouldn't need the store ID.  It's optional and isn't normally necessary to retrieve an item.

"can I use the received time for a sent items as well"
Yes.  You can also switch to the SentOn property if you want.
0
 
eyalyaariAuthor Commented:

getitemfromID needs storeID or otherwise assumes a default folder, no?

should I use something else?
0
 
David LeeCommented:
No.  I tested earlier before posting and was able to retrieve messages in three different information stores with just the EntryID.
0
 
eyalyaariAuthor Commented:

ok I'll try' again
0
 
eyalyaariAuthor Commented:

okay there is good news and bad news.

the good news is that it works just like you said.
The bad news is that it takes about a minute to do the search because of all the folders :-(

What's interesting though is that if I do the same search using the instant search box it just takes a few seconds. Apparently the advanced search is not as good as the instant search.

so I was thinking what if I write a script that utilizes the instant search box interface to get a manageable sized search result and then:call a VBA routine to iterate over the search results and display the correct one?

My question would be then how do I reference the search results from within VBA?  I remember something about an active inspector, would that work? What is the syntax for that?  do you think this strategy makes sense?

Eyal
0
 
David LeeCommented:
"The bad news is that it takes about a minute to do the search because of all the folders"
I was afraid of that.  

"Apparently the advanced search is not as good as the instant search"
My understanding is that instant search is an advanced search that uses the content index.  There must be something going on behind the scenes that we aren't privy to.

"write a script that utilizes the instant search box interface to get a manageable sized search result and then:call a VBA routine to iterate over the search results"
I don't think that will work well if at all.  The first problem I see is that it means manipulating the user's screen.  The code would have to stuff the search condition into the instant search box and execute the search.  The results would then be plainly visible onscreen.  I expect that'd be very disruptive to the user.  Then there's the problem of how to iterate the results.  I don't see a way to do that.  The results appear in the current Explorer window.  It's easy to get the items that appear in an Explorer, but in this case all the items in the searched folders appear in the window with the matches highlighted in yellow.  I don't know how you'd be able to separate the matches from the rest of the items.  

"do you think this strategy makes sense?"
I don't see a good solution to this given the constraints you're operating under.  
0
 
eyalyaariAuthor Commented:
hi,

There's still hope after all:

I noticed some strange behavior that might help explain why it's taking so long for the search to happen.  I defined the scope to be four different folders (inbox, sent items, junk e-mail, and customers). I noticed that for some reason the searches would only find mail items from the first three and not from the client's own folder called customers.  when I switched the order of the folders being searched in the scope, putting the customers folder first and then the other folders after, then the search would not find anything in any folder.  Thinking that the folder or its subfolders must be corrupt, I created a new folder under personal folders called test and I put one of those e-mails into that folder so that the search would find it.   the new folder behaved exactly like the old folder, the search would not find anything inside of it and when that folder is placed in the beginning of the list for the scope than the rest of the folders also don't find anything.

What's even more bizarre is that if I use the instant search from the user interface than it does show me all the correct results from all the folders all in about 3 seconds.   so if something is corrupt then why isn't it corrupt for the instant search? maybe I am missing a setting on the properties of the folder?

Any clue?

Thx, Eyal
0
 
David LeeCommented:
Have you verified that the path you entered for the Customers folder is correct?
0
 
eyalyaariAuthor Commented:

Yes, it's '\\Personal Folders\Customers'

Isn't that weird?
0
 
David LeeCommented:
That is very strange.  Is there something unusual about these messages?  What happens if you change the code to only search the one folder?  Does that make any difference?
0
 
eyalyaariAuthor Commented:

I created a new folder called test and put one e-mail into it as well as making it the only folder in the scope.  It still couldn't find the e-mail but instant search through the user interface could.  I'm trying to think  if it's the user profile being corrupt or the PST file corrupt, or something in the migration of the old PST file from Outlook 2003 to Outlook 2007that screwed things up.....
0
 
David LeeCommented:
I don't think the profile or PST file is corrupt.  If either of them was, then the search should fail without regard to how it's launched.  I'm wondering if the property tag is correct for those items.  Let's try this.  

1.  Add the code below to Outlook
2.  Select a message in the problem folder
3.  Run DisplayPropValue

If the property tag is correct, then a dialog-box should pop up displaying the subject of the selected message.
Sub DisplayPropValue()
    Dim olkPA As Outlook.PropertyAccessor
    Set olkPA = Application.ActiveExplorer.Selection(1).PropertyAccessor
    MsgBox olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0037001E")
    Set olkPA = Nothing
End Sub

Open in new window

0
 
eyalyaariAuthor Commented:

okay I'll try that as soon as I can log into the client's PC.

By the way,  apparently certain methods and functions work asynchronously. I believe "sleep" and ".Run" are: asynchronous.  Is there a way to make them synchronous, or is there a synchronous equivalent?
0
 
David LeeCommented:
Which Sleep and Run are you referring to?  For Run if you're talking WshShell's Run, then yes.  You can set the WaitOnReturn parameter to True and execution will pause there until the program invoked by the Run finishes.
0
 
eyalyaariAuthor Commented:

By the way, that last problem with that folder not being included in the search decided to just go away on its own. Don't ask me how. What a waste of time! But it still takes just about one minute to do a search through all of these folders.  Apparently, it's not that the advanced search is slower than the instant search within a given folder but rather its implementation of scope that kills it. From what I can see the advanced search searches one folder at a time whereas the instant search is privy to having the equivalent of an "all mail items " folder so that it searches in one folder instead of the hundreds. If only I had access to that folder I would be golden. I even tried setting up a search folder that had all the mail items and thought about running the advanced search on that search folder, but of course the advanced search will not search on a search folder :-(

So I think I'm going to go with the option of writing a script that utilizes the user interface instant search box which is currently finding stuff in about 2 seconds. The only down side as you had mentioned was the annoying manipulation of Windows that the customer will have to put up with. Better that than waiting a minute for the results...  

I'm going to close out this thread.  Last small question:  after I run an instant search from the user interface I want to get back to the previous Explorer window before I ran this search. I tried setting the active explores current folder to the folder that the customer had in the window before running the search but it only partially worked. What it did was switch the folder to inbox for example, but it kept the Explorer window in "search results " mode.  If I then hit the ESC button or click on the inbox folder icon it would get me out of that search mode and into the normal mode.  how do I programmatically get into that normal mode?

Thanks once again for all your relentless help!  if I come across other issues I'll just open a new thread.

Eyal
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now