Count dates

  • Thread starter Thread starter stevewy
  • Start date Start date
S

stevewy

I have Outllok 2003 running on an Exchange server. I don't normally
program VBA in Outlook, only Word or Excel.

Is there a way I could do a little macro to count the messages in a
folder and produce a list of how many messages are present for each
date (like 26 for Aug 10, 33 for Aug 9, 12 for Aug 8, etc)?
 
I have Outllok 2003 running on an Exchange server.
Click to expand...

I hope you don't mean that. Outlook running on the Exchange Server
is not a good thing.
I don't normally
program VBA in Outlook, only Word or Excel.
Click to expand...

The techique below is not very Outlook-specific.
Is there a way I could do a little macro to count the messages in a
folder and produce a list of how many messages are present for each
date (like 26 for Aug 10, 33 for Aug 9, 12 for Aug 8, etc)?
Click to expand...

VBA's Dictionary Object springs to mind as an appropriate tool.
Here's some code that should get you started:

Sub CounDates()

Dim fldFolder As MAPIFolder
Dim dctDict As New Scripting.Dictionary
Dim itmMail As MailItem
Dim strDate As String
Dim itmNote As NoteItem
Dim i As Long

Set fldFolder = ActiveExplorer.CurrentFolder
Set dctDict = CreateObject("Scripting.Dictionary")

' This will fail if there are items other than MailItems in the folder
For Each itmMail In fldFolder.Items
strDate = Format(itmMail.ReceivedTime, "YYYY-MMM-DD")
dctDict.Item(strDate) = dctDict.Item(strDate) + 1
Next itmMail

' Saving the results in a NoteItem
Set itmNote = CreateItem(olNoteItem)
itmNote.Body = "Counted " & fldFolder.Items.Count & " mail items for " _
& dctDict.Count & " dates in folder """ & fldFolder.Name & """:" & vbCr
For i = 0 To dctDict.Count - 1
itmNote.Body = vbCr & itmNote.Body & dctDict.Keys(i) & ": " & dctDict.Items(i) & vbCr
Next i
MsgBox "Counted " & fldFolder.Items.Count & " mail items for " _
& dctDict.Count & " dates in folder """ & fldFolder.Name & """.", vbOKOnly + vbInformation, "CountDates"
itmNote.Display

End Sub
 
I'm getting an error when I run it. The error comes on the line "Dim
dctDict As New Scripting.Dictionary" and says "Compile error: user
defined type not defined".
 
Put a reference to the Scripting object in your VBA project references.

And never, ever run Outlook on the Exchange server, it's a bad idea.
 
Ken said:
Put a reference to the Scripting object in your VBA project references.

And never, ever run Outlook on the Exchange server, it's a bad idea.
Click to expand...

Well, I don't actually know that it's running *on* the server. I think
maybe the e-mail accounts are stored on there. I leave that to the
techies.

Err... how do I put a reference to the Scripting object in your VBA
project references exactly?
 
Tools, References.

Set the reference to Microsoft Scripting Runtime (SCRRUN.DLL).
 
..... and thanks to Michael too, of course, who I just realised supplied
the original macro!
 
Back
Top