Counting Outlook outbox items and logging to a file

Question

I need a macro that would count the number of sent emails (all in one sent folder, no subfolders) and log the result to a file (csv or txt). The output should file should state number of emails per date, sender address/name and recipient's domain (@company.com).

I was able to get this code to work partially, but it only shows date and number of emails in the output file.

Also - is there a way to append new data to the file, not to overwrite it?

Message Box in Outlook is really optional, output file is the critical part.

Sub HowManyEmails()

    Dim objOutlook As Object, objnSpace As Object, objFolder As MAPIFolder
    Dim EmailCount As Integer
    Set objOutlook = CreateObject("Outlook.Application")
    Set objnSpace = objOutlook.GetNamespace("MAPI")

    On Error Resume Next
    Set objFolder = objnSpace.Folders("xxx@company.com").Folders("Outbox")
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "No such folder."
        Exit Sub
    End If

    EmailCount = objFolder.Items.Count

    MsgBox "Number of emails in the folder: " & EmailCount, , "email count"

    Dim dateStr As String
    Dim myItems As Outlook.Items
    Dim dict As Object
    Dim msg As String
    Set dict = CreateObject("Scripting.Dictionary")
    Set myItems = objFolder.Items
    myItems.SetColumns ("ReceivedTime")
    ' Determine date of each message:
    For Each myItem In myItems
        dateStr = GetDate(myItem.ReceivedTime)
        If Not dict.Exists(dateStr) Then
            dict(dateStr) = 0
        End If
        dict(dateStr) = CLng(dict(dateStr)) + 1
    Next myItem

    ' Output counts per day:
    msg = ""
    For Each o In dict.Keys
        msg = msg & o & ": " & dict(o) & " items" & vbCrLf
    Next

    Dim fso As Object
    Dim fo As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fo = fso.CreateTextFile("C:\Users\xxx\Documents\outlook_test_log.txt")
    fo.Write msg
    fo.Close

    Set fo = Nothing
    Set fso = Nothing
    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
End Sub

Function GetDate(dt As Date) As String
    GetDate = Year(dt) & "-" & Month(dt) & "-" & Day(dt)
End Function

To append to an existing file, use `OpenTextFile` with parameter `ForAppending` as explained [here](https://msdn.microsoft.com/en-us/library/aa265347(v=vs.60).aspx). — Axel Kemper, Feb 22 '17 at 11:33
Do you expect emails to be sitting in the Outbox waiting for delivery? Or do you want to catch emails that are being delivered as they are sent? — Eric Legault, Feb 22 '17 at 23:32
Oh sorry, I made a mistake - actually I want to log emails that have already been sent - not waiting for delivery or logging them on the fly. So basically to go through sent emails once a day and log items matching the criteria to a file... — ondas, Feb 23 '17 at 12:44
To get to the correct folder change "Outbox" to "Sent Items". To get information from individual items this may be helpful http://stackoverflow.com/questions/21556389/iterate-all-email-items-in-a-specific-outlook-folder — niton, Feb 24 '17 at 11:27

score 0 · Answer 1 · edited Feb 22 '17 at 12:38

0

Why don't you create a VSTO Add-In? I think this isn't a VSTO Add-In, isn't it? As I remember you can access much more objects this way. If I'm wrong please correct me.

https://msdn.microsoft.com/de-de/library/cc668191.aspx (German version).

https://msdn.microsoft.com/en-us/uk-uk/library/cc668191.aspx (English version).

edited Feb 22 '17 at 12:38

Darren Bartrup-Cook

18,362
1
23
45

answered Feb 22 '17 at 11:57

Philipp Müller

56
9

Being in corporate environment I don't have access to Visual Studio (nor the experience). I was hoping there would be a simple way to expand vba code above to include sender address and recipient's domain and I am just missing it...? – ondas Feb 23 '17 at 12:39

score 0 · Answer 2 · answered Feb 25 '17 at 01:21

To access the data for the sender, look at the MailItem.SenderName and .SenderEmailAddress properties (or .Sender for more details). For recipients, you'll have to access Recipient objects in the MailItem.Recipients collection.

See these articles for helpful walkthroughs for all of that:

https://msdn.microsoft.com/en-us/library/office/ff866259.aspx

https://msdn.microsoft.com/en-us/library/office/ff868695.aspx

For recipients

Counting Outlook outbox items and logging to a file

2 Answers2