Count the sent items the items on the selected folder and all sub folders that might be there.

Hi,

Count the sent items the items on the selected folder and all sub folders that might be there.
its a related post from here
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24324938.html#a24175987

I wat the data to an excel ot txt with the user name and no of mails that i have sent. Need a box that asks me if it has to query just the folder or all sub folders too.

As i have 10000 + sub folders need a fast way to get the data.

Regards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
David LeeCommented:
Part 3 of 3

Follow these instructions.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

Run the macro RunMsgSum to produce the summary file.  It outputs to a .csv file suitable for opening in Excel.  It breaks down the count for each recipient by To, CC, BCC, and total.
Sub RunMsgSum()
    Dim objMsgSum As New MessageSummary
    With objMsgSum
        Set .RootFolder = Outlook.Application.ActiveExplorer.CurrentFolder
        'Uncomment the next line if you want to process subfolders'
        '.Subfolders = True
        .Execute
    End With
    Set objMsgSum = Nothing
End Sub

Open in new window

0
 
David LeeCommented:
Sharath,

"As i have 10000 + sub folders need a fast way to get the data."
I'm concerned about your meaning in this statement.  If you mean you need a fast way to look up this information, then I understand.  If instead you mean you need the process that builds this information to do it quickly, then that's pretty much impossible.  There's no "fast" way to search all the messages in 10,000+ sub folders.  I'm not even sure that VBA will be able to search that many folders.  
0
 
bsharathAuthor Commented:
Ok David...
Can i get seperate files txt or excel created for each 100 users.Just a thought as the script running for a long time stops the response of outlook and restarts many times.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bsharathAuthor Commented:
Ok David...
Can i get seperate files txt or excel created for each 100 users.Just a thought as the script running for a long time stops the response of outlook and restarts many times.
0
 
David LeeCommented:
It doesn't matter how many files the process outputs.  That's immaterial.  The issue is having to crawl through all the messages in all of those folders.  Once you start the process it has to run until it finishes.  It won't matter whether it outputs one file for every hundred users or waits and outputs all the counts in one file.  The issue is iterating through all those folders.  Processing 10,000+ folders is going to take a long time. I don't know exactly how long because that depends on the number of messages and the number of addressees per message.  I wouldn't be surprised if it took at least an hour.  And every time you want to update the counts it's going to have to repeat the process.  The only way to avoid that would be to process a few folders now, then a few more later, etc.  You'd be in control of that and you'd have to remember which folders you've processed and which ones are left to go.
0
 
bsharathAuthor Commented:
Ok thats looks fine but how can i run just on few. As i have 1 sent folder which has all these folders below it...

And if its going to take an Hr without restarting the outlook i am ok with that time...
0
 
bsharathAuthor Commented:
Ok thats looks fine but how can i run just on few. As i have 1 sent folder which has all these folders below it...

And if its going to take an Hr without restarting the outlook i am ok with that time...
0
 
David LeeCommented:
You'd run on just a few by processing one folder at a time, or perhaps one set of subfolders at a time.
0
 
David LeeCommented:
Here's my solution for this.  It comes in three parts.

Part 1 of 3

Follow these instructions.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  Right-click on Class Modules, select Insert > Class Module
5.  In the Properties panel click on Name and enter RecipientCounter
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor


'Constants'
Private Const CLASSNAME = "Recipient Counter"
 
'Class Properties'
Private strName As String, _
    intTo As Integer, _
    intCC As Integer, _
    intBCC As Integer
    
Public Property Let Name(strValue As String)
    strName = strValue
End Property
 
Public Property Get Name() As String
    Name = strName
End Property
 
Public Property Get ToCount() As Integer
    ToCount = intTo
End Property
 
Public Property Get CCCount() As Integer
    CCCount = intCC
End Property
 
Public Property Get BCCCount() As Integer
    BCCCount = intBCC
End Property
 
Public Sub IncrTo()
    intTo = intTo + 1
End Sub
 
Public Sub IncrCC()
    intCC = intCC + 1
End Sub
 
Public Sub IncrBCC()
    intBCC = intBCC + 1
End Sub

Open in new window

0
 
David LeeCommented:
Part 2 of 3

Follow these instructions.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  Right-click on Class Modules, select Insert > Class Module
5.  In the Properties panel click on Name and enter MessageSummary
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor


'Constants'
Private Const CLASSNAME = "Message Summary"
 
'Class Properties'
Private objCounters As Collection, _
    olkRootFolder As Outlook.Folder, _
    bolSubfolders As Boolean
    
 
Public Property Set RootFolder(olkFolder As Outlook.Folder)
    Set olkRootFolder = olkFolder
End Property
 
Public Property Let Subfolders(bolValue As Boolean)
    bolSubfolders = bolValue
End Property
 
Public Property Get Subfolders() As Boolean
    Subfolders = bolSubfolders
End Property
 
Public Sub Execute()
    Dim datStart As Date, datEnd As Date
    datStart = Now
    Set objCounters = New Collection
    If TypeName(olkRootFolder) = "Nothing" Then
        MsgBox "No root folder"
    Else
        ProcessFolder olkRootFolder
    End If
    WriteResults
    datEnd = Now
    MsgBox "Processing completed in " & DateDiff("s", datStart, datEnd) & " seconds.", vbInformation + vbOKOnly, CLASSNAME
End Sub
 
Private Sub ProcessFolder(olkFolder As Outlook.Folder)
    Dim olkItem As Object, _
        olkRecipient As Outlook.Recipient, _
        objCounter As RecipientCounter, _
        olkSubfolder As Outlook.Folder, _
        strName As String
    On Error Resume Next
    For Each olkItem In olkFolder.Items
        For Each olkRecipient In olkItem.Recipients
            strName = olkRecipient.Name
            If Left(strName, 1) = "'" Then strName = Mid(strName, 2)
            If Right(strName, 1) = "'" Then strName = Mid(strName, 1, Len(strName) - 1)
            Set objCounter = objCounters.Item(strName)
            If TypeName(objCounter) = "Nothing" Then
                Set objCounter = New RecipientCounter
                objCounter.Name = strName
                objCounters.Add objCounter, strName
            End If
            With objCounter
                Select Case olkRecipient.Type
                    Case olTo
                        .IncrTo
                    Case olCC
                        .IncrCC
                    Case olBCC
                        .IncrBCC
                End Select
            End With
        Next
        Set objCounter = Nothing
        DoEvents
    Next
    On Error GoTo 0
    If bolSubfolders Then
        For Each olkSubfolder In olkFolder.Folders
            ProcessFolder olkSubfolder
        Next
    End If
End Sub
 
Private Sub WriteResults()
    Dim objCounter As RecipientCounter, _
        objFSO As Object, _
        objFile As Object, _
        SQ As String, _
        DQ As String
    SQ = Chr(34)
    DQ = Chr(34) & "," & Chr(34)
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the file name and path on the next line.'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Message Summary.csv", True)
    objFile.WriteLine "Name,To,CC,BCC,Total"
    For Each objCounter In objCounters
        With objCounter
            objFile.WriteLine SQ & .Name & DQ & .ToCount & DQ & .CCCount & DQ & .BCCCount & DQ & .ToCount + .CCCount + .BCCCount & SQ
        End With
    Next
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set objCounter = Nothing
End Sub

Open in new window

0
 
bsharathAuthor Commented:
David this code works fast ....
For 10000's of folders...But a little confused...

Does it query the SENT mails or received mails...Could not figure that out...
0
 
bsharathAuthor Commented:
David this code works fast ....
For 10000's of folders...But a little confused...

Does it query the SENT mails or received mails...Could not figure that out...
0
 
David LeeCommented:
That's good to know.  The speed will depend not just on the number of folders, but also on the number of items in those folders.  

It looks at all mails in those folders.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.