VBA Excel Macro - The attempted operation failed. An object could not be found


Can anyone help me with this concern. I have created a macro that will read my Outlook emails and extract the values and headers (subject time sent etc.) them going to one of the sheet of my excel.

This tool was already working before. However, I think there something happen on my outlook somewhere and the code was not working anymore and prompting an error "The attempted operation failed. An object could not be found".

The location of the folder to read by the code was in "Inbox/TIBCO Reports Folder" only. The error is prompting at line

Set olFolder = olFolder.Folders(1).Folders("Inbox").Folders("TIBCO Reports Folder")

Here is the part of the code:

Dim olApp As Object
Dim olFolder As Object
Dim olMailItem As Object

Dim strTo As String
Dim strFrom As String
Dim dateSent As Variant
Dim dateReceived As Variant
Dim strSubject As String
Dim strBody As String

Dim date1 As Date
Dim date2 As Date

Dim loopControl As Variant
Dim mailCount As Long
Dim totalItems As Long

 '//Turn off screen updating
Application.ScreenUpdating = False

 '//Clearing worksheet content

 '//Setup headers for information
Range("A1:F1").Value = Array("Subject", "From", "Date/Time Sent", "Date/Time Received", "To", "Attachment")

 '//Format columns E and F to
Columns("C:D").EntireColumn.NumberFormat = "MM/DD/YYYY HH:MM:SS"

 '//Create instance of Outlook
Set olApp = CreateObject("Outlook.Application")

 '//Select folder to extract mail from
Set olFolder = olApp.GetNamespace("MAPI")
Set olFolder = olFolder.Folders(1).Folders("Inbox").Folders("TIBCO Reports Folder")

Thanks in advance, jsioson

Show source
| excel-vba   | email   | vba   | excel   | outlook   2017-01-05 01:01 2 Answers

Answers ( 2 )

  1. 2017-01-05 02:01

    Not an answer, but a debugging strategy which is too long to post as a comment.

    A line like

    Set olFolder = olFolder.Folders(1).Folders("Inbox").Folders("TIBCO Reports Folder")

    is very hard to debug. Each dot (.) tries to access a property. One of those attempts is failing. You don't know which one since there are three.

    What you could do is replace that one line by the lines

    Set oFolder = olFolder.Folders(1)
    Set oFolder = olFolder.Folders("Inbox")
    Set oFolder = olFolder.Folders("TIBCO Reports Folder")

    One of these three lines will fail. Seeing which one will allow you to focus your debugging efforts.

  2. 2017-01-05 03:01

    When you are working with Outlook from Excel set your Outlook Inbox references like this.

    Option Explicit
    Public Sub Example()
        Dim olApp As Outlook.Application
        Dim olNS As Outlook.Namespace
        Dim Inbox As Outlook.MAPIFolder
        Dim Items As Outlook.Items
        Dim i As Long
        '// Ref to Outlook Inbox
        Set olApp = CreateObject("Outlook.Application")
        Set olNS = olApp.GetNamespace("MAPI")
        Set Inbox = olNS.GetDefaultFolder(olFolderInbox).Folders("Folder Name")
        Set Items = Inbox.Items
        For i = Items.count To 1 Step -1
            Debug.Print Items(i) '
    '       do something with Items
    End Sub

    Make sure to set Microsoft Outlook Object XX.X in the Tools>Reference menu of VBE

    See another example here http://stackoverflow.com/a/40356349/4539709

◀ Go back