Searching Outlook Folder

Question

I want to search a specific Outlook folder using an activecell value.

I tried Excel VBA for searching in mails of Outlook and VBA Search in Outlook.

The closest I was able to get:

Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim myitem As Object
Dim Found As Boolean
Dim OutlookSearch as string

Outlooksearch = Cstr(Activecell.cells(1,4).Value)

Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myitems = myInbox.Items
Found = False

For Each myitem In myitems
    If myitem.Class = olMail Then
        If InStr(1, myitem.Subject, "sketch") > 0 Then
            Debug.Print "Found"
            Found = True
        End If
    End If
Next myitem

'If the subject isn't found:
If Not Found Then
    MsgBox "Cannot find"
End If

myOlApp.Quit
Set myOlApp = Nothing

I want to use the string in Activecell.cells(1, 4) as the subject for a search in a specific Outlook folder in the inbox.

I get is the MsgBox even if I've sent an email containing values that match with activecell.

score 0 · Answer 1 · answered Apr 22 '15 at 11:37

0

You can specify the folder to search in, within the inbox, by using the .Folders property.

Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox).Folders("myFolder")

answered Apr 22 '15 at 11:37

luke_t

2,935
4
22
38

score 0 · Answer 2 · answered Apr 22 '15 at 12:03

I've had a play around and come up with the code below. No need to set references to Outlook.

Sub Test1()

    Dim oOutlook As Object 'Outlook.Application
    Dim nNameSpace As Object 'Outlook.Namespace
    Dim mFolderSelected As Object 'Outlook.MAPIFolder
    Dim cFolder As Object
    Dim oItem As Object
    Dim oMyItem As Object
    Dim sOutlookSearch As String
    Dim aFolders() As String
    Dim i As Long

    'sOutlookSearch needs to be something like:
    '"Mailbox - Darren Bartrup-Cook\Inbox"

    sOutlookSearch = ThisWorkbook.Worksheets("Sheet1").Cells(1, 4)
    sOutlookSearch = Replace(sOutlookSearch, "/", "\")
    aFolders() = Split(sOutlookSearch, "\")

    Set oOutlook = GetObject(, "Outlook.Application")
    Set nNameSpace = oOutlook.GetNamespace("MAPI")

    Set mFolderSelected = nNameSpace.Folders.Item(aFolders(0))
    If Not mFolderSelected Is Nothing Then
        For i = 1 To UBound(aFolders)
            Set cFolder = mFolderSelected.Folders
            Set mFolderSelected = Nothing
            Set mFolderSelected = cFolder.Item(aFolders(i))
            If mFolderSelected Is Nothing Then
              Exit For
            End If
        Next i
    End If

    'Set mFolderSelected = nNameSpace.PickFolder 'Alternative to above code block - just pick the folder.

    For Each oItem In mFolderSelected.items
        If oItem.class = 43 Then '43 = olmail
            If InStr(1, oItem.Subject, "sketch") > 0 Then
                Debug.Print "Found:  " & oItem.sendername
                Exit For
            End If
        End If
    Next oItem

End Sub

The code block for finding the correct folder was taken from here: http://www.outlookcode.com/d/code/getfolder.htm

for this line: Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox).Folders("myFolder") ... what if it's in a subfolder? — user2519726, Apr 23 '15 at 02:37
That's the answer given by @vockz. To get a subfolder with this method you'd use: `Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox).Folders("Temp").Folders("aa")` For more subfolders it may be better to put within a loop: `Dim sInbox As String Dim sPath() As String Dim i As Long sInbox = "Temp\aa" sPath = Split(sInbox, "\") Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox) For i = LBound(sPath) To UBound(sPath) Set myInbox = myInbox.Folders(sPath(i)) Next i` — Darren Bartrup-Cook, Apr 23 '15 at 12:01

Searching Outlook Folder

2 Answers2