5 VBA Scripts to Automate Custom Outlook Reports from Excel

If you use Excel to manage your projects and tasks, it’s common to do repetitive work—checking Excel data, copying the necessary values, pasting them into an email, finding the right person’s address, and hitting send over and over again. For teams managing projects, tasks, schedules, or client lists, this manual process wastes hours every week and invites human error. VBA is Excel’s built-in automation language, and you can use it to automate sending emails via Outlook.

In this tutorial, we will show 5 VBA scripts to automate custom Outlook reports from Excel. Using data from your worksheet, you can automatically prepare and send emails with custom reports, meeting invites, and schedule reminders.

Imagine a project tracker where you need to send different Outlook reports to different team members for different tasks and meetings.

Add Outlook Reference:

Go to the Developer tab >> select Visual Basic
Or press Alt + F11 to open the VBA Editor
Go to Tools >> select References >> check “Microsoft Outlook xx.0 Object Library” (xx is your version, like 16.0 for recent Office)

Script 1: Send a Formatted HTML Email from a Selected Row (Project)

Your clients may ask for a status update on a specific project instead of the whole report. Instead of typing an email manually, you can select that project, run the macro, and a professionally formatted HTML email will appear in Outlook, pre-filled with the project’s details. It will be ready to review and send in seconds.

Go to the Developer tab >> select Visual Basic
From the Insert menu >> select Module
Copy and paste the following VBA script

VBA Code:

Sub SendRowEmailReport()
    Dim ws As Worksheet
    Dim outlookApp As Object
    Dim mail As Object
    Dim selectedRow As Long
    Dim recipient As String, projectName As String
    Dim status As String, notes As String
    Dim htmlBody As String

    Set ws = ActiveSheet
    selectedRow = ActiveCell.Row

    If selectedRow <= 1 Then
        MsgBox "Please select a data row (row 2 or below).", vbExclamation
        Exit Sub
    End If

    recipient   = ws.Cells(selectedRow, 1).Value
    projectName = ws.Cells(selectedRow, 3).Value
    status      = ws.Cells(selectedRow, 4).Value
    notes       = ws.Cells(selectedRow, 10).Value

    If recipient = "" Or InStr(recipient, "@") = 0 Then
        MsgBox "Row " & selectedRow & " has no valid email address.", vbExclamation
        Exit Sub
    End If

    htmlBody = "<html><body style='font-family:Arial,sans-serif;font-size:14px;'>"
    htmlBody = htmlBody & "<div style='background:#1F4E79;padding:20px;color:white;'>"
    htmlBody = htmlBody & "<h2 style='margin:0;'>Project Status Report</h2></div>"
    htmlBody = htmlBody & "<table style='width:100%;border-collapse:collapse;margin-top:16px;'>"
    htmlBody = htmlBody & "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;width:150px;'>Project</td>"
    htmlBody = htmlBody & "<td style='padding:8px;border-bottom:1px solid #eee;'>" & projectName & "</td></tr>"
    htmlBody = htmlBody & "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;'>Status</td>"
    htmlBody = htmlBody & "<td style='padding:8px;border-bottom:1px solid #eee;'>" & status & "</td></tr>"
    htmlBody = htmlBody & "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;'>Notes</td>"
    htmlBody = htmlBody & "<td style='padding:8px;'>" & notes & "</td></tr></table>"
    htmlBody = htmlBody & "<p style='color:#888;font-size:12px;margin-top:20px;'>Sent automatically from Excel on "
    htmlBody = htmlBody & Format(Now(), "DD-MMM-YYYY") & "</p></body></html>"

    Set outlookApp = CreateObject("Outlook.Application")
    Set mail = outlookApp.CreateItem(0)

    With mail
        .To       = recipient
        .Subject  = "Project Status: " & projectName & " - " & status
        .HTMLBody = htmlBody
        .Display
    End With

    MsgBox "Email prepared for: " & recipient, vbInformation
    Set mail = Nothing
    Set outlookApp = Nothing
End Sub

This VBA script reads the report values from whichever row your cursor is on, builds a styled HTML email using those values, and opens it in Outlook for review. It validates the email address before proceeding and returns an error if anything is missing.

Select a project
Click on the Run icon

The email opens addressed to the team member with the subject “Project Status: API Integration – Overdue”

Script 2: Bulk Email Trigger Based on Status Column Value

Let’s imagine your task sheet has multiple “Overdue” tasks, and you want to notify each assignee individually. Writing separate emails is time-consuming, but you can run a single macro to automatically notify all overdue tasks via email. Each person gets a personalized message. The script highlights sent rows in red so you can see at a glance what was actioned. VBA Code:

Sub BulkEmailByStatus()
    Const TRIGGER_VALUE As String  = "Overdue"   ' <-- Change trigger word
    Const STATUS_COL    As Integer = 4
    Const EMAIL_COL     As Integer = 1
    Const NAME_COL      As Integer = 3
    Const DUE_COL       As Integer = 5

    Dim ws As Worksheet
    Dim outlookApp As Object, mail As Object
    Dim lastRow As Long, i As Long, sentCount As Integer
    Dim recipient As String, itemName As String, dueDate As String

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, STATUS_COL).End(xlUp).Row
    Set outlookApp = CreateObject("Outlook.Application")
    sentCount = 0

    If MsgBox("Email all rows where Status = '" & TRIGGER_VALUE & "'. Continue?", _
              vbYesNo + vbQuestion, "Confirm Bulk Email") = vbNo Then Exit Sub

    For i = 2 To lastRow
        If Trim(ws.Cells(i, STATUS_COL).Value) = TRIGGER_VALUE Then

            recipient = Trim(ws.Cells(i, EMAIL_COL).Value)
            itemName  = ws.Cells(i, NAME_COL).Value
            dueDate   = Format(ws.Cells(i, DUE_COL).Value, "DD-MMM-YYYY")

            If recipient = "" Or InStr(recipient, "@") = 0 Then GoTo NextRow

            Set mail = outlookApp.CreateItem(0)
            With mail
                .To      = recipient
                .Subject = "Action Required: " & itemName & " is " & TRIGGER_VALUE
                .HTMLBody = "<html><body style='font-family:Arial;font-size:14px;'>" & _
                    "<p>Hello,</p>" & _
                    "<p>This is an automated reminder that <strong>" & itemName & "</strong> " & _
                    "is currently marked as <strong style='color:red;'>" & TRIGGER_VALUE & "</strong>." & _
                    "</p><p>Due Date: <strong>" & dueDate & "</strong></p>" & _
                    "<p>Please take action as soon as possible.</p>" & _
                    "<p style='color:#888;font-size:12px;'>Auto-sent from Excel " & _
                    Format(Now(), "DD-MMM-YYYY") & "</p></body></html>"
                .Send
            End With

            ws.Cells(i, STATUS_COL).Interior.Color = RGB(255, 200, 200)
            sentCount = sentCount + 1
        End If
NextRow:
    Next i

    Set mail = Nothing
    Set outlookApp = Nothing
    MsgBox sentCount & " email(s) sent for status: " & TRIGGER_VALUE, vbInformation
End Sub

This script loops through every data row, checks the status column for a trigger word you define at the top, and sends a tailored email to each matching row. A message box asks for confirmation before sending emails, skips rows with missing email addresses, and marks each notified row with a background color.

Go to the Developer tab >> select Macros
Select the Sub procedure and click Run

With TRIGGER_VALUE = “Overdue”, running this script emails only the overdue task details
A message box will appear to confirm the trigger
Click OK

The Overdue tasks are highlighted with red fill, and the emails are sent automatically to the team members

Warning: .Send sends emails immediately. It’s best to test by temporarily setting recipient = “[email protected]” inside the loop before going live.

Script 3: Create Outlook Meeting Requests from Excel Schedule Data

Suppose you run a weekly planning session and maintain a meeting schedule in Excel. You have multiple meetings to book this week, each for a different project team, and all the meeting details are already in your tracker. Instead of creating each meeting invite manually, you can use a script where you select one project at a time and run the macro. Each time, Outlook opens a fully populated meeting request with all attendees added, the correct time and duration set, and a 15-minute reminder configured. The meeting request will be ready to send in one click.

VBA Code:

Sub CreateMeetingFromRow()
    Dim ws As Worksheet
    Dim outlookApp As Object, apptItem As Object
    Dim selectedRow As Long
    Dim attendees As String, subject As String, location As String
    Dim meetDate As Date, meetTime As Date, duration As Integer
    Dim startDateTime As Date

    Set ws = ActiveSheet
    selectedRow = ActiveCell.Row

    If selectedRow <= 1 Then
        MsgBox "Please select a data row.", vbExclamation
        Exit Sub
    End If

    attendees = ws.Cells(selectedRow, 1).Value
    subject = ws.Cells(selectedRow, 3).Value
    meetDate = CDate(ws.Cells(selectedRow, 6).Value)
    meetTime = CDate(ws.Cells(selectedRow, 7).Value)
    duration = CInt(ws.Cells(selectedRow, 8).Value)
    location = ws.Cells(selectedRow, 9).Value

    startDateTime = CDate(Format(meetDate, "YYYY-MM-DD") & " " & Format(meetTime, "HH:nn"))

    Set outlookApp = CreateObject("Outlook.Application")
    Set apptItem = outlookApp.CreateItem(1)  ' 1 = olAppointmentItem

    With apptItem
        .MeetingStatus = 1   ' olMeeting: enables the attendees panel
        .Subject = subject
        .Location = location
        .Start = startDateTime
        .Duration = duration
        .Body = "Meeting auto-created from Excel schedule." & vbCrLf & _
                    "Row: " & selectedRow & " | Created: " & Format(Now(), "DD-MMM-YYYY HH:nn")
        .ReminderSet = True
        .ReminderMinutesBeforeStart = 15

        Dim attList() As String, att As Variant
        attList = Split(attendees, ";")
        For Each att In attList
            If Trim(att) <> "" Then
                Dim recip As Object
                Set recip = .Recipients.Add(Trim(att))
                recip.Type = 1  ' 1 = olTo (required attendee)
            End If
        Next att

        .Recipients.ResolveAll

        .Display
    End With

    Set apptItem = Nothing
    Set outlookApp = Nothing
End Sub

This script converts a single Excel task into a complete Outlook calendar appointment. It handles date and time parsing, splits multiple attendees from a semicolon-separated list, sets meeting metadata (including location and reminder), and opens the invite for final review.

Select the Sub Procedure and Run the macro
Outlook opens a 60-minute meeting invite for 1 March at 9 am

Tip: Use a distribution list email (e.g. [email protected]) in column A to invite an entire group with a single address.

Script 4: Send a Weekly Summary Digest Email

Suppose your manager expects a summary of all active projects at the start of each week. Instead of copy-pasting rows into an email and formatting them manually, you can use a VBA script that scans the entire sheet, pulls every row where Status = “In Progress,” builds a clean HTML table, and emails it to your manager as a single digest with the correct week label in the subject line, generated automatically.

VBA Code:

Sub SendWeeklySummaryDigest()
    Const DIGEST_RECIPIENT As String  = "[email protected]"  ' <-- Change
    Const FILTER_STATUS    As String  = "In Progress"           ' <-- Change
    Const STATUS_COL       As Integer = 4

    Dim ws As Worksheet
    Dim outlookApp As Object, mail As Object
    Dim lastRow As Long, i As Long, col As Integer
    Dim rowCount As Integer
    Dim htmlTable As String, htmlBody As String, weekLabel As String

    Set ws   = ActiveSheet
    lastRow  = ws.Cells(ws.Rows.Count, STATUS_COL).End(xlUp).Row
    rowCount = 0

    weekLabel = "Week of " & Format(Now() - Weekday(Now(), 2) + 1, "DD-MMM-YYYY")

    htmlTable = "<table style='width:100%;border-collapse:collapse;font-size:13px;'>"
    htmlTable = htmlTable & "<tr style='background:#1F4E79;color:white;'>"
    For col = 1 To 5
        htmlTable = htmlTable & "<th style='padding:8px;text-align:left;'>" & _
            ws.Cells(1, col).Value & "</th>"
    Next col
    htmlTable = htmlTable & "</tr>"

    For i = 2 To lastRow
        If Trim(ws.Cells(i, STATUS_COL).Value) = FILTER_STATUS Then

            Dim rowColor As String
            rowColor = IIf(rowCount Mod 2 = 0, "#FFFFFF", "#F4F6F8")
            htmlTable = htmlTable & "<tr style='background:" & rowColor & ";'>"

            For col = 1 To 5
                Dim cellVal As String
                cellVal = CStr(ws.Cells(i, col).Value)
                If IsDate(ws.Cells(i, col).Value) Then
                    cellVal = Format(CDate(ws.Cells(i, col).Value), "DD-MMM-YYYY")
                End If
                htmlTable = htmlTable & "<td style='padding:8px;border-bottom:1px solid #eee;'>" & _
                    cellVal & "</td>"
            Next col
            htmlTable = htmlTable & "</tr>"
            rowCount = rowCount + 1
        End If
    Next i
    htmlTable = htmlTable & "</table>"

    If rowCount = 0 Then
        MsgBox "No rows found with status: " & FILTER_STATUS, vbInformation
        Exit Sub
    End If

    htmlBody = "<html><body style='font-family:Arial,sans-serif;font-size:14px;max-width:800px;'>"
    htmlBody = htmlBody & "<div style='background:#1F4E79;padding:16px 24px;color:white;'>"
    htmlBody = htmlBody & "<h2 style='margin:0;'>Weekly Status Digest</h2>"
    htmlBody = htmlBody & "<p style='margin:4px 0 0;font-size:13px;opacity:0.8;'>" & weekLabel & "</p></div>"
    htmlBody = htmlBody & "<p>Items with status: <strong>" & FILTER_STATUS & "</strong> (" & rowCount & " items)</p>"
    htmlBody = htmlBody & htmlTable
    htmlBody = htmlBody & "<p style='color:#888;font-size:12px;margin-top:20px;'>Auto-generated from " & _
        ws.Name & " on " & Format(Now(), "DD-MMM-YYYY HH:nn") & "</p></body></html>"

    Set outlookApp = CreateObject("Outlook.Application")
    Set mail = outlookApp.CreateItem(0)
    With mail
        .To       = DIGEST_RECIPIENT
        .Subject  = "Weekly Digest: " & FILTER_STATUS & " Items - " & weekLabel
        .HTMLBody = htmlBody
        .Display
    End With

    Set mail = Nothing
    Set outlookApp = Nothing
    MsgBox "Digest prepared: " & rowCount & " rows included.", vbInformation
End Sub

Instead of one email per task, this script aggregates matching tasks into one consolidated report email. It builds the table header dynamically from row 1 of your sheet, adds alternating row colors for readability, formats any date cells automatically, and opens the final email for review.

Run the VBA code
With FILTER_STATUS = “In Progress”, the digest email will be prepared automatically for the manager

Script 5: Auto-Remind Assignees When Due Dates Are Approaching

Due dates are one of the most important elements of a project tracker. It’s easy to forget to check upcoming deadlines every morning and miss who needs a nudge. One VBA script can fix that permanently.

Every time you open the workbook, it scans your tasks to find items due within 3 days that aren’t complete yet. Then it prepares color-coded reminder emails to the assignee—red for today, orange for tomorrow, and blue for 2–3 days out. It also logs the send date per row so nobody gets spammed twice in the same day.

VBA Code:

Sub SendDueDateReminders()
    Const DAYS_WARNING  As Integer = 3
    Const EMAIL_COL     As Integer = 1
    Const NAME_COL      As Integer = 2
    Const PROJECT_COL   As Integer = 3
    Const STATUS_COL    As Integer = 4
    Const DUE_COL       As Integer = 5
    Const NOTIFIED_COL  As Integer = 11

    Dim ws As Worksheet
    Dim outlookApp As Object, mail As Object
    Dim lastRow As Long, i As Long, sentCount As Integer
    Dim today As Date, dueDate As Date, daysLeft As Integer
    Dim todayStr As String, lastNotified As String
    Dim recipient As String, projectName As String

    today    = Date
    todayStr = Format(today, "YYYY-MM-DD")
    Set ws   = ActiveSheet
    lastRow  = ws.Cells(ws.Rows.Count, DUE_COL).End(xlUp).Row
    Set outlookApp = CreateObject("Outlook.Application")
    sentCount = 0

    For i = 2 To lastRow

        If ws.Cells(i, DUE_COL).Value = "" Then GoTo NextRow

        If LCase(Trim(ws.Cells(i, STATUS_COL).Value)) = "complete" Then GoTo NextRow
        If LCase(Trim(ws.Cells(i, STATUS_COL).Value)) = "done"     Then GoTo NextRow

        dueDate  = CDate(ws.Cells(i, DUE_COL).Value)
        daysLeft = DateDiff("d", today, dueDate)

        If daysLeft < 0 Or daysLeft > DAYS_WARNING Then GoTo NextRow

        lastNotified = Trim(CStr(ws.Cells(i, NOTIFIED_COL).Value))
        If lastNotified = todayStr Then GoTo NextRow

        recipient   = Trim(ws.Cells(i, EMAIL_COL).Value)
        projectName = ws.Cells(i, PROJECT_COL).Value

        If recipient = "" Or InStr(recipient, "@") = 0 Then GoTo NextRow

        Dim urgencyColor As String, urgencyLabel As String
        Select Case daysLeft
            Case 0:    urgencyColor = "#C0392B" : urgencyLabel = "TODAY"
            Case 1:    urgencyColor = "#E67E22" : urgencyLabel = "TOMORROW"
            Case Else: urgencyColor = "#2980B9" : urgencyLabel = "In " & daysLeft & " Days"
        End Select

        Set mail = outlookApp.CreateItem(0)
        With mail
            .To      = recipient
            .Subject = "Reminder: '" & projectName & "' is due " & urgencyLabel
            .HTMLBody = "<html><body style='font-family:Arial;font-size:14px;'>" & _
                "<div style='background:" & urgencyColor & ";padding:14px 20px;color:white;'>" & _
                "<h3 style='margin:0;'>Task Due " & urgencyLabel & "</h3></div>" & _
                "<p>This is an automated reminder for the following task:</p>" & _
                "<table style='border-collapse:collapse;width:100%;'>" & _
                "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;width:120px;'>Project</td>" & _
                "<td style='padding:8px;border-bottom:1px solid #eee;'>" & projectName & "</td></tr>" & _
                "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;'>Assignee</td>" & _
                "<td style='padding:8px;border-bottom:1px solid #eee;'>" & ws.Cells(i, NAME_COL).Value & "</td></tr>" & _
                "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;'>Due Date</td>" & _
                "<td style='padding:8px;color:" & urgencyColor & ";font-weight:bold;'>" & _
                Format(dueDate, "DD-MMM-YYYY") & "</td></tr>" & _
                "<tr><td style='padding:8px;background:#DEEAF1;font-weight:bold;'>Status</td>" & _
                "<td style='padding:8px;'>" & ws.Cells(i, STATUS_COL).Value & "</td></tr>" & _
                "</table><p>Please update your task status once complete.</p>" & _
                "<p style='color:#888;font-size:12px;'>Auto-reminder | " & todayStr & "</p>" & _
                "</body></html>"
            .Display
        End With

        ws.Cells(i, NOTIFIED_COL).Value = todayStr
        sentCount = sentCount + 1
NextRow:
    Next i

    Set mail = Nothing
    Set outlookApp = Nothing

    If sentCount > 0 Then
        MsgBox sentCount & " reminder(s) prepared.", vbInformation
    Else
        MsgBox "No reminders prepared. Check due dates are within " & DAYS_WARNING & " days from today.", vbInformation
    End If
End Sub

To auto-run every morning when the workbook opens, paste this into the ThisWorkbook module:

Private Sub Workbook_Open()
    Call SendDueDateReminders
End Sub

This script combines due date arithmetic, status filtering, duplicate-send prevention, and customized color coding based on how soon each due date is. It automates Outlook email preparation in a single loop and can run automatically using the Workbook_Open event.

Now, every time you open the workbook, it will prepare reminder emails for tasks due within 3 days using a color-coded message

It will also insert the email send date in the Last Notified column

Best Practices & Troubleshooting

Always Test with .Display First: Every script uses either .Display or .Send. During development, always use .Display. This opens each email as a draft so you can inspect the layout, recipients, and subject line before anything is sent. Only switch to .Send once everything looks correct.

Dates Must Be Real Excel Dates, Not Text: Use proper Excel date formats if you want to compare dates, send emails based on dates, or create meeting events reliably.

Attaching the Workbook to Any Email: Add these two lines inside any With mail block, just before .Display or .Send, to attach the current Excel file to the email:

If ThisWorkbook.Path <> "" Then
    .Attachments.Add ThisWorkbook.FullName
End If

Error Handling for Production Use: Add an error handler to any script you plan to run regularly. It catches failures gracefully instead of leaving Outlook objects open in memory:

Sub YourMacroName()
    On Error GoTo ErrorHandler

    ' ... your code here ...

    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Macro Error"
    If Not mail Is Nothing Then Set mail = Nothing
    If Not outlookApp Is Nothing Then Set outlookApp = Nothing
End Sub

Outlook Security Prompt: If Outlook shows a dialog saying, ‘A program is trying to access email addresses stored in Outlook’, this is expected. Click Allow. In corporate environments, your IT administrator can whitelist the Excel file permanently so the prompt never appears.

Conclusion

This tutorial provided 5 VBA scripts to automate custom Outlook reports from Excel. These ready-to-use scripts can turn your Excel tracker into an active communication tool. Using your Excel data, you can send bulk emails, notify overdue tasks, book meetings, set reminders, and deliver weekly digests. You can use the code in your own reports or projects—just adjust the column references and cell mappings to match your worksheet.

Get FREE Advanced Excel Exercises with Solutions!