Solved

instant search through vba/vb in outlook 2007

Posted on 2009-05-18
22
1,891 Views
Last Modified: 2012-05-07
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
Comment
Question by:eyalyaari
  • 11
  • 11
22 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24428003
Hi, Eyal.

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

Author Comment

by:eyalyaari
ID: 24428951
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24429916
"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
 

Author Comment

by:eyalyaari
ID: 24430146
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
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24437467
"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
 
LVL 76

Expert Comment

by:David Lee
ID: 24437502
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
 

Author Comment

by:eyalyaari
ID: 24455452

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
 
LVL 76

Expert Comment

by:David Lee
ID: 24455899
"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
 

Author Comment

by:eyalyaari
ID: 24456483

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

should I use something else?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24456530
No.  I tested earlier before posting and was able to retrieve messages in three different information stores with just the EntryID.
0
 

Author Comment

by:eyalyaari
ID: 24456552

ok I'll try' again
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:eyalyaari
ID: 24464806

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
 
LVL 76

Expert Comment

by:David Lee
ID: 24468060
"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
 

Author Comment

by:eyalyaari
ID: 24483805
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24487839
Have you verified that the path you entered for the Customers folder is correct?
0
 

Author Comment

by:eyalyaari
ID: 24488107

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

Isn't that weird?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24488183
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
 

Author Comment

by:eyalyaari
ID: 24488378

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
 
LVL 76

Expert Comment

by:David Lee
ID: 24488686
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
 

Author Comment

by:eyalyaari
ID: 24488794

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
 
LVL 76

Expert Comment

by:David Lee
ID: 24488840
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
 

Author Comment

by:eyalyaari
ID: 24520311

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
If you don't know how to downgrade, my instructions below should be helpful.
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…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

705 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

20 Experts available now in Live!

Get 1:1 Help Now