Hello,
For that, you can use VBA codes to do that, I just find one which is close to what you need.
Private Sub Application_Reminder(ByVal Item As Object)
If Item.Class = olTask And Item.Subject = "Update Email Count" Then
Call GetAllInboxFolders
End If
End Sub
Private Sub GetAllInboxFolders()
Dim objInboxFolder As Outlook.Folder
Dim strExcelFile As String
Dim objExcelApp As Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet
Dim nNextEmptyRow As Integer
Dim lEmailCount As Long
lEmailCount = 0
Set objInboxFolder = Outlook.Application.Session.GetDefaultFolder(olFolderInbox)
Call UpdateEmailCount(objInboxFolder, lEmailCount)
‘Change the path to the Excel file
strExcelFile = "E:\Email\Email Count.xlsx"
Set objExcelApp = CreateObject("Excel.Application")
Set objExcelWorkbook = objExcelApp.Workbooks.Open(strExcelFile)
Set objExcelWorksheet = objExcelWorkbook.Sheets("Sheet1")
nNextEmptyRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
'Add the values into the columns
objExcelWorksheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1
objExcelWorksheet.Range("B" & nNextEmptyRow) = Year(Date - 1) & "-" & Month(Date - 1) & "-" & Day(Date - 1)
objExcelWorksheet.Range("C" & nNextEmptyRow) = lEmailCount
'Fit the columns from A to C
objExcelWorksheet.Columns("A:C").AutoFit
'Save the changes and close the Excel file
objExcelWorkbook.Close SaveChanges:=True
End Sub
Private Sub UpdateEmailCount(objFolder As Outlook.Folder, ByRef lCurEmailCount As Long)
Dim objItems As Outlook.Items
Dim objItem As Object
Dim objMail As Outlook.MailItem
Dim strDay As String
Dim strReceivedDate As String
Dim lEmailCount As Long
Dim objSubFolder As Outlook.Folder
Set objItems = objFolder.Items
objItems.SetColumns ("ReceivedTime")
strDay = Year(Date - 1) & "-" & Month(Date - 1) & "-" & Day(Date - 1)
For Each objItem In objItems
If objItem.Class = olMail Then
Set objMail = objItem
strReceivedDate = Year(objMail.ReceivedTime) & "-" & Month(objMail.ReceivedTime) & "-" & Day(objMail.ReceivedTime)
If strReceivedDate = strDay Then
lCurEmailCount = lCurEmailCount + 1
End If
End If
Next
'Process the subfolders in the folder recursively
If (objFolder.Folders.Count > 0) Then
For Each objSubFolder In objFolder.Folders
Call UpdateEmailCount(objSubFolder, lCurEmailCount)
Next
End If
End Sub
And there are some details you may need, so I also add the link here:
https://www.datanume...day-excel-file/
Good luck.