Count Emails In Outlook

N

nancy25dec

New Member
Joined
Sep 15, 2007
Messages
34
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Assuming the MIS folder is a direct object in your Outlook's Personal Folders tree:

Code: 
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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
This will do what you say you want, based on how you say your folders are set up.

Code: 
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
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Automatically sending email from a shared mailbox via an Excel macro
Replies
1
Views
679
Jeff Leubecker
J
J
  • Question
Importing Outlook Data
Replies
0
Views
360
Jack2010
J
M
  • Question
Extract data from outlook emails using excel vba
Replies
1
Views
996
RoryA
RoryA
L
  • Question
VBA Email extract problem
Replies
0
Views
320
Liverlee
L
L
  • Question
VBA to Export Email not working to everyone
Replies
3
Views
306
Luna_Maria
L

Forum statistics

Threads
1,214,400
Messages
6,119,289
Members
448,885
Latest member
LokiSonic

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top