Retrieve Outlook email count from sent folder of the last month (through word VBA)

Question

I've been trying to filter my outlook sent folder in Word to get the count of the total emails from last month.

' Connect to outlook
Dim outlook As Object
Dim NumEmails As Long
Dim name_space As Object
Dim SentFolder As MAPIFolder
Dim criterion As String

Set outlook = CreateObject("Outlook.Application")
Set name_space = outlook.GetNamespace("MAPI")

On Error Resume Next
Set SentFolder = name_space.GetDefaultFolder(olFolderSentMail)
If Err.Number <> 0 Then
    Err.Clear
    MsgBox "No such folder."
    Exit Sub
End If

If Month(Date) = 1 And Day(Date) < 27 Then
    ' It's January but we're reporting Decemember
    criterion = ""
ElseIf Day(Date) > 27 Then
    ' It's the end of the month; pull this month's data
    criterion = ""
Else
    ' It's not the end of the month; pull last month's data
    criterion = ""
End If

Is what I got so far, but I'm stuck what to put as my criterion variable and how to utilize it for filtering the folder.

Any help will be appreciated.

You should find information about finding the first day and last day of a month. You can feed those dates into a Restrict similar to the way it is used in https://stackoverflow.com/questions/51450541/search-outlook-emails-from-vba — niton, Jul 24 '18 at 17:35

score 0 · Answer 1 · answered Sep 12 '18 at 21:17

With date code from one of multiple possible sites you can filter like this:

Option Explicit

Private Sub ItemsByMonth()

    Dim myStart As Date
    Dim myEnd As Date

    Dim outlook As Object
    Dim name_space As Object
    Dim SentFolder As Object

    Dim oItems As Items
    Dim oitem As Object

    Dim strRestriction As String
    Dim oResItems As Items

    Set outlook = CreateObject("Outlook.Application")
    Set name_space = outlook.GetNamespace("MAPI")

    ' http://www.anysitesupport.com/vba-time-and-date-functions/
    If Day(Date) < 27 Then
        'Last day of previous month
        myEnd = DateSerial(Year(Date), Month(Date), 0)
    Else
        'Last day of month
        myEnd = DateSerial(Year(Date), Month(Date) + 1, 0)
    End If

    ' First day of the myEnd month
    myStart = DateSerial(Year(myEnd), Month(myEnd), 1)

    Set SentFolder = name_space.GetDefaultFolder(olFolderSentMail)
    Set oItems = SentFolder.Items

    strRestriction = "[SentOn] <= '" & myEnd & "' AND [SentOn] >= '" & myStart & "'"
    Set oResItems = oItems.Restrict(strRestriction)
    Debug.Print oResItems.count

ExitRoutine:
    Set outlook = Nothing
    Set name_space = Nothing
    Set SentFolder = Nothing
    Set oItems = Nothing
    Set oResItems = Nothing

End Sub

Retrieve Outlook email count from sent folder of the last month (through word VBA)

1 Answers1