Fetch Outlook emails into Excel

Question

I am trying to export Outlook emails into Excel. The code below, for some unknown reason to me, is only exporting 21 mail items. What am I doing wrong?

The array olItems has 1140 items in the count. I don't understand why the control is breaking out of the loop after 21 items.

Option Explicit

Sub list_email_info()

Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim i As Long
Dim arrHeader As Variant

Dim oINS As NameSpace
Dim oIInboxFolder As MAPIFolder
Dim olItems As Items
Dim olMailItem As MailItem

arrHeader = Array("Date Created", "Subject", "Sender's Name", "Unread")

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlwb = xlApp.Workbooks.Add

Set oINS = GetNamespace("MAPI")
Set oIInboxFolder = oINS.GetDefaultFolder(olFolderInbox).Folders("OST")
Set olItems = oIInboxFolder.Items

i = 1

On Error Resume Next
xlwb.worksheets(1).Range("A1").Resize(1, UBound(arrHeader) + 1).Value = arrHeader

For Each olMailItem In olItems

    xlwb.worksheets(1).cells(i + 1, "A").Value = olItems(i).CreationTime
    xlwb.worksheets(1).cells(i + 1, "B").Value = olItems(i).Subject
    xlwb.worksheets(1).cells(i + 1, "C").Value = olItems(i).SenderName
    xlwb.worksheets(1).cells(i + 1, "D").Value = olItems(i).UnRead

    i = i + 1
Next olMailItem

MsgBox "Done"
xlwb.worksheets(1).cells.entirecolumn.autofit




Set xlwb = Nothing
Set xlApp = Nothing

Set olItems = Nothing
Set oIInboxFolder = Nothing
Set oINS = Nothing


End Sub

@Ben - I have modified as suggested, I am still getting the Type Mismatch Error

@Ben - Below error when I try to "Run" the code in the editor. This was not happening earlier.

Comment out `On Error Resume Next`. Do you get an error, and what is the error message? — BigBen, Dec 08 '19 at 00:49
It's very possible that you have items in that folder that are not `MailItem`s: [When is a MailItem not a MailItem](https://stackoverflow.com/questions/78924/when-is-a-mailitem-not-a-mailitem) — BigBen, Dec 08 '19 at 00:49
So, I commented out On Error Resume Next. When I watch the "olItems", it errors out as "Type mismatch" because oIItems =nothing. But, then when I press the "Run" button in the IDE, the control catches up with the next item in the loop. — Jrules80, Dec 08 '19 at 01:04
Where would I do that? Something interesting also is happening and I do not know if they are related but the watch window variables are disappearing when this error 13 (Type mismatch) occurs. — Jrules80, Dec 08 '19 at 01:09
That answer does *exactly* the same thing as mine. It uses `TypeOf` to test the type of the item. — BigBen, Dec 08 '19 at 01:33

BigBen · Accepted Answer · 2019-12-08T01:19:43.290

1

There are other types of items besides MailItems - see When is a MailItem not a MailItem.

First, remove On Error Resume Next. That is just hiding potential errors.

Second, change your loop to something like this:

Dim itm as Object
For Each itm In olItems
    If TypeOf itm Is MailItem Then
        Set olMailItem = itm
        xlwb.worksheets(1).cells(i + 1, "A").Value = olMailItem.CreationTime
        xlwb.worksheets(1).cells(i + 1, "B").Value = olMailItem.Subject
        xlwb.worksheets(1).cells(i + 1, "C").Value = olMailItem.SenderName
        xlwb.worksheets(1).cells(i + 1, "D").Value = olMailItem.UnRead

        i = i + 1   
    End If  
Next

edited Dec 08 '19 at 01:19

answered Dec 08 '19 at 01:14

BigBen

46,229
7
24
40

This above code did the trick. What is the difference between this code and the code in the screen capture in my question? – Jrules80 Dec 08 '19 at 01:33
The problem in your code is `For Each olMailItem In olItems`. That fails when the item is not a `MailItem`. The proposed answer is different: `itm` is an *Object*, not a `MailItem`. – BigBen Dec 08 '19 at 01:36
Gotcha! Duh me. – Jrules80 Dec 08 '19 at 01:36
Is there a way I could have this same piece of code work as VB macro? – Jrules80 Dec 08 '19 at 01:37
What do you mean? – BigBen Dec 08 '19 at 01:37
Right now, I have this code in the Outlook's VB Editor and I do not know how to make it run unless I open up the editor and hit the play button. How could I make this code to run from within the Outlook? I clicked on the macro name from under the "Macros" option and nothing happens. What gives? ** EDIT** I think my workplace email system has macros disabled. Is that why I can run this macro on demand from within Outlook? – Jrules80 Dec 08 '19 at 01:40
Should work. Are you still in `Debug` mode still? – BigBen Dec 08 '19 at 01:42
I have closed out the editor. I only have Outlook open. I am under Developer >>Macros and when I click project1.list_email_info, nothing happens. – Jrules80 Dec 08 '19 at 01:45
Did you see what I posted in [chat](https://chat.stackoverflow.com/rooms/203826/discussion-between-jrules80-and-bigben)? – BigBen Dec 08 '19 at 01:48

Fetch Outlook emails into Excel

1 Answers1