CODEWITHSUNDEEP
vbaemailoutlookoutlook-2003
Duncan
Duncan

Reputation: 1

Get count of messages by received date

I have the following code for counting gross number of e-mails in Outlook:

Sub Test()

    Dim objOutlook As Object, objnSpace As Object, objFolder As Object
    Dim EmailCount As Integer

    Set objOutlook = CreateObject("Outlook.Application")

    Set objnSpace = objOutlook.GetNamespace("MAPI")

    On Error Resume Next

    Set objFolder = objnSpace.Folders("MyFolder").Folders("Spam")

    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "No such folder."
        Exit Sub
    End If

    EmailCount = objFolder.Items.Count
    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing

    MsgBox "Number of emails in the folder: " & EmailCount, , "Number of spam messages "

End Sub

Could someone please give me some way to count for a particular day of the week?

Examples:

Or even for a particular date

Upvotes: -1

Views: 2877

Answers (3)

Isaac Comer-Wyrd
Isaac Comer-Wyrd

Reputation: 51

Well this is what I've got. There does not appear to be any simple property or method to give you the answer you're wanting. I don't know if there's any way to reference the mail items through a SQL query. If there were, that would be the ideal approach. Since that doesn't seem to be possible, you're only option is to iterate over ALL the items in the folder (or folders) and have several counter variables for the days of the week. Then you increment the appropriate counter for each item.

The main problem is simply figuring out how to reference the appropriate object.

Tested in MS Outlook 2003 and appears to be working. BUT--I haven't check to see if it's counting every item in every sub-folder of Inbox or if it's just giving a count of items in Inbox itself, but not any of the items in sub-folders of the Inbox. Anyway, her it is:

Sub CountEmailsByDayOfWeek()
 'count mail items by day-of-week
 'Isaac Comer, 6-3-2011

 Dim EmCtSun, EmCtMon, EmCtTue, EmCtWed, EmCtThu, EmCtFri, EmCtSat As Long

 Dim myInbox As Variant
 Dim BUNCH_OF_Items As Items
 Dim myInboxItem As Variant

 Set myInbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
 Set BUNCH_OF_Items = myInbox.Items

 ' < Handle any errors like No such folder here >

 ' clear counters for each day-of-week 
 ' "EmCt" means "Email Count". It wasn't a good choice of variable name 
 ' because some "Items" aren't "Emails", but I'm not fixing it right now
 EmCtSun = 0: EmCtMon = 0: EmCtTue = 0: EmCtWed = 0: EmCtThu = 0: EmCtFri = 0: EmCtSat = 0


' disabled test case with the "first" item in Inbox--testing to make sure I had the object reference correct
'     Set myInboxItem = BUNCH_OF_Items(1)
'          With myInboxItem
'               Debug.Print Chr(34) & .Subject & Chr(34) & " Received: "; .ReceivedTime
'               Debug.Print DatePart("w", .ReceivedTime)
'          End With

 For Each myInboxItem In BUNCH_OF_Items
      With myInboxItem
           'disabled code that shows the subject line and received time
           'Debug.Print Chr(34) & .Subject & Chr(34) & " Received: "; .ReceivedTime
           Select Case DatePart("w", .ReceivedTime) ' get the numeric day-of-week corresponding to the vb constants
                Case vbSunday
                     EmCtSun = EmCtSun + 1
                Case vbMonday
                     EmCtMon = EmCtMon + 1
                Case vbTuesday
                     EmCtTue = EmCtTue + 1
                Case vbWednesday
                     EmCtWed = EmCtWed + 1
                Case vbThursday
                     EmCtThu = EmCtThu + 1
                Case vbFriday
                     EmCtFri = EmCtFri + 1
                Case vbSaturday
                     EmCtSat = EmCtSat + 1
           End Select
      End With
 Next

 ' output
 ' send output both to a msgbox and the immediate window.
 Debug.Print "Counts for Emails received Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday respectively."
 Debug.Print EmCtSun; EmCtMon; EmCtTue; EmCtWed; EmCtThu; EmCtFri; EmCtSat
 MsgBox _
      "Sunday:" & vbTab & vbTab & EmCtSun & vbCrLf & _
      "Monday:" & vbTab & vbTab & EmCtMon & vbCrLf & _
      "Tuesday:" & vbTab & vbTab & EmCtTue & vbCrLf & _
      "Wednesday:" & vbTab & EmCtWed & vbCrLf & _
      "Thursday:" & vbTab & EmCtThu & vbCrLf & _
      "Friday:" & vbTab & vbTab & EmCtFri & vbCrLf & _
      "Saturday:" & vbTab & EmCtSat, _
      vbOKOnly + vbInformation, "Count of Items By Day-of-week Received"
End Sub

If this code was helpful please vote for me. I need points. :-)

Upvotes: 0

Duncan
Duncan

Reputation: 11

Perhaps add argument (Optional dteDate As Date) to whole method/function and contain the following code within the body

Select Case Weekday(dteDate)
  Case vbMonday  
    IsCorrectDay = True
  Case Else  
    IsCorrectDay = False
End Select

For Each MapiItem In MapiFolderInbox.Messages
  If MapiItem.TimeReceived = IsCorrectDay Then count = count + 1     
Next MapiItem

Upvotes: 1

ColWhi
ColWhi

Reputation: 1077

Looks like you will have to look at each message and use the TimeReceived property.

For Each MapiItem In MapiFolderInbox.Messages
            If MapiItem.TimeReceived = dte Then count = count + 1
        Next MapiItem

or something like that.

Upvotes: 0

Related Questions