Using VBA in Excel to reference an Outlook mailbox other than Inbox

Question

Edit: I actually figured this out! I replaced the line

Set outlookInbox = outlookApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

with

Dim NS As Outlook.Namespace
Dim objOwner As Outlook.Recipient

Set NS = outlookApp.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("[email protected]")
    Objowner.Resolve

If objOwner.Resolved Then
   MsgBox objOwner.Name 'You can comment this out if you want

Set outlookInbox = NS.GetSharedDefaultFolder(objOwner, olFolderInbox)
End If

Original Post: I have this code that I run in Excel VBA that searches for a specific sender and attachment name in my Outlook default Inbox. It then saves the attachment to a designated folder on my desktop while renaming the file with the date on which the email was received.

However, I want to edit the code so that it searches not in my default Inbox, but in a different, shared mailbox in my Outlook. Assume the email address at which this shared mailbox receives emails is [email protected]. This is obviously separate from my own personal email address.

How can I edit this code so it searches in this mailbox and not in my own Inbox?

Option Explicit

Sub GetLatestReport()

'Set a reference to Outlook's object library (Visual Basic >> Tools >> References >> check/select Microsoft Outlook Object Library)

Dim outlookApp              As Outlook.Application
Dim outlookInbox            As Outlook.MAPIFolder
Dim outlookRestrictItems    As Outlook.Items
Dim outlookLatestItem       As Outlook.MailItem
Dim outlookAttachment       As Outlook.Attachment
Dim attachmentFound         As Boolean

Const saveToFolder          As String = "C:\Users\jalanger\Desktop\Demo" 'change the save to folder accordingly
Const senderName            As String = "Langer, Jaclyn" 'change the sender name accordingly
Const attachmentName        As String = "Report on ACBS LC for AMLS (Chandran Panicker)" 'change the attachment name accordingly

Dim SavePath                As String


'Create an instance of Outlook
Set outlookApp = New Outlook.Application

'Get the inbox from Outlook
Set outlookInbox = outlookApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

'Filter the items from the inbox based on the sender
Set outlookRestrictItems = outlookInbox.Items.Restrict("[SenderName] = '" & senderName & "'")

'Check whether any items were found
If outlookRestrictItems.Count = 0 Then
    MsgBox "No items were found from " & senderName & "!", vbExclamation
    Exit Sub
End If

'Sort the filtered items by received time and in descending order
outlookRestrictItems.Sort Property:="[ReceivedTime]", Descending:=True

'Get the latest item from the filtered and sorted items
Set outlookLatestItem = outlookRestrictItems(1)

'Make sure that file extension at the end of this line is correct
SavePath = saveToFolder & "\" & attachmentName & " " & CStr(Format(outlookLatestItem.ReceivedTime, "Long Date")) & ".xls"
MsgBox SavePath

'Loop through each attachment from the latest item until specified file is found
attachmentFound = False
For Each outlookAttachment In outlookLatestItem.Attachments
    If Left(UCase(outlookAttachment.FileName), Len(attachmentName)) = UCase(attachmentName) Then
        outlookAttachment.SaveAsFile SavePath 'saveToFolder & "\" & outlookAttachment.DisplayName
        attachmentFound = True
        Exit For
    End If
Next outlookAttachment

If attachmentFound Then
    MsgBox "The attachment was found and saved to '" & saveToFolder & "'!", vbInformation
Else
    MsgBox "No attachment was found!", vbExclamation
End If

Workbooks.Open FileName:=SavePath

End Sub

Possible duplicate of Get reference to additional Inbox

niton
– niton

2018-10-13 17:13:14 +00:00
Commented Oct 13, 2018 at 17:13 — niton
– niton, Commented Oct 13, 2018 at 17:13

Evanzheng · Accepted Answer · 2018-10-19 08:02:43Z

1

You can use the DeliveryStore property of the Account to get its inbox. For example:

Sub ResolveName()
Dim ns As NameSpace
Set ns = Application.Session
Dim acc As Account
Dim f As Folder

For Each acc In ns.accounts
    MsgBox acc.UserName
    If acc = "[email protected]" Then
    Set f = acc.DeliveryStore.GetDefaultFolder(olFolderInbox)
    MsgBox f.Items.count

End If
Next
End Sub

You can filter using acc = "[email protected]" or the acc.UserName property.

answered Oct 19, 2018 at 8:02

Evanzheng

2111 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ken Love · Accepted Answer · 2018-10-12 19:51:21Z

0

If you have a second account setup in Outlook (e.g. [email protected]) you could replace this line:

Set outlookInbox = outlookApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

with this:

Set outlookInbox = outlookApp.GetNamespace("MAPI").Accounts.Item(2).Session.GetDefaultFolder(olFolderInbox)

This will use the second account's Inbox.

edited Oct 12, 2018 at 19:51

answered Oct 12, 2018 at 19:30

Ken Love

1786 bronze badges

7 Comments

learningtocode123 Over a year ago

are those meant to be two separate lines, or is the (2).Session.GetDef.... on the second line meant to follow directly after Item at the end of the first line?

Ken Love Over a year ago

Sorry, it's all meant to be on one line. I revised the answer.

learningtocode123 Over a year ago

I get the error message "Array index out of bounds", any idea why?

Ken Love Over a year ago

I interpret that as meaning there is only 1 account setup in Outlook, or at least only one is available in this context. How are you receiving mail for "[email protected]"?

learningtocode123 Over a year ago

The email address of the mailbox is [email protected], and it has its own Inbox, Sent, Drafts, Deleted Items, etc., folders. I also have my own personal email in Outlook, [email protected]. I have both of these mailboxes set up in my Outlook. It's a shared mailbox, so many people also have it connected in their Outlook. Once a week, a vendor sends an email to [email protected], and I'm trying to access that email with my code.

|

Collectives™ on Stack Overflow

Using VBA in Excel to reference an Outlook mailbox other than Inbox

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related