Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-05-2017, 08:00 AM   #1
dudezzz
Newly Registered User
 
Join Date: Feb 2005
Posts: 60
Thanks: 2
Thanked 0 Times in 0 Posts
dudezzz is on a distinguished road
Export Outlook email to Access - Run Time Error '424': Object required

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? 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

dudezzz is offline   Reply With Quote
Old 02-05-2017, 08:37 AM   #2
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Export Outlook email to Access - Run Time Error '424': Object required

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
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 02-05-2017, 08:46 AM   #3
dudezzz
Newly Registered User
 
Join Date: Feb 2005
Posts: 60
Thanks: 2
Thanked 0 Times in 0 Posts
dudezzz is on a distinguished road
Re: Export Outlook email to Access - Run Time Error '424': Object required

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?

dudezzz is offline   Reply With Quote
Old 02-05-2017, 08:50 AM   #4
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Export Outlook email to Access - Run Time Error '424': Object required

Do you have a link to where you got the code?
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 02-05-2017, 09:00 AM   #5
dudezzz
Newly Registered User
 
Join Date: Feb 2005
Posts: 60
Thanks: 2
Thanked 0 Times in 0 Posts
dudezzz is on a distinguished road
Re: Export Outlook email to Access - Run Time Error '424': Object required

http://www.vbforums.com/showthread.p...-to-access-VBA
dudezzz is offline   Reply With Quote
Old 02-05-2017, 11:15 AM   #6
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Export Outlook email to Access - Run Time Error '424': Object required

I did notice you have chanced your code a bit. I see this: objFolder.Items.Count
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 02-05-2017, 11:20 AM   #7
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,821
Thanks: 7
Thanked 311 Times in 301 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: Export Outlook email to Access - Run Time Error '424': Object required

You'll notice the link you supplied gets the same error you are getting.

__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
The Following User Says Thank You to RuralGuy For This Useful Post:
dudezzz (02-05-2017)
Old 02-05-2017, 11:45 AM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Export Outlook email to Access - Run Time Error '424': Object required

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.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
dudezzz (02-05-2017)
Old 02-05-2017, 12:53 PM   #9
dudezzz
Newly Registered User
 
Join Date: Feb 2005
Posts: 60
Thanks: 2
Thanked 0 Times in 0 Posts
dudezzz is on a distinguished road
Re: Export Outlook email to Access - Run Time Error '424': Object required

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_emai...ss_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(olFold erInbox)

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(olFold erInbox)
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 is offline   Reply With Quote
Old 02-05-2017, 12:59 PM   #10
dudezzz
Newly Registered User
 
Join Date: Feb 2005
Posts: 60
Thanks: 2
Thanked 0 Times in 0 Posts
dudezzz is on a distinguished road
Re: Export Outlook email to Access - Run Time Error '424': Object required

Found it. Appears that this line should be: OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox).Parent.Folders("Test")


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

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(olFold erInbox).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
dudezzz is offline   Reply With Quote
Old 02-06-2017, 02:49 AM   #11
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Export Outlook email to Access - Run Time Error '424': Object required

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")

Cronk is offline   Reply With Quote
Reply

Tags
outlook , run time error 424 , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-time error '424': Object required chmcke01 Modules & VBA 6 03-03-2016 08:33 AM
Run time error 424 object required bmorse Modules & VBA 6 06-12-2015 09:35 AM
[SOLVED] Run-time error 424, Object required Wappervliegje Modules & VBA 4 04-28-2015 11:34 PM
run-time error 424 object required - access wolfiewoman Forms 7 05-07-2009 05:09 AM
run time error '424' object required aman Forms 1 03-30-2009 11:07 AM




All times are GMT -8. The time now is 07:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World