Find no of mails sent to one user.In a particular folders are the mails.

Hi,

Find no of mails sent to one user.In a particular folders are the mails.
Outlook 2007 has sent mails in a folder called "Nas"

I have been sending repeated mails to employers on a specific feedback. many have replyed but some have not. So need a macro that can query the folder and tell me how many mails i have sent to one employer
Like

Sharath 10
ramesh 6
Ganesh 2

Into an excel or txt file.

Regards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
The following work at all?

Chris
Sub countItems()
Dim olApp As Object
Dim olNS As Object
Dim olSrcFolder As Object
Dim olMai As Object
Dim olMaiCopy As Object
Dim folderItems As Object
Dim SortedItems As Object
Dim olItemCount As Long
Dim arrSenders() As String
Dim strfilter As String
Dim strReport As String
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
    
    outputPath = "c:\MacroData"
    outputFileName = "mailAddyCount.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(outputPath) Then
        fso.CreateFolder outputPath
    End If
 
    Set olApp = GetObject(, "outlook.application")
    If TypeName(olApp) <> "Application" Then Set olApp = CreateObject("outlook.application")
    Set olNS = olApp.GetNamespace("MAPI")
    Set olSrcFolder = olNS.PickFolder
    Set folderItems = olSrcFolder.Items
    folderItems.SetColumns "Senderemailaddress, ReceivedTime"
    folderItems.Sort "Senderemailaddress", False
    Set SortedItems = folderItems
    ReDim arrSenders(1 To 1)
    arrSenders(1) = SortedItems(1).SenderEmailAddress
    For olItemCount = 1 To SortedItems.count
        If SortedItems(olItemCount).Class = olMail Then
            If SortedItems(olItemCount).SenderEmailAddress <> arrSenders(UBound(arrSenders)) Then
                ReDim Preserve arrSenders(1 To UBound(arrSenders) + 1)
                arrSenders(UBound(arrSenders)) = SortedItems(olItemCount).SenderEmailAddress
            End If
        End If
    Next
    For olItemCount = 1 To UBound(arrSenders)
        strfilter = "[SenderEmailAddress] = " & append_quotes(arrSenders(olItemCount))
        Set SortedItems = folderItems.Restrict(strfilter)
        SortedItems.Sort "ReceivedTime", False
        strReport = strReport & SortedItems(SortedItems.count).SenderEmailAddress & vbTab & SortedItems.count & vbCrLf
    Next
    Set outputFile = fso.CreateTextFile(outputPath & "\" & outputFileName, True)
    outputFile.Write strReport
    outputFile.Close
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris BottomleySoftware Quality Lead EngineerCommented:
Default data relating to the output file you may to edit is:

    outputPath = "c:\MacroData"
    outputFileName = "mailAddyCount.txt"

which will produce the file:
c:\MacroData\mailAddyCount.txt

Chris
0
bsharathAuthor Commented:
I get this

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Ambiguous name detected: append_quotes
---------------------------
OK   Help  
---------------------------

I Changed the location but still get this...
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:
I get this

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Ambiguous name detected: append_quotes
---------------------------
OK   Help  
---------------------------

I Changed the location but still get this...
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Ok, I put the script in the same module as some of the earlier work ... just need to add the quotes routine to wherever you put the above script.

Chris
Function append_quotes(objString As String) As String
    append_quotes = "'" & CStr(objString) & "'"
End Function

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
FYI ... and as I understand it, Ambiguous name detected means that more than one copy of the named routine exists in modules in outlook.  If none existed it would indicate not found, "sub or function not found".

Placing a copy in the specific code module means it is found and over-rules any copies in other modules.  Placing two copies in the module of course returns the ambiguous scenario again.

Chris
0
bsharathAuthor Commented:
Sorry Chris did not follow the above comment...

Should i replace something...
0
bsharathAuthor Commented:
Sorry Chris did not follow the above comment...

Should i replace something...
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Yup

paste the three lines in along with the module in the first post.

My last post was meant to clarify why you got the ambiguous message instead of the not found one but can simply be ignored.

i.e. add the following to the module containg sub countItems

Chris
Function append_quotes(objString As String) As String
    append_quotes = "'" & CStr(objString) & "'"
End Function

Open in new window

0
bsharathAuthor Commented:
Thanks Chris worked perfect....
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Good ... sorry about the confusion in my attempt to help!

Chris
0
bsharathAuthor Commented:
No problem Chris ...
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Just been out will look and see if I can figure anything out in the morning ... unless you have a solution by then

Chris
0
bsharathAuthor Commented:
Ok Chris...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

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.