Solved

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

Posted on 2008-10-29
15
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Changing a few Outlook Options can help keep you organized!
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

624 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