Outlook mail intermittently not sent using Excel VBA

Question

I have an intermittent Excel VBA problem when sending via Outlook.

20 or 30 times a day my clients send email files. About once a week the email doesn't send. It's not in the Outbox or Sent. We only find out when we receive a physical sample without the corresponding email, usually the next day.

The relevant Excel VBA code:

'[bunch of code that sets everything up in order to send]    

Err.Clear
On Error Resume Next
Set OutMail = oOutlook.CreateItem(0) ' [oOutlook is a previously created object which is an instance of Outlook]
With OutMail
    .To = Address ' [Address=email I have pre-filled]
    .Subject = "Swabs File"
    .body = vbAns ' [vbAns = variant I have pre-filled]
    .Attachments.Add (Dir) ' [Dir = string which is the name of the file]
    .send
End With

'Error trap if there is any problem at all
If Err Then
    MsgBox ("Problem with Outlook - Failed to Send. Please try again")
    Exit Sub
else
    MsgBox ("Your file has been sent")
End If

When the problem occurs the error isn't trapped, and the client thinks they have successfully sent the file.

How can I trap the error?

niton niton · Accepted Answer · 2020-09-23T15:16:59

Option Explicit

Private Sub test()

    Dim oOutlook As Object
    Dim OutMail As Object
    
    ' *** with Outlook ***
    ' As many times as you want there will only be one
    'Set oOutlook = New outlook.Application
    
    'On Error Resume Next   ' generate mysterious failure,
    '                          hide error so developer cannot debug
    
    On Error GoTo trap      ' Error trap if there is any problem at all
    
    If Not oOutlook Is Nothing Then ' confirm Outlook is available
    
        Set OutMail = oOutlook.CreateItem(0)
        
        With OutMail
        
            '.To = Address ' [Address=email I have pre-filled]
            .Subject = "Swabs File"
            '.body = vbAns ' [vbAns = variant I have pre-filled]
            
            Error 5   ' for demonstration
            .Attachments.Add (dir) ' [Dir = string which is the name of the file]
            
            .Display    '.Send
            
        End With
        
        MsgBox "Your file has been sent"
        
    Else
    
        MsgBox "oOutlook object does not exist - Failed to Send."
        
    End If
    
    Exit Sub    ' <---  bypass the trap if no error
    

trap:   'Error trap if there is any problem at all
    
    MsgBox "Problem with Outlook - Failed to Send. Please try again"

End Sub

Only use On Error Resume Next when "You know why the error occurs."
You can either take steps to address the error or ignore it on purpose.

There is a general method of using On Error Resume Next.

Outlook mail intermittently not sent using Excel VBA

1 Answers