How to count emails, according to criterion, from all folders and subfolders in Outlook using Excel VBA?

Question

I have to count the number of mails received within certain criterion for weekly reporting. The mails are in various folders and subfolders of Outlook.

Dim objOutlook As Object, objnSpace As Object, objFolder As Outlook.MAPIFolder
Dim EmailCount As Integer

Sub HowManyDatedEmails()
    Set objOutlook = CreateObject("Outlook.Application")
    Set objnSpace = objOutlook.GetNamespace("MAPI")

    On Error Resume Next
    Set objFolder = objnSpace.GetDefaultFolder(olFolderInbox)
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "No such folder."
        Exit Sub
    End If

    Dim iCount As Integer, DateCount1 As Integer
    Dim myDate1 As Date
    Dim myDate2 As Date
    Dim DateCount2 As Integer

    EmailCount = objFolder.Items.Count
    DateCount1 = 0
    DateCount2 = 0
    myDate1 = Sheets("Sheet1").Range("A1").Value
    myDate2 = Sheets("Sheet1").Range("B1").Value

    For iCount = 1 To EmailCount
        With objFolder.Items(iCount)

            If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) >= myDate1 And _
              DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) <= myDate2 And _
              .SenderEmailAddress Like "*kailash*" Then

                DateCount1 = DateCount1 + 1
            End If

            If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) >= myDate1 And _
              DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) <= myDate2 And _
              .SenderEmailAddress Like "*soumendra*" Then

                DateCount2 = DateCount2 + 1
            End If

         End With
     Next iCount

    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
    Sheets("Sheet1").Range("B2").Value = DateCount1
    Sheets("Sheet1").Range("B3").Value = DateCount2

End Sub

I want Excel VBA code such that the sheet list shows the count figure against the criterion number.

I am able to do it for one folder but I want to achieve it for all folders and subfolders recursively in Inbox.

Welcome to SO. please see [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and don't forget to read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) and [I downvoted because no attempt was made](http://idownvotedbecau.se/noattempt/). Also check out the [tour](https://stackoverflow.com/tour), and more good information at ["help center".](https://stackoverflow.com/help/on-topic) — Foxfire And Burns And Burns, Sep 12 '19 at 10:09
I know of no standard Outlook feature that could count emails in the way you seek. There are add-on packages that offer this functionality which confirms my belief that it is not available as standard. — Tony Dallimore, Sep 12 '19 at 10:32
With VBA, it is not particularly difficult to scan every folder within every store looking for emails. If you have a specific set of search criteria in mind, coding a routine to count matches to those criteria would not add much extra difficult. However, an interface that allowed the user to enter different criteria would be more challenging. You need to specify your requirement in a lot more detail before anyone could offer any help or advice. — Tony Dallimore, Sep 12 '19 at 10:33
Check https://stackoverflow.com/questions/2169879/whats-the-best-language-to-automate-this-windows-task-involving-outlook-and-exc — David García Bodego, Sep 12 '19 at 11:19
You have removed text from your question so I am unsure how that could make your question clearer. I have provided an answer that contains a demonstration macro that finds every folder within every store. This will give you are start. — Tony Dallimore, Sep 13 '19 at 13:16
Possible duplicate of [Can I iterate through all Outlook emails in a folder including sub-folders?](https://stackoverflow.com/questions/2272361/can-i-iterate-through-all-outlook-emails-in-a-folder-including-sub-folders) — niton, Sep 13 '19 at 21:36

score 0 · Answer 1 · answered Sep 13 '19 at 13:20

As I said in my comment, this is an Outlook macro. I can show you how to convert it to an Excel macro if necessary. If you need more help, you must expand your question.

Sub ListStoresAndAllFolders()

  ' Displays the name of every accessible store
  ' Under each store, displays an indented list of all its folders

  ' Technique for locating desktop from answer by Kyle:
  ' http://stackoverflow.com/a/17551579/973283

  ' Needs reference to Microsoft Scripting Runtime if "TextStream"
  ' and "FileSystemObject" are to be recognised

  Dim FileOut As TextStream
  Dim FldrCrnt As Folder
  Dim Fso As FileSystemObject
  Dim InxFldrChild As Long
  Dim InxStoreCrnt As Long
  Dim Path As String
  Dim StoreCrnt As Folder

  Path = CreateObject("WScript.Shell").specialfolders("Desktop")

  Set Fso = CreateObject("Scripting.FileSystemObject")
  Set FileOut = Fso.CreateTextFile(Path & "\ListStoresAndAllFolders.txt", True)

  With Application.Session
    For InxStoreCrnt = 1 To .Folders.Count
      Set StoreCrnt = .Folders(InxStoreCrnt)
      With StoreCrnt
        FileOut.WriteLine .Name
        For InxFldrChild = .Folders.Count To 1 Step -1
          Set FldrCrnt = .Folders(InxFldrChild)
          Call ListAllFolders(FldrCrnt, 1, FileOut)
        Next
      End With
    Next
  End With

  FileOut.Close

End Sub
Sub ListAllFolders(ByRef Fldr As Folder, ByVal Level As Long, ByRef FileOut As TextStream)

  ' This routine:
  '  1. Output name of Fldr
  '  2. Calls itself for each child of Fldr
  ' It is designed to be called by ListStoresAndAllFolders()

  Dim InxFldrChild As Long

  With Fldr
    FileOut.WriteLine Space(Level * 2) & .Name
    For InxFldrChild = .Folders.Count To 1 Step -1
      Call ListAllFolders(.Folders(InxFldrChild), Level + 1, FileOut)
    Next
  End With

End Sub

Thanks! I have updated my question with specific request. Can you please help me in formulating that code for excel vba. — Jatin Bansal, Sep 17 '19 at 02:58

How to count emails, according to criterion, from all folders and subfolders in Outlook using Excel VBA?

1 Answers1