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]

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

That is quite a different error than the one I thought you were getting. The one I thought you were getting indicates a reference to an undefined Object variable. The one you are actually getting is more exotic. I have not been able to find a simple answer that applies to your situation. Like I said, if I get time I'll try it on my machine.

 

[Georgiboy]

Thanks Jeff, it will be appreciated.

 

[6StringJazzer]

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?

This could be important. I am having trouble visualizing what you have here. If you are connecting to a VM, are you using both Excel and Outlook installed on the VM? Or are you trying some scenario where you are connecting something local to something on the VM?

If everything is on the VM, then whatever device you are on is theoretically beside the point, it just acts as an input interface. (I used to use Citrix and other types of virtual Windows connections, but never did anything this complicated on them.)

 

[Georgiboy]

are you using both Excel and Outlook installed on the VM?

Yes both are on the VM and we are using Citrix to connect.

I have also read this morning that the issue could be made worse by the setting 'Download shared folders' but I am not very knowledgeable with Outlook settings and how they change automation from Excel with VBA.

 

[Georgiboy]

Just a follow up on this, I ended up using a workaround.

I removed the folders from the inbox, this solved the issue. Never really got to the bottom of it but referencing the folders outside of the inbox works reliably, so we have gone with that option. I decided to go with this solution as there was never a problem attaching to the inbox folder itself but only the folders inside the inbox had the issue.

Thank you to all that looked at this