2

I have created a macro to read Outlook emails and extract values and headers (subject time sent etc.) to Excel.

This was working before but now prompts an error "The attempted operation failed. An object could not be found".

The location of the folder is "Inbox/TIBCO Reports Folder". 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
 'Sheets("OutlookEmail").Cells.Clear

 '//Setup headers for information
Sheets("OutlookEmail").Select
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")
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
jsioson
  • 21
  • 2
  • using the `olFoder` variable for both the `Namespace` and `Folder` object looks fishy to me. I would suggest creating a separate object variable for each of those. (Also using early binding may help in debugging too, with the built in intellisense). – Scott Holtzman Jan 05 '17 at 01:32
  • Hi Scott, I don't know if the suggestion will work. The code was working before and was able to get and read the emails from the outlook folder. Will you able to show to me your suggestion? As I am not very familiar with the VBA macro coding. thanks – jsioson Jan 05 '17 at 02:00
  • Are you running the vba from Excel or Outlook? – 0m3r Jan 05 '17 at 02:09
  • see the code in [this answer](http://stackoverflow.com/questions/11151811/reference-a-folder-by-name#answer-34282145) to understand what I am talking about. Research the web for any methods you are confused about. A simple search will reveal a wealth of information. The answer there uses early binding. – Scott Holtzman Jan 05 '17 at 02:09
  • Hi Om3r, I am running the vba in Excel. – jsioson Jan 05 '17 at 02:18

2 Answers2

2

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 = New 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
    Next
End Sub

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

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

0m3r
  • 12,286
  • 15
  • 35
  • 71
1

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.

John Coleman
  • 51,337
  • 7
  • 54
  • 119