[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
269 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

868 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