Counting number of emails in Outlook folder by date

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
Jase AlexanderCompliance ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
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
0
Emmanuel AdebayoGlobal Windows Infrastructure Engineer - ConsultantCommented:
You might try it with this code:

Sub HowManyEmails()

    Dim objOutlook As Object, objnSpace As Object, objFolder As MAPIFolder
    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("Inbox").Folders("report's").Folders("Customer")
        If Err.Number <> 0 Then
        Err.Clear
        MsgBox "No such folder."
        Exit Sub
        End If

    EmailCount = objFolder.Items.Count

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

    Dim dateStr As String
    Dim myItems As Outlook.Items
    Dim dict As Object
    Dim msg As String
    Set dict = CreateObject("Scripting.Dictionary")
    Set myItems = objFolder.Items
    myItems.SetColumns ("SentOn")
    ' Determine date of each message:
    For Each myItem In myItems
        dateStr = GetDate(myItem.SentOn)
        If Not dict.Exists(dateStr) Then
            dict(dateStr) = 0
        End If
        dict(dateStr) = CLng(dict(dateStr)) + 1
    Next myItem

    ' Output counts per day:
    msg = ""
    For Each o In dict.Keys
        msg = msg & o & ": " & dict(o) & " items" & vbCrLf
    Next
    MsgBox msg

    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
End Sub

Function GetDate(dt As Date) As String
    GetDate = Year(dt) & "-" & Month(dt) & "-" & Day(dt)
End Function
0
Jase AlexanderCompliance ManagerAuthor 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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ShumsDistinguished Expert - 2017Commented:
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

0
Jase AlexanderCompliance ManagerAuthor 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
0
Alexei KuznetsovMicrosoft Outlook MVPCommented:
There is a free Outlook Message Report utility that does exactly what you want: it generates the table (you can copy-paste it to Excel or save as CSV and open in Excel) with all messages in selected folders and shows their dates and folders. Also, the Outlook Folder Report shows all folders with number of emails.

DISCLAIMER: I recommend these tools because I'm one of the developers, so feel free to ask any further questions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jase AlexanderCompliance ManagerAuthor Commented:
Hey Guys

Thanks for the help

Not exactly what I was looking for but appreciate the help

J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.