Count the no of mails in a folder with the name of the user i sent a mail to.

Hi,

Count the no of mails in a folder with the name of the user i sent a mail to.
I want a macro when run on the sent items gets me a stats on the no of mails i have sent to a person. I want this for all mails in a folder and another macro that processes into all sub folders as well.

2 codes one to find how many mails i have sent to a He/She in the sent and another to process the sent and all sub folders with in it.

REgards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
No problem.  This is why it's important to be specific in wording questions/requests.  

Change line #34 in the class module from

    ProcessFolder Outlook.Session.GetDefaultFolder(olFolderSentMail)

to

    ProcessFolder Outlook.Application.ActiveExplorer.CurrentFolder

Select the folder you want to run the code against before running it.
0
 
IvaSoftCommented:
Hire a programmer or write a macro yourself.

Regards,
Victor
0
 
David LeeCommented:
Hi, sharath.

The solution to this comes in two parts.  This is part 1.  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 MessageCounter
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


Private Const CLASSNAME = "Message Counter"
 
Private strSender As String, _
    olkRecipient As Outlook.Recipient, _
    bolSubfolders As Boolean, _
    intCount As Integer
 
Public Property Let Sender(strValue As String)
    strSender = strValue
End Property
 
Public Property Get Sender() As String
    Sender = strSender
End Property
 
Public Property Let Subfolders(bolValue As Boolean)
    bolSubfolders As Boolean
End Property
 
Public Property Get Subfolders() As Boolean
    Subfolders = bolSubfolders
End Property
 
Public Function Count() As Integer
    If strSender <> "" Then
        intCount = 0
        Set olkRecipient = Outlook.Session.CreateRecipient(strSender)
        olkRecipient.Resolve
        If olkRecipient.Resolved Then
            ProcessFolder Outlook.Session.GetDefaultFolder(olFolderSentMail)
        Else
            MsgBox "Processing Aborted" & vbCrLf & "The name " & strSender & " could not be resolved.", vbCritical + vbOKOnly, CLASSNAME
        End If
    Else
        MsgBox "Processing Aborted" & vbCrLf & "You must provide a name to search for.", vbCritical + vbOKOnly, CLASSNAME
    End If
    Count = intCount
End Function
 
Private Sub ProcessFolder(olkFolder As Outlook.Folder)
    Dim olkItem As Object, _
        olkAddressee As Outlook.Recipient, _
        olkSubfolder As Outlook.Folder
    On Error Resume Next
    For Each olkItem In olkFolder.Items
        Debug.Print olkItem.Subject
        For Each olkAddressee In olkItem.Recipients
            If olkAddressee.Address = olkRecipient.Address Then
                intCount = intCount + 1
                Exit For
            End If
        Next
    Next
    On Error GoTo 0
    If bolSubfolders Then
        For Each olkSubfolder In olkFolder.Folders
            ProcessFolder olkSubfolder
        Next
    End If
End Sub

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
David LeeCommented:
This is part 2.

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 this macro when you want to do a search.
Sub CountSentMessages()
    Const MACRONAME = "Count Sent Messages"
    Dim objCounter As New MessageCounter
    With objCounter
        .Sender = InputBox("Enter the name of email address of the person to count for.", MACRONAME)
        .Subfolders = (MsgBox("Check subfolders too?", vbQuestion + vbYesNo, MACRONAME) = vbYes)
        MsgBox "There were " & .Count() & " items sent to " & .Sender, vbInformation + vbOKOnly, MACRONAME
    End With
    Set objCounter = Nothing
End Sub

Open in new window

0
 
David LeeCommented:
I have a typo in the first code snippet.  Change line 17 to read

    bolSubfolders = bolValue
0
 
bsharathAuthor Commented:
Thanks David...
I dont want a popup to ask me for the email id/....

I want to query every email id in the sent folder and after selection there sub folders.
The 2nd popup is the only one that needs to come...
I want to find each and every email id that i ahve sent to,...

The current code does not get any resilts i get 0 as results for any email id i place
I get this in the immediate window...
===================================================
RE: Processing files
Try this
Errors in 100's of machines
Venk@plc.com have you given him permanent laptop
===================================================

Just thought this might help....

0
 
bsharathAuthor Commented:
Thanks David...
I dont want a popup to ask me for the email id/....

I want to query every email id in the sent folder and after selection there sub folders.
The 2nd popup is the only one that needs to come...
I want to find each and every email id that i ahve sent to,...

The current code does not get any resilts i get 0 as results for any email id i place
I get this in the immediate window...
===================================================
RE: Processing files
Try this
Errors in 100's of machines
Venk@plc.com have you given him permanent laptop
===================================================

Just thought this might help....

0
 
David LeeCommented:
"I want to query every email id in the sent folder and after selection there sub folders."
Sorry, but I don't understand.  If you're saying that you want to create a report of all messages to all senders, then that's not what you asked for.  You said, "I want a macro when run on the sent items gets me a stats on the no of mails i have sent to a person".  The key words are "a person".  "A" is singular, one person, as in I want to get stats on the number of mails I have sent to one person.  That's what I provided.  If you want a report of the number of messages to every person represented in sent items, then that's a different matter.
0
 
bsharathAuthor Commented:
Sorry David
I want a report of mails sent to a person (For all persons) in the sent items. I guess i missed "ALL'
If thats a different approach i shall post a related post.
And for a single user also i get "0" found for any email id i mention
0
 
bsharathAuthor Commented:
Sorry David
I want a report of mails sent to a person (For all persons) in the sent items. I guess i missed "ALL'
If thats a different approach i shall post a related post.
And for a single user also i get "0" found for any email id i mention
0
 
David LeeCommented:
"If thats a different approach i shall post a related post."
Yes, counting for all recipients is a bit different than counting for one recipient.  

"And for a single user also i get "0" found for any email id i mention"
I tested the code before posting, so I know it works.  Two questions.

1.  How are you entering the names?  Give me an example.
2.  Do the names appear in your contacts or global address list?
0
 
bsharathAuthor Commented:
Ok once this is done shall post a related Q... Thank U... :-)
1. Entering the names as "Sharath@plc.com"
2. Some appear in the contact and all in Global contact list
0
 
bsharathAuthor Commented:
Ok once this is done shall post a related Q... Thank U... :-)
1. Entering the names as "Sharath@plc.com"
2. Some appear in the contact and all in Global contact list
0
 
David LeeCommented:
For people in your company enter a name, not an address.  The reason it isn't finding matches is because Exchange uses x.400 addresses internally, not SMTP addresses.  Internally you aren't "Sharath@plc.com".  The X.400 address looks very different.  
0
 
bsharathAuthor Commented:
Ok...
I get this

---------------------------
Message Counter
---------------------------
Processing Aborted

The name sharath yut could not be resolved.
---------------------------
OK  
---------------------------

0
 
bsharathAuthor Commented:
Ok...
I get this

---------------------------
Message Counter
---------------------------
Processing Aborted

The name sharath yut could not be resolved.
---------------------------
OK  
---------------------------

0
 
David LeeCommented:
The name has to match a name that's in your contacts or the GAL.  
0
 
bsharathAuthor Commented:
Yes they are the same.. Say my email is
Sharath.yut@plc.com
When resolved it shows as
Sharath yut
That is what i am using to find a sent item.
0
 
bsharathAuthor Commented:
Yes they are the same.. Say my email is
Sharath.yut@plc.com
When resolved it shows as
Sharath yut
That is what i am using to find a sent item.
0
 
David LeeCommented:
Replace the code in the class module with the version below.  I added a procedure that'll allow us to see the address we're searching for.
Private Const CLASSNAME = "Message Counter"
 
Private strSender As String, _
    olkRecipient As Outlook.Recipient, _
    bolSubfolders As Boolean, _
    intCount As Integer
 
Public Property Let Sender(strValue As String)
    strSender = strValue
End Property
 
Public Property Get Sender() As String
    Sender = strSender
End Property
 
Public Property Let Subfolders(bolValue As Boolean)
    bolSubfolders = bolValue
End Property
 
Public Property Get Subfolders() As Boolean
    Subfolders = bolSubfolders
End Property
 
Public Property Get ResolvedAddress() As String
    ResolvedAddress = olkRecipient.Address
End Property
 
Public Function Count() As Integer
    If strSender <> "" Then
        intCount = 0
        Set olkRecipient = Outlook.Session.CreateRecipient(strSender)
        olkRecipient.Resolve
        If olkRecipient.Resolved Then
            ProcessFolder Outlook.Session.GetDefaultFolder(olFolderSentMail)
        Else
            MsgBox "Processing Aborted" & vbCrLf & "The name " & strSender & " could not be resolved.", vbCritical + vbOKOnly, CLASSNAME
        End If
    Else
        MsgBox "Processing Aborted" & vbCrLf & "You must provide a name to search for.", vbCritical + vbOKOnly, CLASSNAME
    End If
    Count = intCount
End Function
 
Private Sub ProcessFolder(olkFolder As Outlook.Folder)
    Dim olkItem As Object, _
        olkAddressee As Outlook.Recipient, _
        olkSubfolder As Outlook.Folder
    On Error Resume Next
    For Each olkItem In olkFolder.Items
        Debug.Print olkItem.Subject
        For Each olkAddressee In olkItem.Recipients
            If olkAddressee.Address = olkRecipient.Address Then
                intCount = intCount + 1
                Exit For
            End If
        Next
    Next
    On Error GoTo 0
    If bolSubfolders Then
        For Each olkSubfolder In olkFolder.Folders
            ProcessFolder olkSubfolder
        Next
    End If
End Sub

Open in new window

0
 
David LeeCommented:
Replace CountSentMessages with the version below.  Try again and let me know what happens.
Sub CountSentMessages()
    Const MACRONAME = "Count Sent Messages"
    Dim objCounter As New MessageCounter
    With objCounter
        .Sender = InputBox("Enter the name of email address of the person to count for.", MACRONAME)
        .Subfolders = (MsgBox("Check subfolders too?", vbQuestion + vbYesNo, MACRONAME) = vbYes)
        MsgBox "There were " & .Count() & " items sent to " & .Sender & " (" & .ResolvedAddress & ")", vbInformation + vbOKOnly, MACRONAME
    End With
    Set objCounter = Nothing
End Sub

Open in new window

0
 
bsharathAuthor Commented:
I get this

---------------------------
Count Sent Messages
---------------------------
There were 0 items sent to sharath uyt (/o=iGroup/ou=First Administrative Group/cn=Recipients/cn=Sharathu)
---------------------------
OK  
---------------------------
0
 
bsharathAuthor Commented:
I get this

---------------------------
Count Sent Messages
---------------------------
There were 0 items sent to sharath uyt (/o=iGroup/ou=First Administrative Group/cn=Recipients/cn=Sharathu)
---------------------------
OK  
---------------------------
0
 
David LeeCommented:
You are 100% certain that there are messages addressed to Sharath Uyt in Sent Items, correct?
0
 
bsharathAuthor Commented:
Yes 100% sure...

Just to confirm
The script searches all mails and folders from the folder that i select right.
Or the maibox sent items...
0
 
bsharathAuthor Commented:
Yes 100% sure...

Just to confirm
The script searches all mails and folders from the folder that i select right.
Or the maibox sent items...
0
 
David LeeCommented:
"I want a macro when run on the sent items"

You only mentioned sent items, so that's what it's searching.  
0
 
bsharathAuthor Commented:
Sorry again...
:-(
I have the sent items in a folder in a specific PST.
Sent (Pst Name)
>Sent (Folder Name) Not the actual sent
>> All sub folders

Until now i was clicking on the sent folder and running the code was in an asumption its reading what i have selected...
So by default the script reads the Mailbox sent folder. Can you change it to read on the sent folder and sub folders that i have selected... While running
0
 
bsharathAuthor Commented:
Sorry again...
:-(
I have the sent items in a folder in a specific PST.
Sent (Pst Name)
>Sent (Folder Name) Not the actual sent
>> All sub folders

Until now i was clicking on the sent folder and running the code was in an asumption its reading what i have selected...
So by default the script reads the Mailbox sent folder. Can you change it to read on the sent folder and sub folders that i have selected... While running
0
 
bsharathAuthor Commented:
Thanks a lot David...Worked perfect
related post here please have a look
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24334438.html
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.