Reference inbox of a different account

Question

I am looking for a way to pull down the information of a different Outlook account into an Excel spreadsheet.

The below code works only for my personal inbox:

Sub psinbox()
Dim olNs As Outlook.Namespace
Dim oltaskfolder As Outlook.MAPIFolder
Dim oltask As Outlook.TaskItem
Dim olitems As Outlook.Items

Dim xlapp As Excel.Application
Dim xlWB As Excel.Workbook
Dim x As Long
Dim arrheaders As Variant

Set olNs = GetNamespace("MAPI")
Set oltaskfolder = olNs.GetDefaultFolder(olFolderInbox)
Set olitems = oltaskfolder.Items

Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
Set xlWB = xlapp.Workbooks.Add

x = 2
arrheaders = Array("Date Created", "Date Recieved", "Subject", "Sender", 
"Senders Email", "CC", "Sender's Email Type", "MSG Size", "Unread?")
On Error Resume Next
xlWB.Worksheets(1).Range("A1").Resize(1, UBound(arrheaders)).Value = ""

Do
    With xlWB.Worksheets(1)
        If Not (olitems(x).Subject = "" And olitems(x).CreationTime = "") Then
            .Range("A1").Resize(1, UBound(arrheaders) + 1) = arrheaders
            .Cells(x, 1).Value = olitems(x).CreationTime
            .Cells(x, 2).Value = olitems(x).recievedtime
            .Cells(x, 3).Value = olitems(x).Subject
            .Cells(x, 4).Value = olitems(x).SenderName
            .Cells(x, 6).Value = olitems(x).CC
            .Cells(x, 7).Value = olitems(x).SenderEmailType ' this is either internal or external server
            .Cells(x, 8).Value = Format((olitems(x).Size / 1024) / 1024, "#,##0.00") & " MB"
            .Cells(x, 9).Value = olitems(x).UnRead
            x = x + 1

        End If
    End With
Loop Until x >= olitems.Count + 1

Set olNs = Nothing
Set oltaskfolder = Nothing
Set olitems = Nothing

Set xlapp = Nothing
Set xlWB = Nothing

End Sub

I want to record how many received emails are unread.

The closest I found was here Count Read and Unread Emails date wise for shared mailbox , which mentioned that would need to Set c = b.Folders("Name of shared mailbox"), however this appears to be for different folders inside the same mail account. What I am after though is access to two different accounts which outlook has access to?

Edit:

Having tried Niton's example, I am having an issue with the below.

If objOwner.Resolved Then
    Set oltaskfolder = olNs.GetSharedDefaultFolder(objOwner, 
olFolderInbox).Folders("admin")
    Set olitems = oltaskfolder.Items
End If

I have tried to use the user name of the shared inbox, the email address, and the name of the email account, but all bring up the following error.

If you do not have the mailbox in your profile stackoverflow.com/questions/27851850/… otherwise this is the another version of the question you found stackoverflow.com/questions/9076634/… — niton
Possible duplicate of VBA Outlook Selecting a Subfolder in the SharedMailbox using GetSharedDefaultFolder — niton
The above is referring to sub folders rather than seperate mail accounts. However I will try the link in your first suggestion slipstick.com/developer/working-vba-nondefault-outlook-folders — Kyoujin

Kyoujin Kyoujin · Accepted Answer · 2017-10-13T10:59:07

The answer it seems was to remove a section which caused complications.

If objOwner.Resolved Then
    Set oltaskfolder = olNs.GetSharedDefaultFolder(objOwner, 
olFolderInbox)
    Set olitems = oltaskfolder.Items
End If

Removing .Folders("admin") fixed the error that was coming up and solved the issue. It then gave me the information about the inbox exactly as required.

Edit:

Side note which I just found out, if you do want to have a sub folder in a shared mail box, just add the .Folders("mailbox") next to the olFolderInbox instead like below.

If objOwner.Resolved Then
    Set oltaskfolder = olNs.GetSharedDefaultFolder(objOwner, 
olFolderInbox).Folders("mailbox")
    Set olitems = oltaskfolder.Items
End If

The previous pages did not work adding it next to CreateRecipient ??

Reference inbox of a different account

1 Answers