How to solve “Run-time error 430: Class does not support automation “?

Topic

[shazzad.arla]

I am trying to extract all the mails of last 10 days from outlook to excel using VBA. I am targeting a sub folder under Inbox named BD_Invoice where mails are gathered by following rules. But while I am running the VBA it is showing “Run-time error 430: Class does not support automation or does not support expected interface” The code is

Option Explicit
‘This Code is Downloaded from OfficeTricks.com
‘Visit this site for more such Free Code
Sub VBA_Export_Outlook_Emails_To_Excel()
‘Add Tools->References->”Microsoft Outlook nn.n Object Library”
‘nn.n varies as per our Outlook Installation
Dim Folder As Outlook.MAPIFolder
Dim sFolders As Outlook.MAPIFolder
Dim iRow As Integer, oRow As Integer
Dim MailBoxName As String, Pst_Folder_Name As String

‘Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
MailBoxName = “MY_MAIL_ID”

‘Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
Pst_Folder_Name = “BD_Invoice” ‘Sample “Inbox” or “Sent Items”

‘To directly a Folder at a high level
‘Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)

‘To access a main folder or a subfolder (level-1)
For Each Folder In Outlook.Session.Folders(MailBoxName).Folders
If VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_Found
For Each sFolders In Folder.Folders
If VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) Then
Set Folder = sFolders
GoTo Label_Folder_Found
End If
Next sFolders
Next Folder

Label_Folder_Found:
If Folder.Name = “” Then
MsgBox “Invalid Data in Input”
GoTo End_Lbl1:
End If

‘Read Through each Mail and export the details to Excel for Email Archival
ThisWorkbook.Sheets(1).Activate
Folder.Items.Sort “Received”

‘Insert Column Headers
ThisWorkbook.Sheets(1).Cells(1, 1) = “Sender”
ThisWorkbook.Sheets(1).Cells(1, 2) = “Subject”
ThisWorkbook.Sheets(1).Cells(1, 3) = “Date”
ThisWorkbook.Sheets(1).Cells(1, 4) = “Size”
ThisWorkbook.Sheets(1).Cells(1, 5) = “EmailID”
‘ThisWorkbook.Sheets(1).Cells(1, 6) = “Body”

‘Export eMail Data from PST Folder to Excel with date and time
oRow = 1
For iRow = 1 To Folder.Items.Count
‘If condition to import mails received in last 60 days
‘To import all emails, comment or remove this IF condition
If VBA.DateValue(VBA.Now) – VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 10 Then
oRow = oRow + 1
ThisWorkbook.Sheets(1).Cells(oRow, 1).Select
ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderName
ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).Subject
ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTime
ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).Size
ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress
‘ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body
End If
Next iRow
MsgBox “Outlook Mails Extracted to Excel”
Set Folder = Nothing
Set sFolders = Nothing

End_Lbl1:
End Sub

How can I solve the issue or what quick fix I need to overcome the problem? I am using Office 365.

Thanks.

• This topic was modified 2 years, 4 months ago by shazzad.arla. Reason: Code Correction
• This topic was modified 2 years, 4 months ago by shazzad.arla. Reason: Code Correction
• This topic was modified 2 years, 4 months ago by shazzad.arla. Reason: File Attachment

Replies

@shazzad.arla,

You haven’t said whether you are placing this code in Excel or Outlook. From the looks of it you are putting it in Excel.

I think you are missing defining outlook, which you can do as follows:

Code:

Dim Outlook As Outlook.Application
Set Outlook = New Outlook.Application

See how that goes

Hi Btbs

You are right. I was trying to run the code in excel.

btbs wrote:

Dim Outlook As Outlook.Application Set Outlook = New Outlook.Application

Tried this but reflecting the same result. However, when I tried to extract mails from Sent Items, it is functioning properly.

Try

Code:

Pst_Folder_Name = "Inbox.BD_Invoice"

It is reflecting “Run time Error 91 : Object variable or with block variables not set”.

In debugging, it is highlighting If Folder.Name = "" Then in line 35.

btbs wrote:

Pst_Folder_Name = “Inbox.BD_Invoice”

Ignore the previous suggestion.

As you know the folder you’re looking for use this:

Code:

Folder = Outlook.Session.Folders(MailBoxName).Folders("inbox").Folders("BD_Invoice")

and delete/comment out everything between and including the line “‘To access a main folder or a subfolder (level-1)” to the “End if” before line “‘To access a main folder or a subfolder (level-1)”.

Rationale: you don’t need to loop through folders to find the one you want when you know the path to it.

Hopefully that helps.

Hi Btbs,

It is still showing the same error, 91.

Could you please edit the code in the file, if you have time ?

Thanks in advance.

Shazzad,

See attached.

I’ve added a tab with the code in it for referencing purposes but you can delete that once you see what I’ve done. I created a BD_Invoice folder under my inbox and dumped a bunch of messages into it and the attached code worked for me.

Changes are:

• I deleted the loop as we know the direct path.
• I added a test at (new) line 34 to make sure the item is an email (.class = olMail) to ensure that we are not looking for a field that doesn’t exist.

See how the attached works for you.

Hi Btbs,

Thanks for your time and effort. I believe your solution is workable but I don’t even know what actually is going wrong in my machine. I am using my official laptop and here some restriction could be imposed from administrator. On the other side, the mail extraction can be done from other folders i.e. Sent Items which is really confusing. I really got puzzled and cannot solve the issue. However, thanks once again.

Shazzad

What error(s) are you getting and at what line are they occuring?

The only other thing I suggest is to step through the code line by line and check that everything is doing what you’d expect.

For example, you could add

Code:

msgbox folder.Items().Count

immediately after the “Set Folder =
Outlook.Session.Folders(MailBoxName).Folders(“Inbox”).Folders(“BD_Invoice”)” line and check that is giving the number of messages in the BD_Invoice folder.

Hi Btbs,

It is reflecting the same error , Run-time error 430: Class does not support automation or does not support expected interface. 

However, I have tried your file in my remote desktop and it is perfectly working. And I have found that in my machine macro is disabled from excel trust center using group policy (see attached file). But in remote desktop, that is enabled.

Shazzad

That makes sense. At least you’ve got one method of making it work.

1 user thanked author for this post.

shazzad.arla

Yes and Thanks 🙂