Export Outlook email to Access - Run Time Error '424': Object required (1 Viewer)

dudezzz

Registered User.
Local time
Today, 11:43
Joined
Feb 17, 2005
Messages
60
Hi,

With the following code assembled from multiple sources online, I am attempting to download a specific folder in my Outlook Inbox (outlook 365) into my access table called "Email" (Access 2016). I get a Run time error '424' Object required on the line:

If Items.UnRead Then

What am I doing wrong? :banghead: Can someone help me please?

Option Compare Database

Private Sub cmdmail_Click()
Dim adoConn As ADODB.Connection, adoRS As ADODB.Recordset
Dim DBFullName As String
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim i As Long

Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder


'~~> Replace with your database
DBFullName = "C:\mydb.accdb"

Set adoConn = New ADODB.Connection

'~~> Open the database using connection string

adoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";"

Set adoRS = New ADODB.Recordset
If Items.UnRead Then
With adoRS
'~~> Replace Table1 with the name of the table in your database
.Open "SELECT * FROM EMAIL", adoConn, , adLockOptimistic, adCmdText

For i = objFolder.Items.Count To 1 Step -1
With objFolder.Items(i)
If .Class = olMail Then
adoRS.AddNew
adoRS("Subject") = .Subject
adoRS("Body") = .Body
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
adoRS.Update
End If
End With
Next
End With
End If
'~~> Close and Cleanup
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
MsgBox ("done")
End Sub
 

RuralGuy

AWF VIP
Local time
Today, 10:43
Joined
Jul 2, 2005
Messages
13,826
I'm going to take a guess that Items is part of the adoRS collection, which would make the valid line of code:
If adoRS.Items.UnRead Then
 

dudezzz

Registered User.
Local time
Today, 11:43
Joined
Feb 17, 2005
Messages
60
Thank you for giving your time on this. I tried what you suggested, but that gives an error "Method or data member not found"

VBA doesn't show me options when I type "adors.items" in the VBA editor. Maybe that is not a method? I don't know....Perhaps you are steering me in the right direction here....do you know what else I should be doing?
 

RuralGuy

AWF VIP
Local time
Today, 10:43
Joined
Jul 2, 2005
Messages
13,826
Do you have a link to where you got the code?
 

RuralGuy

AWF VIP
Local time
Today, 10:43
Joined
Jul 2, 2005
Messages
13,826
I did notice you have chanced your code a bit. I see this: objFolder.Items.Count
 

RuralGuy

AWF VIP
Local time
Today, 10:43
Joined
Jul 2, 2005
Messages
13,826
You'll notice the link you supplied gets the same error you are getting.
 

Cronk

Registered User.
Local time
Tomorrow, 04:43
Joined
Jul 4, 2013
Messages
2,469
The basic problem is that Items as in Items.Unread is not defined. In fact you should get an error if you try to run debug on your code.

Items is a property of the folder object.

Secondly, Unread is a property of the mail item, not the collection. That is
Items.Unread does not make sense
It should be objFolder.items(n).Unread which will return true or false depending on its read status.
 

dudezzz

Registered User.
Local time
Today, 11:43
Joined
Feb 17, 2005
Messages
60
Thanks for helping me understand this a little better.

While I did not yet fix this code, I have a working code below that I used by tweaking the code found in http://www.blueclaw-db.com/read_email_access_outlook.htm

My working code that does the job is below. I hope this helps someone like me in the future. The only issue I have with this is that instead of "inbox" in my outlook mail, I want this to come from a folder "Test" and I don't know what I should change in the line: Set inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)

If you guys know the answer to the question, I will be grateful. I am still googling to figure this out. thanks again!



Private Sub cmdmail_Click()
Dim TempRst As DAO.Recordset
Dim rst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim inbox As Outlook.MAPIFolder
Dim inboxitems As Outlook.Items
Dim Mailobject As Object
Dim db As DAO.Database
Dim dealer As Integer
Set db = CurrentDb

Set OlApp = CreateObject("Outlook.Application")
'Set inbox = GetNamespace("mapi").PickFolder
Set inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
Set TempRst = CurrentDb.OpenRecordset("email")

Set inboxitems = inbox.Items


For Each Mailobject In inboxitems
If Mailobject.UnRead Then
With TempRst

.AddNew
!Subject = Mailobject.Subject
!SenderName = Mailobject.SenderName
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
.Update
Mailobject.UnRead = False



End With
End If
Next

Set OlApp = Nothing
Set inbox = Nothing
Set inboxitems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing

End Sub
 

dudezzz

Registered User.
Local time
Today, 11:43
Joined
Feb 17, 2005
Messages
60
Found it. Appears that this line should be: OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox).Parent.Folders("Test")


My revised code that works in my environment is below. Thank you both for helping me today.:D

Private Sub cmdmail_Click()
Dim TempRst As DAO.Recordset
Dim rst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim inbox As Outlook.MAPIFolder
Dim inboxitems As Outlook.Items
Dim Mailobject As Object
Dim db As DAO.Database
Dim dealer As Integer
DoCmd.RunSQL "Delete * from email"
Set db = CurrentDb

Set OlApp = CreateObject("Outlook.Application")
'Set inbox = GetNamespace("mapi").PickFolder
Set inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox).Parent.Folders("Test")
Set TempRst = CurrentDb.OpenRecordset("email")

Set inboxitems = inbox.Items


For Each Mailobject In inboxitems
If Mailobject.UnRead Then
With TempRst

.AddNew
!Subject = Mailobject.Subject
!SenderName = Mailobject.SenderName
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
.Update
Mailobject.UnRead = False



End With
End If
Next

Set OlApp = Nothing
Set inbox = Nothing
Set inboxitems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing

End Sub
 

Cronk

Registered User.
Local time
Tomorrow, 04:43
Joined
Jul 4, 2013
Messages
2,469
Rather than starting with a child folder (Inbox), then tracing the path from the parent folder to another child folder, you could make it more simpler by deleting the bit in red

OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox).Parent.Folders("Test")

That is,
OlApp.GetNamespace("Mapi").Folders("Test")
 

Users who are viewing this thread

Top Bottom