Copy emails from my Sent Folder in Outlook 2010 to an Excel file

Question

I need to make a record of some emails I've sent over the last couple years, and to include who they were sent to, the date, and the body of the message. Exporting from Outlook does not carry the date, and for some reason Access won't import data from Outlook on my company computers

I came across this macro to export from Outlook to Excel, most of the information I need, but it pulls from the inbox: http://officetricks.com/outlook-email-download-to-excel/

I searched the Office VBA website for commands to make it export from the Sent Items folder instead of the Inbox, but I kept getting run-time error 438 "Object doesn't support this property or method" at the ReceivedByDate and CC lines (under the For command below). It only happens to my sent emails. I tried moving them to a separate folder and into my Inbox, but the macro fails when it reads emails sent from me.

Sub Mail_to_Excel()
'
' Mail_to_Excel Macro
' Copies emails from Outlook to an Excel file
' Add Tools->References->"Microsoft Outlook nn.n Object Library"
' nn.n varies as per our Outlook Installation
    Dim Folder As Outlook.MAPIFolder
    Dim iRow As Integer, oRow As Integer
    Dim MailBoxName As String, Pst_Folder_Name  As String

    'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
    MailBoxName = "[email protected]"

    'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
    Pst_Folder_Name = "Sent Items"

    Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
    If Folder = "" Then
        MsgBox "Invalid Data in Input"
        GoTo end_lbl1:
    End If

    'Read Through each Mail and export the details to Excel for Email Archival
    ThisWorkbook.Sheets(1).Activate
    Folder.Items.Sort "Received"

    'Insert Column Headers
    ThisWorkbook.Sheets(1).Cells(1, 1) = "Sent to"
    ThisWorkbook.Sheets(1).Cells(1, 2) = "Copied"
    ThisWorkbook.Sheets(1).Cells(1, 3) = "Subject"
    ThisWorkbook.Sheets(1).Cells(1, 4) = "Date"
    ThisWorkbook.Sheets(1).Cells(1, 5) = "Size"
    ThisWorkbook.Sheets(1).Cells(1, 6) = "Body"

    'Insert Mail Data
    For iRow = 1 To 5
    'Folder.Items.Count
        oRow = iRow + 1
        ThisWorkbook.Sheets(1).Cells(oRow, 1).Select
        ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).ReceivedByName
        ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).CC
        ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).Subject
        ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).ReceivedTime
        ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).Size
        ThisWorkbook.Sheets(1).Cells(iRow, 6) = Folder.Items.Item(iRow).Body
    Next iRow

    MsgBox "Outlook Mails Extracted to Excel"

end_lbl1:
End Sub

To use this code in Excel, you need to add Outlook reference. Have you done it? In the VBE, Tools -> References, then tick "Microsoft Outlook <version> Object Library". — PatricK
Also ThisWorkbook.Sheets(1).Cells(iRow, 6) = Folder.Items.Item(iRow).Body should be ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body — PatricK
You can't assume all the items in Sent Items are Mails - different types of item (such as invites etc) will have fewer or different properties. You need to check the type of each item before trying to export it. — Tim Williams
PatricK, I added the Outlook 14.0 Object Library fairly early on into editing this program, and later added 3 other Outlook libraries just to see if that would work. And thanks for pointing out the edit! Tim, I hadn't considered that. Come to think of it, if this program is starting with my oldest sent item, then that would be an acceptance to a meeting request. If that damn thing is what's causing my problems... I'll look into this. Thank you! — user2767836
Is there a way to find whether the Item in the Mail Folder is an actual email or Appoinment etc? — Kumarapush

niton niton · Accepted Answer · 2016-09-09T12:34:31

Try SentOn https://msdn.microsoft.com/en-us/library/office/ff864408.aspx instead of ReceivedTime.

You may be interested in a discrepancy between the two. MAPI Outlook object model Mailitem.senton > Mailitem.receivedtime, how is this possible?

Some items in the Sent Items folder will not be mailitems so may not have a .CC property.

You will need a test like

If Item(iRow).class = olMail then

Copy emails from my Sent Folder in Outlook 2010 to an Excel file

1 Answers