Solved

Help with Macro to customize search

Posted on 2008-09-29
23
315 Views
Last Modified: 2012-05-05
Does anyone know of a macro that I can use that will customize a search on the SENT ITEMS folder? I need it to read from the clipboard. So, it'd be something like this:

1. CTRL-C an email address in the text of the email message body
2. Hit a HOTKEY invoking the Macro
3. The macro will search the SENT ITEMS folder with the clipboard.settext function
4. The macro will return a value showing if the email on the clipboard is located in the SENT ITEMS folder.
0
Comment
Question by:Takamine334
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
23 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22601401
Hi, Takamine334.

A couple of questions.
  1. Do you just want to know if the folder contains a match, or do you want to see the matches like you would if you'd used advanced search?
  2. Should the search only look in address fields (i.e. TO, CC, BCC) or does it need to look in message text too?
0
 

Author Comment

by:Takamine334
ID: 22602123
1. I would want to see the match if it occurs
2. I would like to see if the match occurs in the TO text box.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 22609889
Give this a try.  This code must go in the ThisOutlookSession module.

Sub MySearch()
    Const strScope As String = "'Sent Items'"
    Const strTag As String = "Search1"
    Dim objIE As Object, _
        objDoc As Object, _
        olkSearch As Outlook.Search, _
        strFilter As String, _
        varText As Variant
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.navigate ("about:blank")
    Set objDoc = objIE.Document
    varText = objDoc.parentWindow.clipboardData.getData("Text")
    strFilter = "urn:schemas:httpmail:displayto LIKE '%" & varText & "%'"
    Set objSch = Application.AdvancedSearch(Scope:=strScope, Filter:=strFilter, SearchSubFolders:=False, Tag:=strTag)
End Sub
 
Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
    'Change the file path on the following line
    Const strFilename = "C:\eeTesting\SearchResults.htm"
    Dim olkResults As Outlook.Results, _
        objFSO As Object, _
        objFile As Object, _
        objIE As Object, _
        strBuffer As String
    Set olkResults = SearchObject.Results
    strBuffer = "Results of Search <b>" & SearchObject.Tag & "</b><br><br>" & vbCrLf
    strBuffer = strBuffer & "There were <b>" & olkResults.Count & "</b> matches.<br><br>" & vbCrLf
    For Each Item In olkResults
        strBuffer = strBuffer & "<a href=""Outlook:" & Item.EntryID & """>" & Item.Subject & "</a><br>" & vbCrLf
    Next
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(strFilename, True)
    objFile.Write strBuffer
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Navigate2 strFilename
    Do Until objIE.readyState = 4
        DoEvents
    Loop
    objIE.Visible = True
    Set objIE = Nothing
End Sub

Open in new window

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!

 

Author Comment

by:Takamine334
ID: 22610078
First off, I think you should be given the Mensa Award.

Now that that's said, I highlighted and copied an email address in a message. I ran the macro and it pulled up:

Results of Search Search1
There were 1 matches.
NACA Question

When I clicked on the link, nothing happened. Can you tell me if I'm doing something wrong?


0
 

Author Comment

by:Takamine334
ID: 22610186
Actually, another IE window appeared with the URL:
outlook:00000000D2BB204E2AD386469DE63453C260EE8EA4882300


The page said:
The webpage cannot be displayed
   
   Most likely cause:
Some content or files on this webpage require a program that you don't have installed.
 
   What you can try:
     Search online for a program you can use to view this web content.  
 
     Retype the address.  
 
     Go back to the previous page.
 
0
 

Author Comment

by:Takamine334
ID: 22610228
Also, I'm getting the below response when using this macro. Is there anyway to just open up the SENT ITEMS folder that stores this email?
Clipboard01.gif
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22610233
Thanks for the thought.

You aren't doing anything wrong.  By default Office/Outlook 2007 does not install a URL protocol handler for links to Outlook items.  The protocol handler is just a couple of registry entries.  There's an explanation and .reg file download tht'll add the entries for you at this page: http://www.davidtan.org/outlook-2007-adding-outlook-url-protocol/  After applying the .reg file you may have to edit the entries.  Go ahead and install the .reg file, then try a one of the links and let me know what happens.
0
 

Author Comment

by:Takamine334
ID: 22610419
Absolutely perfect. Would it be possible to put this command in a (right click) menu link?

1. Select hyperlink
2. ALT+F8
3. Select THISOUTLOOKSESSION.MYSEARCH
0
 

Author Closing Comment

by:Takamine334
ID: 31501289
Once again, BlueDevil rocked it!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22610454
The dialog-box you took the screenshot of is generated when reading data from the clipboard.  Try replacing MySearch with the version below.  You'll need to add a reference to FM20.DLL in Outlook.  To do that
  1. Open the VB editor in Outlook
  2. Click Tools > References
  3. Click the Browse button
  4. Go to the Windows System32 folder
  5. Find and add FM20.DLL
Sub MySearch()
    Const strScope As String = "'Sent Items'"
    Const strTag As String = "Search1"
    Dim objData As New DataObject, _
        olkSearch As Outlook.Search, _
        strFilter As String, _
        strBuffer As String
    objData.GetFromClipboard
    strBuffer = objData.GetText()
    strFilter = "urn:schemas:httpmail:displayto LIKE '%" & varText & "%'"
    Set objSch = Application.AdvancedSearch(Scope:=strScope, Filter:=strFilter, SearchSubFolders:=False, Tag:=strTag)
End Sub

Open in new window

0
 

Author Comment

by:Takamine334
ID: 22610495
Did the above and the error is gone, however, when searching an email I get:

Results of Search Search1
There were 0 matches.

I'm actually looking at the SENT ITEMS folder that does contain that email.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22610496
Oops.  Typo.  Disregard that last version and use this one instead.

Sub MySearch()
    Const strScope As String = "'Sent Items'"
    Const strTag As String = "Search1"
    Dim objData As New DataObject, _
        olkSearch As Outlook.Search, _
        strFilter As String, _
        strBuffer As String
    objData.GetFromClipboard
    strBuffer = objData.GetText()
    strFilter = "urn:schemas:httpmail:displayto LIKE '%" & strBuffer & "%'"
    Set objSch = Application.AdvancedSearch(Scope:=strScope, Filter:=strFilter, SearchSubFolders:=False, Tag:=strTag)
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 22610499
I'll work on the context menu angle.
0
 

Author Comment

by:Takamine334
ID: 22610520
I see that varText  = Empty
0
 

Author Comment

by:Takamine334
ID: 22610568
Changed varText to strBuffer and it works.
0
 

Author Comment

by:Takamine334
ID: 22610744
Would it be possible to put this command in a (right click) menu link?

1. Select hyperlink
2. ALT+F8
3. Select THISOUTLOOKSESSION.MYSEARCH
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22630496
The right-click, "context", menus in Outlook are separate entities.  Do you want this added to all of them or only one?  If one, which one?
0
 

Author Comment

by:Takamine334
ID: 22630729
I'm sorry, I don't understand the question.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22634355
No problem.  Te right-click menus in Outlook are called "context" menus.  The selections offered on a menu are in the "context" of the click.  For example, right-clicking on a folder displays selections that apply to a folder context.  Right-clicking on a message displays selections in the context of a message.  Outlook defines the following six contexts: Attachment, Folder, Item, ShortCut Store, and View.  My question is whether you want a menu selection added to all of them, some of them, or only one of them?  In other words, do you want the search option to appear when you right-click on an attachment, a folder, a vew, etc?  Unfortunately the context menu that appears when you right-click on an address isn't programmable.
0
 

Author Comment

by:Takamine334
ID: 22639238
I see. Thank you for that information. I would assume I'd only need that function when I right click an email when viewing emails in the preview pane or when they are double clicked and the email message is in its own form.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22665066
Sorry to be slow getting back to you.  None of the context menus work in open items.  Are you sure you wouldn't prefer a toolbar button?
0
 

Author Comment

by:Takamine334
ID: 22665118
A button would work fine. Thank you.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22694912
Apologies again for being so slow.  Here's how to add a toolbar and button for running a given macro.

1.  Click View->Toolbars->Customize
2.  Click the Toolbars tab
3.  Click New
4.  Name the toolbar
5.  Click the Commands tab
6.  Under Categories click Macros
7.  Under Commands click and hold on the macro, then drag it out and drop it on the new toolbar
8.  Dock the toolbar somewhere onscreen
0

Featured Post

Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
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 …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

738 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