Solved

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

Posted on 2009-04-15
30
250 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:bsharath
  • 17
  • 12
30 Comments
 
LVL 9

Expert Comment

by:IvaSoft
ID: 24155717
Hire a programmer or write a macro yourself.

Regards,
Victor
0
 
LVL 76

Expert Comment

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

Expert Comment

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

Expert Comment

by:David Lee
ID: 24163246
I have a typo in the first code snippet.  Change line 17 to read

    bolSubfolders = bolValue
0
 
LVL 11

Author Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

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

Author Comment

by:bsharath
ID: 24175187
Ok...
I get this

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

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

0
 
LVL 11

Author Comment

by:bsharath
ID: 24175188
Ok...
I get this

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

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

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 76

Expert Comment

by:David Lee
ID: 24175672
The name has to match a name that's in your contacts or the GAL.  
0
 
LVL 11

Author Comment

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

Author Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

by:David Lee
ID: 24175896
You are 100% certain that there are messages addressed to Sharath Uyt in Sent Items, correct?
0
 
LVL 11

Author Comment

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

Author Comment

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

Expert Comment

by:David Lee
ID: 24175937
"I want a macro when run on the sent items"

You only mentioned sent items, so that's what it's searching.  
0
 
LVL 11

Author Comment

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

Author Comment

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

Accepted Solution

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

Author Comment

by:bsharath
ID: 24176063
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now