The following VBA example demonstrates how to analyze emails within the « Sent Items » folder in Outlook to answer questions such as:
- What percentage of all emails contain at least one attachment?
- On average, how many attachments does an email with attachments have?
- When and to whom was a specific file sent as an email attachment?
Sub AccessMailAttachments()
Dim appOutlook As Outlook.Application
Dim ns As Outlook.Namespace
Dim folder As Outlook.Folder
Dim mailItem As Outlook.MailItem
Dim attachment As Outlook.Attachment
Dim countWithAttachments As Integer
Dim percentWithAttachments As Single
Dim totalAttachments As Integer
Dim attachmentsPerMail As Single
Dim output As String
Const maxItems As Integer = 100
Dim processedItems As Integer
' Start Outlook application
Set appOutlook = CreateObject("Outlook.Application")
' Get MAPI namespace
Set ns = appOutlook.GetNamespace("MAPI")
' Access the Sent Items folder
Set folder = ns.GetDefaultFolder(olFolderSentMail)
' Initialize counters
processedItems = 0
countWithAttachments = 0
totalAttachments = 0
' Loop through emails in the folder (limit to maxItems)
For Each mailItem In folder.Items
processedItems = processedItems + 1
' Check if email has attachments
If mailItem.Attachments.Count > 0 Then
countWithAttachments = countWithAttachments + 1
totalAttachments = totalAttachments + mailItem.Attachments.Count
End If
' Stop if maxItems processed
If processedItems > maxItems Then Exit For
Next mailItem
' Calculate percentage of emails with attachments
percentWithAttachments = countWithAttachments / folder.Items.Count
MsgBox Format(percentWithAttachments, "0.00 %") & _
" of emails in the 'Sent Items' folder have attachments."
' Calculate average number of attachments per email with attachments
If countWithAttachments > 0 Then
attachmentsPerMail = totalAttachments / countWithAttachments
Else
attachmentsPerMail = 0
End If
MsgBox Format(attachmentsPerMail, "0.00") & _
" attachments per email that contains attachments."
' Search for a specific attachment by filename
processedItems = 0
output = ""
For Each mailItem In folder.Items
processedItems = processedItems + 1
For Each attachment In mailItem.Attachments
If attachment.FileName = "Mappe9.xlsm" Then
output = output & "Sent to " & mailItem.To & _
" on " & mailItem.CreationTime & vbCrLf
End If
Next attachment
If processedItems > maxItems Then Exit For
Next mailItem
' Display results if the file was found
If output <> "" Then MsgBox output
' Clean up
appOutlook.Quit
Set attachment = Nothing
Set mailItem = Nothing
Set folder = Nothing
Set ns = Nothing
Set appOutlook = Nothing
End Sub
Explanation:
This script uses a For Each loop to iterate over all items in the « Sent Items » folder of Outlook. Since processing a very large number of emails can take a long time, the script limits the examination to the first 100 items by using a constant (maxItems) and a counter (processedItems). You can adjust maxItems to analyze more or fewer emails.
For each email (mailItem), the code checks if it contains any attachments by inspecting the Attachments.Count property. If the email has attachments, it increments the counter for emails with attachments and adds the number of attachments to a total count.
After processing, it calculates and displays:
- The percentage of emails that contain at least one attachment relative to all emails in the folder.
- The average number of attachments per email that has attachments.
Next, using nested loops, the code examines every attachment in each email to find any attachment matching a specific filename — in this case, « Mappe9.xlsm ». For every match found, the script collects and outputs the recipient (mailItem.To) and the creation date/time of the email (mailItem.CreationTime).
Finally, the Outlook application and all object references are properly released to free resources.