Microsoft Excel - Finding a specific folder in Outlook Via Excel VBA

Asked By Vijay Raval on 01-Sep-13 06:43 AM

Hi all,

I am currently struggling to find a way to search Outlook to find a folder.

I the following code  so far:

Dim oitem As Outlook.MailItem

    Dim ol As Outlook.Application

    Dim olns As Outlook.Namespace

    Dim oinbox As Outlook.Folder

    Dim j As Long

     Set ol = New Outlook.Application

    Set olns = ol.GetNamespace("MAPI")


    Set oinbox = olns.GetDefaultFolder(olFolderInbox) 'select's the inbox, but i have others

    Set oinbox = oinbox.Folders("Agency") 'this the subfolfer

    Application.DisplayAlerts = False


    oinbox.Items.Sort "[ReceivedTime]", True

    j = 2


    For Each oitem In oinbox.Items ' loop outlook emails

      ThisWorkbook.Sheets(1).Range("a" & j).Value = oitem.SenderName

      ThisWorkbook.Sheets(1).Range("b" & j).Value = oitem.Subject

      ThisWorkbook.Sheets(1).Range("c" & j).Value = oitem.ReceivedTime

      ThisWorkbook.Sheets(1).Range("d" & j).Value = oitem.Categories




    Set oinbox = Nothing

    Set olns = Nothing

    Set ol = Nothing


    Application.DisplayAlerts = True

Application.ScreenUpdating = False

End Sub

The following are defaults that I am aware of:

'olFolderDeletedItems = 3

'olFolderOutbox = 4

'olFolderSentMail = 5

'olFolderInbox = 6

'olFolderCalendar = 9

'olFolderContacts = 10

'olFolderJournal = 11

'olFolderNotes = 12

'olFolderTasks = 13

'olFolderDrafts = 16

'olFolderJunk = 23


Thank you for looking


Harry Boughen replied to Vijay Raval on 01-Sep-13 04:35 PM
Hello Vijay,
When I extract this from your text
Set oinbox = olns.GetDefaultFolder(olFolderInbox) the second letter after the equality comes out as a lowercase L, I think it should be an Uppercase i.
Hope this helps.
EDIT: Sorry misread some of your other code. H
Harry Boughen replied to Vijay Raval on 01-Sep-13 04:59 PM
Hello again Vijay,
This code comes from another site and is reported to work.  Maybe it will give you some clues on where to go. 

Option Explicit
Sub Ltr_Segment_Imports()
Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object
Dim App As String, Subj As String, MBG As String
Dim NewRpt As Boolean
Dim PDate As Date
On Error GoTo eHandler ' Set error handling
' Set the reference to outlook or create one
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
' Set the reference to the oulook MAPI namespace
Set olNS = olApp.GetNamespace("MAPI")
' Define the generic Mailbox name
MBG = "Mailbox - generic"
Set eFldr = olNS.Folders(MBG).Folders("Personal Archive Folders").Folders("EXCEPTION REPORTS")
' Cycle through the emails in the generic mailbox, Exception folder
For Each olMail In eFldr.Items
' Determine what the next exception report is
NewRpt = False
If InStr(1, olMail.Subject, "Report A", vbTextCompare) > 0 Then
App = "A"
NewRpt = True
ElseIf InStr(1, olMail.Subject, "Report B", vbTextCompare) > 0 Then
App = "B"
NewRpt = True
End If
If NewRpt = True Then
PDate = Format(olMail.ReceivedTime, "mm/dd/yy")
Subj = olMail.Subject
End If

' From this point additional scripting is used to extract data from the email.
' Not worth including for this example

Next olMail

Set olApp = Nothing
Set olNS = Nothing
Set olMail = Nothing
Set eFldr = Nothing

End Sub

Vijay Raval replied to Harry Boughen on 01-Sep-13 05:48 PM
Hmmm interesting, I will look at the code in more detail at work, and see what happens.
So far I have found that when working in Outlook (via Excel) with so many folders that it is not that simple.
What I guess I am looking for is what the hierarchy/structure of outlook to be able to navigate. However I will look at the code and come back to you. However I would like take this opportunity  to thank you for your help. Vj

Harry Boughen replied to Vijay Raval on 01-Sep-13 06:23 PM
Hi Vijay,
I don't have any direct experience but it looks to me as if you just drill down through the folder structure with a series of .Folder() directives starting from the Inbox or where ever your folder tree starts.
Vijay Raval replied to Harry Boughen on 02-Sep-13 02:11 AM
Hi Harry, I think what you have given me shd help me to do that.
Will look at the code today.

Many thanks :)