Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-18
15
Medium Priority
?
352 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:bsharath
  • 8
  • 7
15 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24176121
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
 
LVL 11

Author Comment

by:bsharath
ID: 24176134
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
 
LVL 11

Author Comment

by:bsharath
ID: 24176135
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 76

Expert Comment

by:David Lee
ID: 24176175
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
 
LVL 11

Author Comment

by:bsharath
ID: 24176191
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
 
LVL 11

Author Comment

by:bsharath
ID: 24176192
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24180630
You'd run on just a few by processing one folder at a time, or perhaps one set of subfolders at a time.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24180636
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24180646
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
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 24180661
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
 
LVL 11

Author Comment

by:bsharath
ID: 24181777
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
 
LVL 11

Author Comment

by:bsharath
ID: 24181778
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24183011
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I came across an unsolved Outlook issue and here is my solution.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question