Using Excel VBA to count mails in outlook

[kazikamuntu]

Hi to all

I want to count mails in outlook in a specific month in specific folders (of outlook) using excel vba, I alrealdy tried a code in this thread
https://www.mrexcel.com/forum/excel-questions/293671-count-emails-outlook.html

but is very very slow.


I tried this code in outlook-vba and it is very fast.... How can i use that code in Excel?
I added excel and outlook references but it's not working.

how can i sobstitute "Set objItems = Outlook.Application.ActiveExplorer.CurrentFolder.Items" whith my specific folder/subfolder ?

Sub count()
    Dim objItems As Outlook.Items
    Dim objItem As Object
    Dim objMail As Outlook.MailItem
    Dim strMonth As String
    Dim dReceivedTime As Date
    Dim strReceivedDate As String
    Dim i, n As Long
    Dim strMsg As String
    Dim nPrompt As Integer
 
    Set objItems = Outlook.Application.ActiveExplorer.CurrentFolder.Items
 
    objItems.SetColumns ("ReceivedTime")
    strMonth = InputBox("Enter the specific month.(Format: yyyy-mm-dd)", "Specify month")
 
    If strMonth <> "" Then
       n = 0
       For i = 1 To objItems.Count
           If objItems.Item(i).Class = olMail Then
              Set objMail = objItems.Item(i)
              dReceivedTime = objMail.ReceivedTime
              strReceivedDate = Year(dReceivedTime) & " - " & Month(dReceivedTime)
              If strReceivedDate = strMonth Then
                 n = n + 1
              End If
           End If
       Next i
 
       strMsg = "You have received " & n & " emails on " & strMonth & "."
       nPrompt = MsgBox(strMsg, vbExclamation, "Count Received Emails")
    Else
       nPrompt = MsgBox("Please input the specific day!", vbExclamation)
    End If
End Sub

 

[JonasAdamski]

Hi Guys, This article was really helpful for me. However what I need is a little differernt.

I want to get the number of emails in specific folders (multiple folders, 7 to be exact), I am not worried about the date or month. But a count of the emails in these folders is enough.

I also want the date of the last email in these sub folders. The folder is a shared folder that many employees have just like I do and we are supposed to process the data from this shared folder in Outlook.

Could someone please help me because I don't seem to make any progress. Thanks and have a nice day