+ Reply to Thread
Results 1 to 7 of 7

Macro to take count of emails in each folder and subfolder from outlook mailbox

  1. #1
    Registered User
    Join Date
    12-19-2010
    Location
    mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    71

    Macro to take count of emails in each folder and subfolder from outlook mailbox

    Hello Everyone,

    I am looking to build a vba macro that will give me count of emails that i receive in each subfolder of my outlook email box and day to day basis.

    I have many folders under my inbox . i have listed the names of those folders in in an excel sheet. In cell A2, A3, A4, A4, A5, A6 A7 etc i have list of folders. and cell B2, C2, D2, E2, F2....i have dates of each month.

    What i need is, whenever i will run the macro i should get the count of mails received in folder mentioned in column A and dates mentioned in cell b2, C2 and on..

    Thnaks.

  2. #2
    Registered User
    Join Date
    12-19-2010
    Location
    mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    71

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    Hello All,

    Does anyone has any solution for it. I am stuck.

    Thanks.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    Hi

    Are you on an Exchange server or using a pst for mail storage? Also, what is in column B- is it an actual date (if so which day of the month) or just a text indicator of the month and year?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    12-19-2010
    Location
    mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    71

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    I have attached excel four reference.

    so in b2, I need count of emails received in folder(A2) on date(B1).

    thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-19-2010
    Location
    mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    71

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    Hi Folks,

    I found this code which gives me number of emails for dates mentioned in column "A". so one part is solved. I need sub folder wise counts.

    Any help would be appreciated.

    Thanks

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    Quote Originally Posted by saurabhlotankar View Post
    I found this code which gives me number of emails
    What code?

  7. #7
    Registered User
    Join Date
    12-19-2010
    Location
    mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    71

    Re: Macro to take count of emails in each folder and subfolder from outlook mailbox

    Hey xlnitwit,

    Sorry. This is the code:

    Sub HowManyDatedEmails()

    ' Set Variables
    Dim objOutlook As Object, objnSpace As Object, objFolder As Object
    Dim EmailCount As Integer, DateCount As Integer, iCount As Integer
    Dim myDate As Date
    Dim arrEmailDates()

    ' Get Outlook Object
    Set objOutlook = CreateObject("Outlook.Application")
    Set objnSpace = objOutlook.GetNamespace("MAPI")

    ' Get Folder Object
    On Error Resume Next
    Set objFolder = objnSpace.Folders("[email protected]").Folders("Inbox")
    If Err.Number <> 0 Then
    Err.Clear
    MsgBox "No such folder."
    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
    Exit Sub
    End If

    ' Put ReceivedTimes in array
    EmailCount = objFolder.Items.Count
    For iCount = 1 To EmailCount
    With objFolder.Items(iCount)
    ReDim Preserve arrEmailDates(iCount - 1)
    arrEmailDates(iCount - 1) = DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime))
    End With
    Next iCount

    ' Clear Outlook objects
    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing

    ' Count the emails dates equal to active cell
    Sheets("Sheet1").Range("A1").Select
    Do Until IsEmpty(ActiveCell)

    DateCount = 0
    myDate = ActiveCell.Value

    For i = 0 To UBound(arrEmailDates) - 1
    If arrEmailDates(i) = myDate Then DateCount = DateCount + 1
    Next i

    Selection.Offset(0, 1).Activate
    ActiveCell.Value = DateCount
    Selection.Offset(1, -1).Activate
    Loop
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Use VBA to get count of emails in Outlook folder
    By catalystsystems in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-05-2017, 06:02 PM
  2. Excel VBA to count emails each day in shared outlook folder
    By k1989l in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2016, 04:29 AM
  3. Replies: 14
    Last Post: 09-18-2016, 08:13 AM
  4. Macro to fetch the name from the signature of outlook sent folder emails
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-22-2015, 03:24 AM
  5. Moving an e-mail from a subfolder in Mailbox to the equivalent subfolder in an archive PST
    By johncassell in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2012, 11:31 AM
  6. Import folder structure in Outlook mailbox
    By Toomay in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 08-14-2011, 05:45 PM
  7. Save emails sent on behalf of another mailbox in correct sent items folder
    By Domski in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 04-06-2010, 12:53 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1