[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-15
19
Medium Priority
?
649 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:rayluvs
  • 10
  • 9
19 Comments
 

Author Comment

by:rayluvs
ID: 36974265
oops.  forgot the excel...
GetEmailDataToExcel.xlsm
0
 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 2000 total points
ID: 36974490
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
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36974498
Line 117 with the End If has of course also been added to your code.

Curt
0
Industry Leaders: 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:rayluvs
ID: 36974647
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
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 36975237
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
 

Author Comment

by:rayluvs
ID: 36975537
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
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36977071
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
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36977179
Maybe
 
If InStr(StrSubject, StrSearch) > 0 Or InStr(mItem.Body, StrSearch) > 0 Then

Open in new window

is better

Curt
0
 

Author Comment

by:rayluvs
ID: 36977752
Yes will try ... and it does make sense with simulating the search within the VBA
0
 

Author Comment

by:rayluvs
ID: 36977995

This error comes up:

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

And it displays everything...
0
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36978044
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
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36978075
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
 

Author Comment

by:rayluvs
ID: 36978078
Ok will try
0
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36978112
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
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 2000 total points
ID: 36978141
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
 

Author Comment

by:rayluvs
ID: 36978483
It worked!  Took some time since our PST is about 7gb, but it worked
0
 

Author Closing Comment

by:rayluvs
ID: 36978513
Excellent!
0
 

Author Comment

by:rayluvs
ID: 36978549
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
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 36978992
Thanks for points and rating! The related question seems to be in good hands already.

Cheers,
Curt
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

834 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