VBA to use specified Outlook folder in Excel

Question

I have the following code:

Option Explicit
Sub EmailText()
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Dim j As Long
Dim abody() As String
Set ObjOutlook = GetObject(, "Outlook.Application")
Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
'For i = 1 To MyNamespace.GetDefaultFolder(6).Folders("TEST").Items.Count
For i = MyNamespace.GetDefaultFolder(6).Folders("TEST").Items.Count To 1 Step -1
    abody = Split(MyNamespace.GetDefaultFolder(6).Folders("TEST").Items(i).Body, Chr(13) &Chr    (10))
    For j = 0 To UBound(abody)
        Sheet1.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = abody(j)
    Next
    MyNamespace.GetDefaultFolder(6).Folders("TEST").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("TEST2")

    Sheets("Sheet2").Select
        Dim NextRow As Range
            With Sheets("Sheet2")
                Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
            End With
    Sheets("Sheet1").Select
    Range("E2:E7").Select
    Selection.Copy
    Sheets("Sheet2").Select
    NextRow.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Sheet1").Select
    Range("A2:A20").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B8").Select
Next
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
End Sub

This works fine for the default outlook mailbox.

I have another inbox setup but cannot for the life of me figure out how to point this at another inbox instead of the default inbox?

Any help would be greatly appreciated,

Many Thanks, Josh

score 0 · Answer 1 · answered May 23 '14 at 11:57

You can use a function like this to get a folder with an arbitrary path:

Function GetFolder(withFolderPath As String) As Folder
    Dim inboxFolder As Folder
    Set inboxFolder = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Dim rootFolder As Folder
    Set rootFolder = inboxFolder.Parent
    Dim i As Integer
    Dim pathParts() As String
    pathParts = Split(withFolderPath, "\")
    Dim currentSubFolder As Folder
    Set currentSubFolder = rootFolder

    For i = 0 To UBound(pathParts)
        Set currentSubFolder = currentSubFolder.Folders.Item(pathParts(i))
    Next

    Set GetFolder = currentSubFolder
End Function

You would use it like this:

Sub test()
    Dim testFolder As Folder
    Set testFolder = GetFolder("Test1\Test2")
End Sub

where "Test1" is a folder on the same level as "inbox" (i.e. at the root of the mailbox) and "Test2" is a folder beneath "Test1".

Basically, it starts with a known folder (inbox) and goes up one level to the root of the mailbox; from there, it iterates through the folders in the path you provided until it reaches the sub-folder.

Hi roryap, thanks for your answer, please excuse me if I am mising something but how do you tell it which mailbox to use? For example, the code I provided uses my Default Inbox and the folders TEST & TEST 2, I now want to use an inbox named Other Inbox and the folders OTHER 1 & OTHER 2? — Uberzen1, May 23 '14 at 12:09
What do you mean "which mailbox to use". Do you have more than one mailbox at play here? How are you accessing this other mailbox; is it loaded into your Outlook profile? — rory.ap, May 23 '14 at 12:35
Sorry, I should of been clearer: I have multiple mailboxes, so if someone emails exampleaddress@company.com it goes into the top mailbox and if someone emails queries@company.com it goes to the bottom mailbox. My terminology is not great with Outlook so again apologies! — Uberzen1, May 23 '14 at 12:59

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

The answer in this thread worked perfectly for the issue I was having:

Access Outlook default folder

edited May 23 '17 at 11:57

Community

1
1

answered May 30 '14 at 07:27

Uberzen1

415
6
18

VBA to use specified Outlook folder in Excel

2 Answers2