We help IT Professionals succeed at work.

Counting number of emails in Outlook folder by date

3,344 Views
Last Modified: 2017-03-30
HI Guys

Hope you can help

Ive tried various pieces of code in Excel to look into my Outlook inbox, specifically in a folder called Performance Data, and basically just return the number of emails in that folder with their respective dates they were sent.

Ive tried some pieces of code that ware giving me an error on establishing the MAPI and / or returns a nil value as if the folder does not exist.

Is there a way to make this possible?

Your suggestions would be very welcome

J
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
It would be helpful if you provide the code you have.

Here is an example, if you need some changes let me know:
Enter date in Sheet1A1 cell
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("Performance Data")
   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 Performance Data folder with matching date: " & DateCount, , "Performance Data date count"
End Sub

Open in new window

Source is from here, I tweaked as per your details
Emmanuel AdebayoGlobal Windows Infrastructure Engineer - Consultant
CERTIFIED EXPERT
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jase AlexanderCompliance Manager

Author

Commented:
Hey Shums

Thank you for the quick response

I believe I used the same code source - it keep coming up with No Such Folder.

Ive tried all manner of combinations but no luck. Is there a way just to list ALL folders and the number of emails in each one with the dates, in case the code would be easier to manipulate this way?

Many Thanks

J
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
If you want emails dated for all the folder then you can try as below, changing "sales@abc.com" to your email ID and enter date in Sheet 1 A1 cell.
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
  objnSpace.Folders("sales@abc.com").Folders("Inbox")
   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 Performance Data folder with matching date: " & DateCount, , "Performance Data date count"
End Sub

Open in new window

Jase AlexanderCompliance Manager

Author

Commented:
HI Shum

Thanks for the above code - aside from it being an interesting update which I will consider using for another project I have, what I actually need for now is, and what I was trying to put across to simplify the result, is to go through all the folders in my inbox and just produce a list in Excel of the folder name with a list of sent dates for each message stored in the folders.

From this I can count the number of mails AND condense the folder list to what I actually need

Is this feasible?

J
Microsoft Outlook MVP
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jase AlexanderCompliance Manager

Author

Commented:
Hey Guys

Thanks for the help

Not exactly what I was looking for but appreciate the help

J

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions