Outlook VBA - Iterate through items in sent box and count

[utahjzz98]

I have a piece of code that iterates through the sent items folder in Outlook and sends an email back with information regarding those sent items. However, as I am iterating through those items I need to seperate them and get a count by day. I can't seem to wrap my head around how to do this. Below is my current code. Any suggestions?

Private Sub Check_Mailbox()
    Dim oNS             As NameSpace
    Dim oFolder         As MAPIFolder
    Dim strBody         As String
    Dim objMsg          As MailItem
    Dim intCount        As Integer
    Dim i               As Integer
    Dim iMeetCount      As Integer
    Dim dtPrevDate      As Date
    Dim blnDateChanged  As Boolean
        
    Set oNS = GetNamespace("MAPI")
    Set oFolder = oNS.GetDefaultFolder(olFolderSentMail)
    
    intCount = oFolder.Items.Count
    Set objMsg = Application.CreateItem(olMailItem)
    iMeetCount = 0
    dtPrevDate = Date
    
     For i = 1 To intCount
        Select Case oFolder.Items(i).Class
            Case olAppointment
                'Don't do anything at this time, might need this functionality later
            Case olContact
                'Don't do anything at this time, might need this functionality later
            Case olMail
                'Don't do anything at this time, might need this functionality later
            Case olMeetingRequest
                dtPrevDate = oFolder.Items(i).CreationTime
                strBody = strBody & "Creation Time: " & oFolder.Items(i).CreationTime & vbCrLf
                strBody = strBody & "Subject: " & oFolder.Items(i).Subject & vbCrLf
                strBody = strBody & "To: " & oFolder.Items(i).Recipients(1) & vbCrLf & vbCrLf
                
                iMeetCount = iMeetCount + 1
        End Select
    Next
    
    strBody = strBody & vbCrLf & "Total Items: " & iMeetCount
    
    objMsg.To = "email address"
    objMsg.Subject = "Test"
    objMsg.Body = strBody
    objMsg.Send
    
    Set oNS = Nothing
    Set oFolder = Nothing
    Set oNewMail = Nothing
    Set objMsg = Nothing
End Sub

 

[xlbo]

Depends what you want to do with the count

Presumably it will be based on the variable dtPrevDate?

Is there any way of knowing what dates might be used or could this span any amount of dates?

Maybe an 2 dimensional array where you

1: iterate through the 1st dimension to see if the date has been stored already
2: If it has not been stored, add it to the 1st dimension and make the corresponding element of the 2nd dimension = 1
3: If date already exists, increment the value in the corresponding element of the 2nd dimension by 1

That would give you an array of dates and a count for each date - what you would do with it then I do not know ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question