How to bypass Outlook item that generates an error when replying to mail using Excel VBA?

Question

I have working code that replies to an email in the user's Outlook, based on the subject. If the most recent item is a meeting invite, my code will not retrieve the email I want. Instead it will not pass the meeting invite and will display an error.

Code is as follows.

Sub Display()
Dim Fldr As Outlook.Folder
Dim olfolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim olReply As Outlook.MailItem
Dim olItems As Outlook.Items
Dim i As Integer
Dim signature As String


Set Fldr = Session.GetDefaultFolder(olFolderInbox)
Set olItems = Fldr.Items
olItems.Sort "[Received]", True
For i = 1 To olItems.Count
    signature = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(signature, vbDirectory) <> vbNullString Then
        signature = signature & Dir$(signature & "*.htm")
    Else:
        signature = ""
    End If
    signature = CreateObject("Scripting.FileSystemObject").GetFile(signature).OpenAsTextStream(1, -2).ReadAll

    Set olMail = olItems(i)
    If InStr(olMail.Subject, Worksheets("Checklist Form").Range("B8")) <> 0 Then
        If Not olMail.Categories = "Executed" Then
            Set olReply = olMail.ReplyAll
            With olReply
             .HTMLBody = "<p style='font-family:calibri;font-size:14.5'>" & "Hi Everyone," & "<p style='font-family:calibri;font-size:14.5'>" & "Workflow ID:" & " " & Worksheets("Checklist Form").Range("B6") & "<p style='font-family:calibri;font-size:14.5'>" & Worksheets("Checklist Form").Range("B11") & "<p style='font-family:calibri;font-size:14.5'>" & "Regards," & "</p><br>" & signature & .HTMLBody

             .Display
             .Subject = "RO Finalized WF:" & Worksheets("Checklist Form").Range("B6") & " " & Worksheets("Checklist Form").Range("B2") & " -" & Worksheets("Fulfillment Checklist").Range("B3")
            End With

            Exit For
        olMail.Categories = "Executed"
        End If
    End If
Next i

End Sub

Is it possible to bypass the most recent item if the code will not pass the first email. Example: Meeting Invite

I presume the error is a *type mismatch* on `Set olMail = olItems(i)`? — Mathieu Guindon, Aug 07 '18 at 21:04
I suspect the same-but-different signature issue might have something to do with how your/their account is configured for replies' signatures. — Mathieu Guindon, Aug 07 '18 at 21:20
@MathieuGuindon Could you please help me with this question. Would really appreciate it. https://stackoverflow.com/questions/51770455/vba-to-loop-through-all-inboxes-including-shared-inboxes — Tmacjoshua, Aug 10 '18 at 12:35

score 2 · Accepted Answer · answered Aug 07 '18 at 21:19

Dim olMail As Outlook.MailItem
...
Set olMail = olItems(i)

That Set assignment will not only fail if the first item is a meeting invite, it will fail for any olItems(i) (i.e. any value of i) that is not an Outlook.MailItem instance. That includes anything that can possibly land into an Outlook inbox, including a meeting invite.

One way to go would be to handle the runtime error that's thrown in the specific case where olItems(i) isn't a MailItem:

    For i = 1 To olItems.Count
        On Error GoTo ErrHandler ' jumps to error-handling subroutine if there's an error
        Set olMail = olItems(i)
        On Error GoTo 0 ' let any other error blow everything up
        ...
SkipToNext:
    Next i
    Exit Sub
ErrHandler:
    Debug.Print "Item index " & i & " is not a MailItem; skipping."
    Resume SkipToNext

Notice I'm putting the assignment/validation as early as possible in the loop - that way you don't run useless instructions if you're not looking at a MailItem.

Another - better - way to go about it, would be to validate the type of olItems(i):

Dim olItem As Object
'...

    For i = 1 To olItems.Count
        Set olItem = olItems(i)
        If Not TypeOf olItem Is Outlook.MailItem Then Goto SkipToNext
        Set olMail = olItem ' essentially a type cast from Object to MailItem
        ...
    SkipToNext:
    Next

Alternatively, you can drop that GoTo jump and increase the nesting level instead:

    For i = 1 To olItems.Count
        Set olItem = olItems(i)
        If TypeOf olItem Is Outlook.MailItem Then
            Set olMail = olItem ' essentially a type cast from Object to MailItem
            ...
        End If
    Next

Note the indentation; feel free to use an indenter if you're not sure how to do this correctly & consistently. Proper indentation is critical for code readability, especially given nested looping & conditional structures (disclaimer: I own that website and the OSS project it's for).

Thank you Mathieu, I used the second solution and it is working perfectly! The time and detail you put into this answer is much appreciated. About the signature, all users use the same programs and roughly the same signatures, I will look deeper into this. — Tmacjoshua, Aug 08 '18 at 13:20
I can make a new question if you would prefer, but I will place it here in case of a simple fix. As of now my email will only reply to the users specific inbox. This is fine for the most part, except a few users also need the code to grab from other general inboxes as well. How would I go about changing my code to allow the reply to either catch from any inbox or use worksheets("sheet1").Range("A1") with the correct inbox name. Thanks — Tmacjoshua, Aug 08 '18 at 13:25
I have found a bug in the second solution. Code line For I = 1 to olItems.Count displays error 6 "Overflow" for one user that has over 60,000 emails in her inbox. Everyone else that is using this receive much less emails. Is there something I need to add to this code to surpass this error? Thanks in advance it is much appreciated! — Tmacjoshua, Aug 08 '18 at 21:09
@Tmacjoshua your bug. `Integer` is a 16-bit signed integer type with a maximum value of `2^15-1`, i.e. 32,768 overflows it. Other than when using Win32 APIs, there's no reason not to declare integer types as `Long`, i.e. 32-bit signed integers, with a maximum value of `2^31-1`, i.e. somewhere well over 2 billion. Declare `i As Long`, problem solved! — Mathieu Guindon, Aug 08 '18 at 21:11
I will test this thanks Mathieu. I will ask another question regarding searching through numerous inboxes. — Tmacjoshua, Aug 08 '18 at 21:17

How to bypass Outlook item that generates an error when replying to mail using Excel VBA?

1 Answers1