Intermittent error Excel - Outlook VBA

[Georgiboy]

Hi all,

I have been passed the below code that has an error finding a folder within a shared inbox, the error is not for all users however, everyone is using Excel 2016 and sometimes users get the error, other times they don't. I can't display the error at this stage as i don't get the error often. It is basically an 'Object not found' error from what i understand.

Could anyone more familiar with writing code that talks to outlook cast their eye over the code to see if there is anything obvious missing or needs changing?

I have tried early binding and late binding methods but to no avail, any help would be apreciated. excuse the code being messy, it has been pushed and pulled to try and find the error, it works as is but will produce an error more for some and less for others:

Dim FilePath As String
Sub attachmentsave()
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    Dim wb As Workbook, tmpDate As String, cnt As Long
    Dim omailitem As Object
    Dim inFol As Object, destFol As Object, errorFol As Object
    Dim atmt As Object
    Dim tmpSTR1 As String, start1 As Long, end1 As Long, tmpSTR2 As String
    Dim olMailbox As Object
    Dim olInbox As Object
    Dim subFolder As Object
    Dim olNS As Object

    Set olNS = olApp.GetNamespace("MAPI")
    Set olMailbox = olNS.Folders("Distribution Returns")
    Set olInbox = olMailbox.Folders("Inbox")
    Set inFol = olInbox.Folders("Return Notification") ' I get the error here stating that it can't find the folder but it does exist within the shared inbox!
    Set destFol = olInbox.Folders("Return Completed")
    Set errorFol = olInbox.Folders("Return Error")

    Set wb = ThisWorkbook
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    FilePath = wb.Path & "\Return Downloads\"

    If inFol.Items.Count < 1 Then
        MsgBox "There are no mails to look at in the 'Return Notification' Outlook folder", vbExclamation, "Error"
        Exit Sub
    End If

    On Error Resume Next
        Kill FilePath & "*.*" ' clear old files
    On Error GoTo 0
    
    Set omailitem = olApp.CreateItem(olMailItem)
    For Each omailitem In inFol.Items
        For Each atmt In omailitem.Attachments
            If LCase(Left(atmt.DisplayName, 16)) = "returns template" Then
                cnt = cnt + 1
                atmt.SaveAsFile FilePath & Format(Now(), "ddmmyyyyhhmmss") & "-" & cnt & ".xlsm"
                DoEvents
                DoEvents
            End If
        Next
        omailitem.UnRead = False
        ' no valid attachments so ove mail to error
        If cnt = 0 Then
            omailitem.Move errorFol
            MsgBox "Mail moved to error folder in Outlook, click 'OK' to continue" & vbNewLine & vbNewLine & "They have been moved due to an issue with the attachment"
        End If
        'cnt = 0
    Next

    Do Until inFol.Items.Count = 0
        inFol.Items(1).Move destFol
    Loop
    
    Application.ScreenUpdating = True
    
    Call test1
End Sub

Thanks in advance

George

 

[6StringJazzer]

This isn't causing the error but in this section

    Set omailitem = olApp.CreateItem(olMailItem)
    For Each omailitem In inFol.Items

the first line of code is not needed. It sets omailitem to a new instance, but the instance is immediately discarded when the loop uses omailitem to iterate all items in the folder. It won't cause an error but should be removed.

I don't see any obvious problem that would cause Object Not Found. It would be very helpful to know which line of code is highlighted when the error occurs.

This

 

[Georgiboy]

Thanks @6StringJazzer ,

I will remove that line.

The erroneous line is the one below:

Set inFol = olInbox.Folders("Return Notification")

For some reason it will find the folder sometime but not others, even the same person on the same machine the error is intermittent. The setup for the mailbox is the same for all users, the folder above is within the inbox of the shared mailbox.

 

[6StringJazzer]

I'm sorry, I totally missed that you had a comment in the code pointing to this line.

I am stumped as to why this error would be intermittent, and the same user would sometimes get it and sometimes not. I may try to test it myself if I can figure out how to set up Outlook so this runs.

 

[Georgiboy]

Not a problem at all, I apreciate you taking the time to look.

Maybe a usefull bit of information is that this is not running on the actaul machine, it is running on devices that look like modems, these devices connect to a VM or virtual desktop. Even on my laptop I have to log into this system via a web link. I have started to wonder if there is a delay in the VBA getting access to Outlook after creating the object?

I just don't know at this stage, but i begin day three of trying to sort this error.

Cheers

George

 

[Georgiboy]

The actual error is:

Run-time error '-2147221233 (8004010f)':
The attempted operation failed. An object could not be found.

 

[kevin9999]

This is a long shot, but what happens if you Dim inFol as Variant instead of Object?

 

[Georgiboy]

Still get the intermittent error unfortunately

 

[Georgiboy]

Currently i have been testing a loop through the folder names inside the inbox and setting them via their name, if the folder was not found then it loops back to the top. I know this is not ideal but i just want to see if it resolves the issue:

jump1:
    Sleep (500)
    Set olNS = Outlook.GetNamespace("MAPI")
    Set olMailbox = olNS.Folders("Distribution Returns")
    Set olInbox = olMailbox.Folders("Inbox")
    For Each ufolder In olInbox.Folders
        If ufolder.Name = "Return Notification" Then
            Set inFol = olInbox.Folders("Return Notification")
        End If
        If ufolder.Name = "Return Completed" Then
            Set destFol = olInbox.Folders("Return Completed")
        End If
        If ufolder.Name = "Return Error" Then
            Set errorFol = olInbox.Folders("Return Error")
        End If
        If inFol Is Nothing Then
            GoTo jump1
        End If
    Next ufolder

 

[kevin9999]

I'm afraid I can't add anything useful, but this might provide some clues?
Shared Inbox Subfolder not visible within VBA