Help on an Excel VBA to Outlook export InBox contents but only a search result

We have been working woth an Excel VBA that export Outlook InBox contents to an Excel workbook.

What we're trying to do is to export just a search result.  

What we mean is that we first go into the InBox "Search Inbox" field and place what we want to search.  Outlook immediately start searching and displays the result.  We also use "Search All Mail Items" to search all Mails in Outlook.

Please review the Excel VBA included and let us know if possible to make this Excel just export Search Results.

Thanx
rayluvsAsked:
Who is Participating?
 
Curt LindstromCommented:
Instead of using the search in Outlook, how about including the subject search in the code like this:

 
Sub ExtractFromEmails_ProcessAllSubFolders()

    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim StrSubject As String
    Dim StrReceived As String
    Dim strName As String
    Dim strTo As String
    Dim strToEmailAddr As String
    Dim strFrom As String
    Dim strFromEmailAddr As String
    Dim strType As String
    Dim strFollowup As String
    Dim strCategory As String
    Dim strCommentObserv As String
    Dim StrFile As String
    Dim StrSavePath As String
    Dim StrFolder As String
    Dim StrFolderPath As String
    Dim StrSaveFolder As String
    Dim Prompt As String
    Dim Title As String
    Dim iNameSpace As NameSpace
    Dim myOlApp As Outlook.Application
    Dim SubFolder As MAPIFolder
    Dim mItem As MailItem
    Dim FSO As Object
    Dim ChosenFolder As Object
    Dim Folders As New Collection
    Dim EntryID As New Collection
    Dim StoreID As New Collection

    Dim outWks As Worksheet

    Dim outCursor As Range
    Dim myRecipient As Variant
    Dim StrSearch As String

    Set outWks = ThisWorkbook.Sheets("Extract Output")

    outWks.Cells.ClearContents

    Set outCursor = outWks.Range("A1")

    outCursor.Value = "From"
    outCursor.Offset(0, 1).Value = "From Email Address"
    outCursor.Offset(0, 2).Value = "Subject"
    outCursor.Offset(0, 3).Value = "Received"
    outCursor.Offset(0, 4).Value = "To"
    outCursor.Offset(0, 5).Value = "To Email Address"
    outCursor.Offset(0, 6).Value = "Type"
    outCursor.Offset(0, 7).Value = "Followup"
    outCursor.Offset(0, 8).Value = "Category"
    outCursor.Offset(0, 9).Value = "CommentObserv"
    outCursor.Offset(0, 10).Value = "Folder"

    Range(outCursor, outCursor.Offset(0, 10)).Font.Bold = True

    Set outCursor = outCursor.Offset(1, 0)

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set myOlApp = Outlook.Application
    Set iNameSpace = myOlApp.GetNamespace("MAPI")
    Set ChosenFolder = iNameSpace.PickFolder

    If ChosenFolder Is Nothing Then GoTo ExitSub

    Call GetFolder(Folders, EntryID, StoreID, ChosenFolder)
    
    ''''''''' SEARCH string ''''''''''''''''''
    StrSearch = InputBox("Enter search string")
    ''''''''''''''''''''''''''''''''''''''''''
    
    For i = 1 To Folders.Count

        Set SubFolder = myOlApp.Session.GetFolderFromID(EntryID(i), StoreID(i))
        On Error Resume Next
        For j = 1 To SubFolder.Items.Count
            Set mItem = SubFolder.Items(j)
            StrSubject = mItem.Subject
            StrReceived = ArrangedDate(mItem.ReceivedTime)
            strTo = mItem.To
            'strtoemailaddr = mitem.
            strFrom = mItem.SenderName
            strFromEmailAddr = mItem.SenderEmailAddress
            strType = mItem.UserProperties("Type")    ' user defined
            strFollowup = mItem.UserProperties("FollowUp")    'user defined
            strCategory = mItem.Categories
            strCommentObserv = mItem.UserProperties("CommentObserv")    'user defined
            strName = StripIllegalChar(StrSubject)

            For Each myRecipient In mItem.Recipients
                strToEmailAddr = myRecipient.Address & "," & strToEmailAddr
            Next myRecipient

            If Len(strToEmailAddr) > 1 Then
                strToEmailAddr = Left(strToEmailAddr, Len(strToEmailAddr) - 1)
            End If
            ''''''''''''' Check subject search string here ''''''''''''
            If Not InStr(StrSubject, StrSearch) = 0 Then
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'now write it out
                outCursor.Value = strFrom
                outCursor.Offset(0, 1).Value = strFromEmailAddr
                outCursor.Offset(0, 2).Value = StrSubject
                outCursor.Offset(0, 3).Value = StrReceived
                outCursor.Offset(0, 4).Value = strTo
                outCursor.Offset(0, 5).Value = strToEmailAddr
                outCursor.Offset(0, 6).Value = strType
                outCursor.Offset(0, 7).Value = strFollowup
                outCursor.Offset(0, 8).Value = strCategory
                outCursor.Offset(0, 9).Value = strCommentObserv
                outCursor.Offset(0, 10).Value = mItem.Parent.FullFolderPath

                Set outCursor = outCursor.Offset(1, 0)
            End If
        Next j
        On Error GoTo 0
    Next i

ExitSub:

End Sub

Open in new window


Code added at lines 38, 72 and 101

Cheers,
Curt
0
 
rayluvsAuthor Commented:
oops.  forgot the excel...
GetEmailDataToExcel.xlsm
0
 
Curt LindstromCommented:
Line 117 with the End If has of course also been added to your code.

Curt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rayluvsAuthor Commented:
It seems its going thru a loop.

We want search thru our search results.  Including "StrSearch" from your script it seems does it search thru the result or throughout the all Inbox including sub-folders?
0
 
Curt LindstromCommented:
Yes it's part of the loop where it checks each subject line to meet the entered search criteria before writing it to the "Extract Output" sheet.

Yes, the search in the modified script will search the the selected folder and all sub folders to that folder. Do you want to limit the search to the top folder only?

Curt
0
 
rayluvsAuthor Commented:
We are working various variation to get it working as we needed.

What we need is, if it possible, to export only the search results.

For example, if the Outlook emails has over 500 emails and in the Inbox we make a search for a specific emails using "Search All Mail Items" to search within all emails and the result display only 20 emails, we want to export only those 20 emails.  The difference is that if we can get the Excel to just go to straight to the search result instead of searching all 500 emails.

Can this be done?



0
 
Curt LindstromCommented:
This modification to the code will search Subject and Body like using "Search All Mail Items" in Outlook.

 
If Not InStr(StrSubject, StrSearch) = 0 Or InStr(mItem.Body, StrSearch) = 0 Then

Open in new window


I'm not sure if you can access the Outlook search results. That's why I'm looking at this work around.

I think you would have to reproduce the the Outlook search function using VBA which is what I'm trying to do. My code is of course a bit simplistic as it doesn't cover searches in the "From" and "To" fields. Also it only looks for the same string in the Subject and Body. However, it will achieve the same results as the Outlook search if your search is only looking at Subject and Body for the same search string. The time to search a few hundred emails shouldn't be that long if the computers you are using are up to date.

It is of course possible to expand the code to cover more search criteria but it will never be as fast as the built in code in the Outlook search.

Curt
0
 
Curt LindstromCommented:
Maybe
 
If InStr(StrSubject, StrSearch) > 0 Or InStr(mItem.Body, StrSearch) > 0 Then

Open in new window

is better

Curt
0
 
rayluvsAuthor Commented:
Yes will try ... and it does make sense with simulating the search within the VBA
0
 
rayluvsAuthor Commented:

This error comes up:

"Excel cannot complete this task with available resources. Choose less data or close other applications"

And it displays everything...
0
 
Curt LindstromCommented:
Strange, it works for me and it only displays emails which match the criteria. I'm using your file with the modified macro.

Try it with the attached file.

Curt
GetEmailDataToExcel-mod1.xlsm
0
 
Curt LindstromCommented:
When I enter the string Tiger I get only 5 emails which have the word Tiger in the subject or body. If I enter just the letter "a" I get 312 emails which is nearly all my emails in the inbox and subfolders since nearly all of them would have an "a" somewhere in the body or subject.

Curt
0
 
rayluvsAuthor Commented:
Ok will try
0
 
Curt LindstromCommented:
I added one line in the code above the search string entry box to change the focus back to excel. This may make it less confusing after the folder has been selected.

Try modified file

Curt
GetEmailDataToExcel-mod2.xlsm
0
 
Curt LindstromCommented:
Adding

outWks.Select

before you exit the sub may be a good idea too. That way you can view the results as soon as they are available..

Curt
0
 
rayluvsAuthor Commented:
It worked!  Took some time since our PST is about 7gb, but it worked
0
 
rayluvsAuthor Commented:
Excellent!
0
 
rayluvsAuthor Commented:
Thank you very much for your assistance.  We have placed another related question.  See link
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27399921.html#a36978545
0
 
Curt LindstromCommented:
Thanks for points and rating! The related question seems to be in good hands already.

Cheers,
Curt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.