Pull Metadata from Outlook to Excel using VBA

Question

I'm trying to pull metadata for metric tracking (times sent) from my sent items mailbox.

I'm getting an error

The attempted operation failed. An Object could not be found.

I've tried several different codes but can not pull any email data from Outlook.

I have ensured that the Outlook 2016 and Excel 2016 objects are active under references.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Inbox").Folders("Sent_Items")

i = 1

For Each OutlookMail In Folder.Items
If OutlookMail.ReceivedTime >= Range("From_date").Value Then
    Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
    Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
    Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
    Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body

    i = i + 1
End If
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

When I select Debug, it highlights the Sub GetFromOutlook () It was also highlighting the Set Folder = OutlookNameSpace.GetDefaultFolder line. — Rob
I have updated the answer. Try using : Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sent_Items") — jainashish
Its giving me a "Compile error: User-Defined type not defined" and its highlighting the Dim OutlookApp As Outlook.Appliation line in blue and highlighting the Sub GetFromOutlook() line in yellow. — Rob
This error comes when the reference is not set. Go to Tools -> References and add the Microsoft Outlook 16.0 reference. — jainashish

jainashish jainashish · Accepted Answer · 2018-12-12T22:21:10

I ran this code from Excel/VBA and it worked perfectly. There is only 1 line of code that gave me error and hence I changed that. It was where you establish reference to the sent items folder.

In the code below, default "Sent Items" folder being used as defined in outlook reference model.

Option Explicit

Sub GetFromOutlook()

    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Namespace
    Dim OutlookRecip As Outlook.Recipient
    Dim Folder As MAPIFolder
    Dim OutlookMail As Variant
    Dim i As Integer

    Set OutlookApp = New Outlook.Application
    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

    Set OutlookRecip = OutlookNamespace .CreateRecipient("[email protected]") '// Owner's Email / Shared Folder email address
    Set Folder = OutlookNamespace.GetSharedDefaultFolder(OutlookRecip, olFolderSentMail)

    'If you want to refer to Sent Items folder then use this.
    'Set Folder = OutlookNamespace.GetDefaultFolder(olFolderSentMail)
    ' Or if your Sent Email folder is inside the Inbox then use the line below (Just Uncomment the below and Comment the upper one)
    'Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sent_Items")

    i = 1

    For Each OutlookMail In Folder.Items
        If OutlookMail.ReceivedTime >= Range("From_date").Value Then
            Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
            Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
            Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
            Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body

            i = i + 1
        End If
    Next OutlookMail

    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing

End Sub

However, if your "Sent Items" folder is under "Inbox" or customised then run the code below to get its exact name and then use it.

For Each Folder In OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders
    Debug.Print Folder.Name
Next

Also, below is the comprehensive list of available folders in the Outlook library. Thought would be helpful to you.

Pull Metadata from Outlook to Excel using VBA

1 Answers