CODEWITHSUNDEEP
excelvbaoutlook
Chris2015
Chris2015

Reputation: 1050

Counting the number of emails in an Outlook Drafts folder

I am trying to count the number of emails in my Outlook "Drafts" folder from Excel VBA.

I have not programmed using the Outlook object hierarchy so I am having some trouble.

Below is one of the variations I've tried. 

Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

Dim objNameSpace As Object
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set items = objNameSpace.GetDefaultFolder(olFolderDrafts).items

MsgBox items.count

When I run this code I receive the error:

Run-time error 5 Invalid procedure call or argument

Upvotes: 1

Views: 1463

Answers (2)

Chris2015
Chris2015

Reputation: 1050

I was able to answer my own question. Firstly, I did not have the MS Outlook Object library clicked. Secondly, I modified my code to the following: 

Dim objOutlook As Object, objNameSpace As Object, objFolder As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderDrafts)

MsgBox objFolder.items.count

Upvotes: 1

0m3r
0m3r

Reputation: 12499

You could also do This

Option Explicit
Public Const olFolderInbox As Long = 6
Public Const olFolderDrafts As Long = 16
Public Const olFolderContacts As Long = 10
Public Const olFolderDeletedItems As Long = 3

Public Sub Count_MailItems()
    Dim olApp As Object  ' Outlook.Application
    Dim olNs As Object   ' Outlook.Namespace
    Dim Folder As Object ' Outlook.MAPIFolder
    Dim Items As Object  ' Outlook.Items
    Dim Msg As String

    Set olApp = CreateObject("Outlook.Application")
    Set olNs = olApp.GetNamespace("MAPI")

'    Set Folder = olNs.Session.PickFolder
    'or
'    Set Folder = olApp.ActiveExplorer.CurrentFolder
    'or
    Set Folder = olNs.GetDefaultFolder(olFolderInbox)
'                                      olFolderDrafts
'                                      olFolderContacts
'                                      olFolderDeletedItems

    Set Items = Folder.Items

    Msg = Items.Count & " Items in " & Folder.Name & " Folder"

    MsgBox (Msg)

End Sub

Upvotes: 0

Related Questions