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

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

  

Next

   

    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

Vj

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.
Harry
EDIT: Sorry misread some of your other code. H

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

NextMsg:
Next olMail

eHandler:
Set olApp = Nothing
Set olNS = Nothing
Set olMail = Nothing
Set eFldr = Nothing

End Sub

Regards
Harry

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

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.
Regards
Harry

Hi Harry, I think what you have given me shd help me to do that.
Will look at the code today.

Many thanks :)

Vj