Count Emails In Outlook

[nancy25dec]

If there is mailbox in outlook called "MIS". What VB should I use to count emails in Mailbox.

Your help will be greatly appreciated.

Nancy

 

[Tom Urtis]

Assuming the MIS folder is a direct object in your Outlook's Personal Folders tree:

Sub HowManyEmails()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")
   
   On Error Resume Next
   Set objFolder = objnSpace.Folders("Personal Folders").Folders("MIS")
   If Err.Number <> 0 Then
   Err.Clear
   MsgBox "No such folder."
   Exit Sub
   End If
   
   EmailCount = objFolder.Items.Count
   Set objFolder = Nothing
   Set objnSpace = Nothing
   Set objOutlook = Nothing

MsgBox "Number of emails in the folder: " & EmailCount, , "MIS email count"
End Sub

 

[nancy25dec]

Thank you for this.

How do i count if they are received on particular date?

 

[Tom Urtis]

Just now, I copied a bunch of old emails of various dates into the MIS folder.

On Sheet1, in cell A1, I entered November 3, 2004. It does not matter what format the cell is in, just that you enter a valid date in that cell representing the date you want to count emails for in the MIS folder, which is what you are asking.

This macro I came up with correctly counted how many of those emails were received on the same date as the date in cell A1 of Sheet1, tested fine no problem:

Sub HowManyDatedEmails()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")
   
   On Error Resume Next
   Set objFolder = objnSpace.Folders("Personal Folders").Folders("MIS")
   If Err.Number <> 0 Then
   Err.Clear
   MsgBox "No such folder."
   Exit Sub
   End If
   
 Dim iCount As Integer, DateCount As Integer
 Dim myDate As Date
 EmailCount = objFolder.Items.Count
 DateCount = 0
 myDate = Sheets("Sheet1").Range("A1").Value
   
 For iCount = 1 To EmailCount
 With objFolder.Items(iCount)
 If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1
 End With
 Next iCount
 
Set objFolder = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

MsgBox "Number of emails in MIS folder with matching date: " & DateCount, , "MIS date count"
End Sub

 

[nancy25dec]

Tom,

now there are 2 subfolders in Infox folder. How do I get to them and count emails in subfolder and in its sub sub folder.

 

[Tom Urtis]

Would you please be more specific with your questions.

First, I assume you have, as most Outlook users do, a primary parent folder named "Personal Folders", below which are numerous subfolders.

I do not know what the "Infox folder" is. Maybe you mean Inbox but that is an Inbox, whose emails are not yet saved into any of the Personal Folders' subfolders.

What is the name of the parent subfolder to the MIS folder.

I assume whatever folder name that is, then you have another subfolder to it named MIS, and they are the 2 subfolders to the Personal Folders you are talking about.

So far is that correct? If not, then please re-explain.

Finally, what exactly do you mean by...
"How do I get to them and count emails in subfolder and in its sub sub folder."
...because in your first post on this thread you were only asking for total email count, and in your second post you asked for a count belonging to a particular date.

So, which kind of count do you want for which named folder - - raw count or count for particular date.

 

[nancy25dec]

I am counting emails received on particular date. Folder structure is as follows:
-Private Folder
1. MIS (Renamed Inbox to MIS)
1.1 Enquiries
1.2 Application

There is rule in Outlook, and the emails are received in MIS Folder. Depending on the nature of the query they are moved to Enquiries or Application subfolder.

I would like to count emails in MIS folder and then in Enquiries and Application folder, which are received on yesrday's date.

I hope that makes it bit more clear.

Your help is greatly appreciated.

Nancy

 

[Tom Urtis]

This will do what you say you want, based on how you say your folders are set up.

Sub CountDatedEmails()

'Declare Outlook app and folder object variables.
Dim objOutlook As Object, objnSpace As Object
Dim objFolder As Object, objFolderA As Object, objFolderB As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")

'Verify existence of MIS folder as direct subfolder of Personal Folders.
On Error Resume Next
Set objFolder = objnSpace.Folders("Personal Folders").Folders("MIS")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder named MIS.", 48, "Cannot continue"
Exit Sub
End If

'Verify existence of Enquiries folder as direct subfolder #1 of Personal Folders.
On Error Resume Next
Set objFolderA = objnSpace.Folders("Personal Folders").Folders("MIS").Folders("Enquiries")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder named Enquiries exists in the MIS folder.", 48, "Cannot continue"
Exit Sub
End If

'Verify existence of Enquiries folder as direct subfolder #2 of Personal Folders.
On Error Resume Next
Set objFolderB = objnSpace.Folders("Personal Folders").Folders("MIS").Folders("Application")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder named Application exists in the MIS folder.", 48, "Cannot continue"
Exit Sub
End If

'All folders are present, OK to continue.

'Declare and define the myDate variable to be yesterday's date.
Dim myDate As Date
myDate = DateSerial(Year(Date), Month(Date), Day(Date) - 1)

'Declare and define the count and date variables for all 3 folders.
Dim iCount As Integer
Dim EmailCountMIS As Integer, EmailCountEnquiries As Integer, EmailCountApplication As Integer
Dim DateCountMIS As Integer, DateCountEnquiries As Integer, DateCountApplication As Integer

'Count total and yesterday's received emails in the MIS folder:
EmailCountMIS = objFolder.Items.Count: DateCountMIS = 0
For iCount = 1 To EmailCountMIS
With objFolder.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountMIS = DateCountMIS + 1
End With
Next iCount

'Count total and yesterday's received emails in the MIS > Enquiries folder:
EmailCountEnquiries = objFolderA.Items.Count: DateCountEnquiries = 0
For iCount = 1 To EmailCountEnquiries
With objFolderA.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountEnquiries = DateCountEnquiries + 1
End With
Next iCount

'Count total and yesterday's received emails in the MIS > Application folder:
EmailCountApplication = objFolderB.Items.Count: DateCountApplication = 0
For iCount = 1 To EmailCountApplication
With objFolderB.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountApplication = DateCountApplication + 1
End With
Next iCount

'Advise the user of all counts for the three folders.
MsgBox _
"MIS folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountMIS & vbCrLf & _
"Yesterday: " & vbTab & DateCountMIS & vbCrLf & vbCrLf & _
"MIS > Enquiries folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountEnquiries & vbCrLf & _
"Yesterday: " & vbTab & DateCountEnquiries & vbCrLf & vbCrLf & _
"MIS > Application folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountApplication & vbCrLf & _
"Yesterday: " & vbTab & DateCountApplication, , "Email counts:"

'Release object variable memory
Set objFolder = Nothing
Set objFolderA = Nothing
Set objFolderB = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

End Sub

 

[nancy25dec]

Thanks Tom

 

[aravindhan_31]

Hi Tom,

I was seaching for this code, I got in this thread, But I am not getting the result,
I have also renamed the folder as per the code, but still I am getting an msg box saying, No such folder exists.

However can we make changes in this code as per my folder structure?
I have a list of all folder names in Column A and like this..

Approval Mails
Approved Mail Processed
Query
Re-Directed Mail
Undelivered Email
All the above folder are created under Inbox.

In column B I want to get the count of emails when ever I run the macro.

Please help me on this if possible..

Arvind...