Solved

Can you search Outlook messages like a database (sort of query for a list of items contained in messages: body, subject, or attachment name)

Posted on 2014-11-04
9
374 Views
Last Modified: 2014-11-19
In Outlook 2010/2013, is there a way to take a list of numbers/codes (simple numerical items) and find if they exist in a specific outlook folder?  The could exist in either the body, subject or in the name of an attachment.  It would be nice to be able to know not only that it was found, but in which section(s) as well.

Note: This is not exchange.  Stand alone Outlook only.
0
Comment
Question by:Ray
[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
  • 5
  • 3
9 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40441007
Yes, that can be done
http://msdn.microsoft.com/en-us/library/office/ff866933(v=office.15).aspx
I suspect though, that you'd need to create a UserForm as a place to enter the strings you'd like and perhaps the results as well.  There could be a fair bit of heavy lifting to that.

How are your Outlook VBA skills?
0
 
LVL 10

Author Comment

by:Ray
ID: 40441047
My Outlook VBA skills are only as good as the skills that will transfer from Excel VBA skills.  Sounding like not good enough though.

What i see from the msdn article doesn't appear to give me the ability to look for a list of things (such as 50-100 different job numbers) and return the results of each of those in a table format (I was likely unclear about wanting a table of results).
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40441105
All things are possible because Application.AdvancedSearch exists.
Since you know Excel VBA, that's maybe where you should do this from.
I'm an Access guy, primarily, but the guts of it are the same.
This code goes in a module and is used to start Outlook

Option Explicit
Public wasOpen As Boolean
Function StartApp(ByVal appName) As Object
On Error GoTo ErrorHandler
Dim oApp As Object

wasOpen = True
Set oApp = GetObject(, appName)    'Error here - Run-time error '429':
Set StartApp = oApp

Exit Function

ErrorHandler:
If Err.Number = 429 Then
    'App is not running; open app with CreateObject
    Set oApp = CreateObject(appName)
    wasOpen = False
    Resume Next
Else
    MsgBox Err.Number & " " & Err.Description
End If
End Function

Open in new window


This code, probably in Excel fired from a macro, gets you an Outlook.Application object to work with

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers


Set objOutlook = StartApp("Outlook.Application")

Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = objOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
Set objOutlookExplorers = objOutlook.Explorers

If wasOpen = False Then
    objOutlookExplorers.Add Folder
    Folder.Display
    'done opening
End If

Open in new window


Now, from there, from the second example in the linked MSDN article you be looking to set the parameters of
expression .AdvancedSearch(Scope, Filter, SearchSubFolders, Tag) with Excel cell values
Set up this way, where expression would be Application if you were doing it in Outlook, it will be objOutlook

And, at the end of the second example
   Do Until MyTable.EndOfTable  
        Set nextRow = MyTable.GetNextRow()  
       Debug.Print nextRow("Subject")  
    Loop  

Instead of Debug.Print, you'll be looking to write results to Excel cells

Let me know what you think
0
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.

 
LVL 10

Author Comment

by:Ray
ID: 40442762
Nick,
At first glance, I think you're right in the range of what I need.  It'll be a week or two before I really get to sit down and try it out.  Since it is miles ahead of where i started, I'm going to mark this as the answer and close the question.

Thank you for leading me down the path to a solution!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40443019
I think you should be able to leave it open.  Don't mark it as answered until it really does have finality for you.
On my part, there's no hurry.

Nick67
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40443184
Have a gander at this
http://www.gregthatcher.com/Scripts/VBA/Outlook/GetListOfOutlookEmails.aspx
What version of Outlook are you targeting?

Here's a solid start on what you need.
I've tested it under Outlook 2013 (so you may need to fix the reference if you are downlevel from that) and it does the job.
More detail on filtering here
http://msdn.microsoft.com/en-us/library/office/ff863965(v=office.15).aspx

And probably you'll want to look to see how to pop more details that just 'Subject' onto the results worksheet.

From there, you're looking at creating code that will get Outlook to display the message you select from the Excel sheet.  Fun!
What I've seen so far requires O2007+
Search-Outlook.xlt
0
 
LVL 10

Author Closing Comment

by:Ray
ID: 40453772
After some looking, this will get me there with a bit more time spent.  Thanks for your help!!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40453927
Then there's the round-trip, so to speak.
With the details knock into the spreadsheet, you should be able to craft code in Excel that will have Outlook find and select the message of the active cell if that's what you're also after.

AdvancedSearch also permits multiple search criteria.  I only set up for a single row in Excel, but that is certainly extensible.
You may find this of interest, as well
http://www.gregthatcher.com/Scripts/VBA/Outlook/GetListOfOutlookEmails.aspx

Glad to be of service!
Nick67
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lost emails in Outlook 18 38
keep the cell in the formula in excel 2013 5 27
Modify Array formula - Reduce Calculation Time 4 31
Color a cell based on a date in Excel 8 23
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 get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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