Solved

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

Posted on 2008-10-29
15
197 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:bsharath
  • 8
  • 7
15 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 22865731
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22865740
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
 
LVL 11

Author Comment

by:bsharath
ID: 22865759
I get this

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

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

I Changed the location but still get this...
0
 
LVL 11

Author Comment

by:bsharath
ID: 22865760
I get this

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

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

I Changed the location but still get this...
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22865822
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22865839
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
 
LVL 11

Author Comment

by:bsharath
ID: 22865865
Sorry Chris did not follow the above comment...

Should i replace something...
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 11

Author Comment

by:bsharath
ID: 22865866
Sorry Chris did not follow the above comment...

Should i replace something...
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22865923
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
 
LVL 11

Author Comment

by:bsharath
ID: 22868063
Thanks Chris worked perfect....
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22868335
Good ... sorry about the confusion in my attempt to help!

Chris
0
 
LVL 11

Author Comment

by:bsharath
ID: 22868767
0
 
LVL 11

Author Comment

by:bsharath
ID: 22868949
No problem Chris ...
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22870688
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
 
LVL 11

Author Comment

by:bsharath
ID: 22871280
Ok Chris...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
Set OWA language and time zone in Exchange for individuals, all users or per database.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
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

17 Experts available now in Live!

Get 1:1 Help Now