Shared Inbox - Skipping non-mail items in Outlook VBA

Question

I am not an expert in Outlook VBA but I have managed to create a few macros that work quite well. I have worked on the below code for some time and am now left with a minor problem. The macro imports information on every email from a sub-folder of a shared inbox into an excel file. The problem I have is when the for next loop encounters a non-mail item (e.g. a meeting invite or delivery failure notification). The code stops on the "Next" line and gives a "type mismatch" error when it encounters these non-mail items. Pressing play again continues the code until it meets another non-mail item. I want to make the code skip these non-mail items and loop through the full inbox/folder.

I have tried "On Error Resume Next" but it seems to skip the "Next" line and continues on with the remaining code without actually looping back to the "For Each" line. I have played around with If's and GoTo statements but none would work for me. Can anybody help please?

I also have another issue with the macro in general. Sometimes it will not run as it seems to not recognise the "ARCHIVE" sub-folder of the inbox but other times it is fine. My guess is that when the shared inbox is syncing with the server, or something like, the "ARCHIVE" folder cannot be accessed that but that's only a guess. If anyone could shed more light on this issue also I would greatly appreciate it.

Sub EmailStatsV3()

Dim olMail As Outlook.MailItem
Dim aOutput() As Variant
Dim lCnt As Long
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim flInbox As Folder

'Gets the mailbox and shared folder inbox
Dim myNamespace As Outlook.NameSpace
Dim myRecipient As Outlook.Recipient
Set myNamespace = Application.GetNamespace("MAPI")
Set myRecipient = myNamespace.CreateRecipient("Shared Inbox") 'Change "Shared Inbox" to whatever shared inbox you use

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetSharedDefaultFolder(myRecipient, olFolderInbox)

'Uses the Parent of the Inbox to specify the mailbox
strFolderName = objInbox.Parent

'Specifies the folder (inbox or other) to pull the info from
Set objMailbox = objNamespace.Folders(strFolderName)
Set objFolder = objMailbox.Folders("Inbox").Folders("ARCHIVE") 'Change this line to specify folder
Set colItems = objFolder.Items

'Specify which email items to extract
ReDim aOutput(1 To objFolder.Items.Count, 1 To 5)
For Each olMail In objFolder.Items
If TypeName(olMail) = "MailItem" Then

        lCnt = lCnt + 1
        aOutput(lCnt, 1) = olMail.SenderEmailAddress 'Sender or SenderName also gives similar output
        aOutput(lCnt, 2) = olMail.ReceivedTime 'stats on when received
        aOutput(lCnt, 3) = olMail.ConversationTopic 'group based on subject w/o regard to prefix
        aOutput(lCnt, 4) = olMail.Subject 'to split out prefix
        aOutput(lCnt, 5) = olMail.Categories 'to split out category
End If

Next olMail

'Creates a blank workbook in excel then inputs the info from Outlook
Set xlApp = New Excel.Application
Set xlSh = xlApp.Workbooks.Add.Sheets(1)

xlSh.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
xlApp.Visible = True


End Sub

The below answer from Macro Man works perfectly. FYI the code stopped at the "Next olMail" line. — Colin Torpey
Glad it's working. Next time, include more relevant information in your Q -- it is usually expected that the asker will identify which line raises the error :) This saves everyone else the trouble of having to guess at what the problem(s) might be. Cheers. — David Zemens

SierraOscar SierraOscar · Accepted Answer · 2015-09-23T13:55:24

Change

Dim olMail As Outlook.MailItem

To

Dim olMail As Variant

A Variant type should be used to iterate collections in a For Each loop, in your example the Next item is not a MailItem - which is what olMail has been declared as. You already have a check in place to see if olMail is a mail item, so you can use a variant here instead.

Shared Inbox - Skipping non-mail items in Outlook VBA

1 Answers