Sending Emails with Attachments via Excel VBA: Troubleshooting the "Undeliverable" Error

As developers, we often bridge the gap between application logic and external services. Automating tasks within Microsoft Office applications, such as sending emails from Excel using VBA and Outlook, is a common requirement for reporting and workflow management. However, as you've experienced, these integrations are rarely seamless. When you encounter an "Undeliverable" error suggesting the recipient cannot be reached, it signals that the issue often lies not in the VBA syntax itself, but in the underlying communication layer—specifically, the SMTP settings or network security protocols.

This post will dissect your specific scenario, provide a robust solution for sending emails with attachments via VBA, and guide you through troubleshooting those frustrating delivery errors.

The Foundation: Sending Mail via Outlook Automation

The approach you are using—leveraging the CreateObject("Outlook.Application") to interact with the Outlook object model—is the standard and correct way to automate email tasks from Excel VBA. This method effectively bridges the gap between your spreadsheet data and the email client.

Let's review your provided code structure:

Sub CreateEmail()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As Variant
    Dim CcRecipient As Variant

    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olMailItem) ' Assuming olMailItem is defined

    ' --- Recipient Setup ---
    For Each ToRecipient In Array("jon.doe@aol.com")
        OlMail.Recipients.Add ToRecipient
    Next ToRecipient

    For Each CcRecipient In Array("jon.doe@aol.com")
        With OlMail.Recipients.Add(CcRecipient)
            .Type = olCC
        End With
    Next CcRecipient

    ' --- Message Setup ---
    OlMail.Subject = "Open Payable Receivable"

    ' --- Attachment Setup ---
    On Error Resume Next ' Added for safer attachment handling
    OlMail.Attachments.Add "C:\OpenPayRecPrint2.pdf"
    If Err.Number <> 0 Then
        MsgBox "Error attaching file: " & Err.Description, vbCritical
        Exit Sub
    End If
    On Error GoTo 0

    ' --- Send Message ---
    OlMail.Send

    Set OlMail = Nothing
    Set OlApp = Nothing
End Sub

While the structure is sound for creating and attempting to send an email, the failure you observed points toward external factors rather than internal VBA errors.

Troubleshooting the "Undeliverable" Error

The error message "recipient cannot be reached" almost always relates to the connection established between Outlook (or the system running the script) and the outgoing mail server (SMTP). Here are the critical areas you must investigate:

1. SMTP Server Configuration

Outlook relies on your system's default or configured SMTP settings to relay mail. If these settings are incorrect, outdated, or blocked by network firewalls, delivery will fail. You need to ensure that the credentials and server addresses used by Outlook are valid for sending external mail.

2. File Path Validation

Although less likely if you are testing locally, always confirm the file path: C:\OpenPayRecPrint2.pdf. Ensure this path is perfectly accurate and that the file exists at that exact location when the macro runs. A failed attachment attempt can sometimes manifest as a general delivery error.

3. Account Permissions and Reputation

The most common reason for "Undeliverable" errors with personal accounts (like AOL) involves mailbox permissions or the email provider flagging the connection as suspicious. If you are sending to corporate addresses, ensure your Outlook profile is correctly authenticated with the organization's mail server settings.

4. Implementing Robust Error Handling

A senior developer never relies on a single execution path. We must anticipate failure. Incorporating explicit error handling allows the script to report why it failed instead of silently stopping.

Here is an improved structure incorporating error trapping:

Sub SendEmailWithAttachment_Robust()
    On Error GoTo ErrorHandler

    Dim OlApp As Object
    Dim OlMail As Object

    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.CreateItem(olMailItem)

    ' Setup Recipients... (Assume this part remains similar)

    OlMail.Subject = "Open Payable Receivable"

    ' Attempt Attachment
    On Error Resume Next ' Temporarily ignore attachment errors for specific reporting
    OlMail.Attachments.Add "C:\OpenPayRecPrint2.pdf"
    If Err.Number <> 0 Then
        MsgBox "Attachment failed: " & Err.Description, vbCritical
        GoTo CleanUp
    End If
    On Error GoTo ErrorHandler ' Restore normal error handling

    ' Send Message
    OlMail.Send

CleanUp:
    Set OlMail = Nothing
    Set OlApp = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "An unexpected error occurred during email sending: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

Conclusion: System Integration Requires Vigilance

Automating tasks through VBA is a powerful skill, allowing you to create custom solutions for business processes. However, when integrating with external services like email delivery, the responsibility shifts from simple coding to system integration and network reliability. Just as in building robust applications using frameworks like Laravel where reliable API communication is paramount, automating Office functions requires vigilance over configuration, permissions, and error handling. By meticulously checking your SMTP settings and implementing strong error trapping, you move from simply attempting a task to reliably executing it.