Unexpected error in Access VBA email handler loop

Trying to diagnose an "Array index out of bounds." error in the following code where the subject line is defined.  The idea is to save the attachments to different locations, move the emails and do different stuff according to the email subject line.  The Array error kind of throws me; I know what out of bounds is, but was not aware that an array was created.  What am I missing?

Public Sub RunChecksRequests()

    Dim myolApp As Outlook.Application
    Dim myItems As Object
    Dim oMail As Outlook.MailItem
    Dim myNamespace As Outlook.NameSpace
    
    Set myolApp = CreateObject("Outlook.Application")
    Set myNamespace = myolApp.GetNamespace("MAPI")
    Set myItems = myNamespace.Folders("XE_IPP").Folders("Inbox").Items

    On Error GoTo notfoundFolder
    
    With myolApp
        For Each oMail In myItems
            If TypeName(oMail) = "MailItem" Then
                If oMail.Subject = "IPP Share Request" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                       oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Requests\" & oMail.Attachments.Item(1).FileName
                       Set rqFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Requests")
                       oMail.Move rqFolder
                       GoTo RequestsTurnaround
                End If
                If oMail.Subject = "IPP Share Check" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                       oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Checks\" & oMail.Attachments.Item(1).FileName
                       Set chkFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Checks")
                       oMail.Move chkFolder
                       GoTo ChecksTurnaround
                End If
            End If
        Next
            
        On Error GoTo 0
    
        Exit Sub
    End With
notfoundFolder:
    MsgBox "Unable to process."
            
RequestsTurnaround:
    Call RequestsTurnaround
Exit Sub
    
ChecksTurnaround:
    Call ChecksTurnaround
Exit Sub

End Sub

Open in new window

Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Initial guess is that when your code gets to an email that does not have a attachment, the error is occurring. VBA does not have what is called a short-circuited IF statement, which means it will check all parts of the IF statement, no matter if any part does not match your desired condition. To handle this, you may want to break the IF statement into nested IFs:
If Subject = ... Then
  If Attachments.Count > 0 Then
     If Attachments.Item(1).FileName = ...
Nick67Commented:
This creates a collection
Set myItems = myNamespace.Folders("XE_IPP").Folders("Inbox").Items
and a collection is an array of items.

This assumes an items exists
If oMail.Subject = "IPP Share Request" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
When it doesn't, you get and array-out-of-bounds error.

You have many untested and unhandled logic branches here.
What if there's no subject--will null blow things up?
What if there are no attachments?
What if the second or third attachment is an xls* and not the first?
What if you get an xls?
What if the folder doesn't exist?
What if the file already exists?
What if the G:\ drive and path aren't available?

You need to revamp your code to catch what reality can throw at it!
Jay WilliamsOwnerAuthor Commented:
Ouch.  I didn't write it, but I have to fix it--but in all fairness, I'm pretty sure that I wouldn't have done much better.  At least I know I'm not the only amateur in the world.  As you can see, I need a lot of guidance on this.  When I go in tomorrow I'll start picking at it.  Thanks, guys.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jay WilliamsOwnerAuthor Commented:
Just thinking: Maybe we need a dynamic array that can handle any number of items?  Maybe a Select Case strategy might be a better than deeply nested If statements?
Nick67Commented:
Maybe we need a dynamic array that can handle any number of items?
A collection handles all that and more when it is instantiated.
When you set it, the number of items is enumerated and references to each thing in the collection are set.
You can get a .Count and then go through each one with a for x = 1 to .Count

Generally, you'd do stuff like
Dim objOutlookAttach As Outlook.Attachment
For Each objOutlookAttach in oMail.Attachments
     'do whatever you are going to do with the attachment
Next objOutlookAttach

Maybe a Select Case strategy might be a better than deeply nested If statements?
Always!

Select Case True is incredibly powerful

Select Case True
    Case Nz(oMail.Subject = "") 'no subject
         'bail
    Case oMail.Attachments.Count = 0 'no attachments
        'bail
    Case oMail.Subject = "IPP Share Request"
        'Block of code to test attachment
    Case oMail.Subject = "IPP Share Check"
         'Block of code to test attachments
    Case Else
        'default actions to be taken, if any
End Select

You put no-go conditions at the top, then the most specific conditions, and then more general ones.
The FIRST true condition encountered gets executed, and nothing else.
Extremely powerful once you know how to use it!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Bing (the sound of lights coming on)!  Now we'll see if I can do it.  Hope you don't go on vacation! :-)
Jay WilliamsOwnerAuthor Commented:
Okay. I'm a Select Case virgin.  Please be gentle.

When I try to compile this code I get an "End With without With" error.
Public Sub RunChecksRequests()

    Dim myolApp As Outlook.Application
    Dim myItems As Object
    Dim oMail As Outlook.MailItem
    Dim myNamespace As Outlook.NameSpace
    
    Set myolApp = CreateObject("Outlook.Application")
    Set myNamespace = myolApp.GetNamespace("MAPI")
    Set myItems = myNamespace.Folders("XE_IPP").Folders("Inbox").Items

    On Error GoTo 0
    
    With myolApp
        For Each oMail In myItems
            Select Case True
            Case oMail.Attachments.Count = 0 'no attachments
                    Set plnFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Plain")
                    oMail.Move rqFolder
            End Select
            Select Case True
            Case Nz(oMail.Subject = "") 'no subject
                    Set plnFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Plain")
                    oMail.Move plnFolder
            End Select
            Select Case True
            Case oMail.Subject = "IPP Share Request"
            'Block of code to test attachment
                LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm"
                    oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Requests\" & oMail.Attachments.Item(1).FileName
                    Set rqFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Requests")
                    oMail.Move rqFolder
                Call RequestsTurnaround
            Case oMail.Subject = "IPP Share Check"
            'Block of code to test attachments
                LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm"
                    oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Checks\" & oMail.Attachments.Item(1).FileName
                    Set chkFolder = myNamespace.Folders("XE_IPP").Folders("Inbox").Folders("Checks")
                    oMail.Move chkFolder
                Call ChecksTurnaround
            End Select
    End With
End Sub

Open in new window

I realize there is probably other stuff wrong with this code, too (and I'm hoping you're kind enough to point it out), but for now, I clearly have some basic understanding missing.

How and when do you "'bail"? Pretty sure I'm not using End Select properly.
Shaun KlineLead Software EngineerCommented:
You are missing the Next for your For loop. Insert between lines 41 and 42.
Jay WilliamsOwnerAuthor Commented:
Thanks, Shaun.  Does the other stuff make sense to you?
Shaun KlineLead Software EngineerCommented:
You do not need to repeat the End Case, Select Case True (Lines 21 & 25).

The logic looks sound. The only other thing you may want to check for is what type of object item is (before all other checks). An Inbox can receive items other than Email object types. I'd have to search for the code to handle the type check, so you may find it as fast as I would.
Jay WilliamsOwnerAuthor Commented:
Good! Just the kind of help I need.  Thanks again.
Dale FyeOwner, Developing Solutions LLCCommented:
@Nick,

"SELECT CASE True"

is very interesting.  In 20 years, I don't think I have every seen that used before, and had never even considered putting a evaluated condition in the case statement.  How is this construct different than an IF/ElseIf construct.

the thing that caught my eye was:

Case Nz(oMail.Subject = "") 'no subject

I'm not sure I understand what you are suggesting with:

NZ(oMail.Subject = "")

Granted, I have not used Outlook automation much, but shouldn't that be:

NZ(oMail.Subject, "") = ""

Dale
Nick67Commented:
@Dale
Bad aircode on my part, good catch.
Select Case True operates very similarly to if elseif -- But nesting ifs can get very confusing in a hurry.  Select Case True lets you build more readable code when you need IF in the blocks of execution
Jay WilliamsOwnerAuthor Commented:
This really was collaborative, and you all got me within striking distance.  I have some other related issues, but they will go on another thread if they prove too stubborn.  Thanks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.