Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 37 ยท Working with Word and Outlook

Outlook E-mail Automation with VBA

Send, read and organize e-mail with Outlook VBA: MailItem, attachments, HTML bodies, folders, Items.Restrict, appointments and the security prompts to expect.

Outlook's object model lets you send mail, read the inbox, save attachments, create appointments and tasks, and react to incoming messages. A common pattern is to build a report in Excel and then send it to a list of people with one macro.

The top object is Outlook.Application. Its GetNamespace("MAPI") gives access to folders and items, and CreateItem makes new mail, appointments, contacts and tasks. This lesson covers sending, reading and filtering, attachments and calendar items, and how to avoid the problems that surprise newcomers.

VBA is read-only on this site. The runnable stand-ins model the logic: building safe recipient lists, filtering messages and formatting an HTML body.

Sending a message

VB
Sub SendReport()
    Dim olApp As Object, mail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)              ' 0 = olMailItem

    With mail
        .To = "[email protected]"
        .CC = "[email protected]"
        .Subject = "Monthly report"
        .HTMLBody = "<p>Hello Ada,</p><p>The report is attached.</p>"
        .Attachments.Add ThisWorkbook.Path & "\Report.xlsx"
        .Display            ' show it for review
        ' .Send             ' send straight away
    End With
End Sub

Use .Display while developing: you can see the message before anything is sent. Switch to .Send only when you are sure. Multiple recipients are separated by semicolons. For a polished body, build HTMLBody as a string with <table> or <p> tags and escape user data.

If you work with the late-bound Object type, you must use numeric constants (0 for mail). With a reference to the Outlook library you get names like olMailItem.

Sending from a list in Excel

VB
Sub SendToAll()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Customers")
    Dim olApp As Object: Set olApp = CreateObject("Outlook.Application")
    Dim r As Long, mail As Object

    For r = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If ws.Cells(r, 3).Value2 = "Yes" Then
            Set mail = olApp.CreateItem(0)
            mail.To = ws.Cells(r, 2).Value2
            mail.Subject = "Hello " & ws.Cells(r, 1).Value2
            mail.Body = "Hi " & ws.Cells(r, 1).Value2 & "," & vbCrLf & "..."
            mail.Display
        End If
    Next r
End Sub

Respect the people you mail: send only to those who agreed, keep volumes sensible and include an unsubscribe route for any bulk message.

Reading the inbox

VB
Sub ListUnread()
    Dim ns As Object, inbox As Object, item As Object
    Set ns = CreateObject("Outlook.Application").GetNamespace("MAPI")
    Set inbox = ns.GetDefaultFolder(6)          ' 6 = olFolderInbox

    For Each item In inbox.Items.Restrict("[Unread] = True")
        If item.Class = 43 Then                  ' 43 = olMail
            Debug.Print item.ReceivedTime, item.SenderName, item.Subject
        End If
    Next item
End Sub

Items.Restrict filters inside Outlook and is far faster than looping over everything. Always test Class, because a folder can contain meeting requests and reports as well as mail. Other default folders: 5 = sent items, 9 = calendar, 10 = contacts, 3 = deleted items.

Saving attachments

VB
Dim att As Object
For Each att In item.Attachments
    If LCase$(Right$(att.FileName, 5)) = ".xlsx" Then
        att.SaveAsFile "C:\Inbox\" & att.FileName
    End If
Next att

Check the file type and never open or run attachments automatically; they can be malicious.

Moving and categorizing

VB
item.Categories = "Processed"
item.UnRead = False
item.Move ns.GetDefaultFolder(6).Folders("Done")
item.Save

When you move or delete items while looping, loop backwards with a For i = items.Count To 1 Step -1 loop, otherwise the indexes shift and you skip messages.

Calendar items

VB
Dim appt As Object
Set appt = olApp.CreateItem(1)                  ' 1 = olAppointmentItem
With appt
    .Subject = "Team meeting"
    .Start = DateSerial(2027, 3, 15) + TimeSerial(10, 0, 0)
    .Duration = 60
    .ReminderMinutesBeforeStart = 15
    .Location = "Room 2"
    .Save
End With

Events and security

Application_NewMailEx and similar event handlers in ThisOutlookSession run when mail arrives. Outlook may show a security prompt when a macro touches the address book or sends mail on older configurations; the supported answer is to sign your macro, not to click-bypass tools. Macros are disabled by default in many organizations, and for good reason: treat any VBA that sends mail as sensitive code.

Try it: filtering and an HTML body

JS Restrict-style filter over messages

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Build a safe HTMLBody table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Calling .Send while testing. Use .Display first.
  • Forgetting to check item.Class, so a meeting request raises an error on .SenderName.
  • Deleting or moving items in a forward For Each loop.
  • Putting unescaped data in HTMLBody.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which method shows a message without sending it?

    Display opens the message window so you can review it; Send sends immediately.
  2. 2. Why use Items.Restrict?

    Restrict lets Outlook do the filtering instead of VBA touching each item.
  3. 3. How should you loop when moving or deleting items?

    Removing items shifts later indexes, so iterate from the end.
  4. 4. What does GetDefaultFolder(6) return?

    6 is olFolderInbox. The calendar is 9, sent items 5 and contacts 10.

Summary

  • Create mail with CreateItem(0), fill To, Subject, HTMLBody, Attachments, and use Display to test.
  • Read folders through GetNamespace("MAPI").GetDefaultFolder(...) and filter with Restrict.
  • Check Class, save attachments carefully and loop backwards when changing a folder.
  • Appointments, contacts and tasks use the same CreateItem approach.

Next: PowerPoint and Cross-Application Automation