Grab info with Attachments from Outlook to Access table

I am trying to grab Email info and put it into an access table. I have 99 percent of it working except for how to do attachments. I tried researching it but it tends to be very vague in how to do it. Here is the code I have now that is working just fine. What i'm looking for is the code to add to grab attachments and place them into the table to read. I do have a field in the table called attachment and it is defined as an attachment field.

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 = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
 
Set InboxItems = Inbox.Items
 
For Each Mailobject In InboxItems
    If Mailobject.UnRead Then
    With TempRst
        .AddNew
        !Subject = Mailobject.Subject
        !From = Mailobject.SenderName
        !To = Mailobject.To
        !Body = Mailobject.Body
        !DateSent = Mailobject.SentOn
        !Attachment = ???????????????
        .Update
        Mailobject.UnRead = False
 
    End With
End If
Next
 
Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
Stephen RoesnerAnalysisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GrahamMandenoConnect With a Mentor Commented:
Hi Joe

I think an Attachment is much more flexible in terms of the file formats it will support, and also its data compression capability.  Besides, it's what Grizbear51 says s/he is using.

Personally, I would not store the attachments in the database at all, but extract them into a subfolder and have the database reference them by file path.

Cheers,
Graham
0
 
GrahamMandenoCommented:
I don't think there is any way to copy an email attachment to an Attachment field.  You will need to save each attachment to a temporary file using the Attachment.SaveAsFile method, then load it into Access using the Field2.LoadFromFile method, then delete the temporary file.

The documentation on these two methods is here:
http://msdn.microsoft.com/en-us/library/office/ff869359.aspx
http://msdn.microsoft.com/en-us/library/office/ff197396.aspx

There are examples of their use on those pages.  Note that to access the methods and properties associated with attachments and multi-value fields, you must declare your objects as Recordset2 and Field2.

-- Graham Mandeno [Access MVP 1996-2014]
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Graham ... instead of using the Attachment field, could we load into a OLE Object Unbound field instead ?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I would not store the attachments in the database at all"
Well, the reason I asked this question is .... it's related to the post I made in Arvin's DL ... where the subject was "test' ... when I mentioned I was build a Web App - grabbing Outlook data, which ultimately would end up in SQL ASURE. It would be cool if I could also include attachments in the db.   So I don't think using the file path approach would work ?

Did you see my post?
No one commented on the post I made (nothing new here), so I guess nobody cares, lol.
But it's still an excuse for building a Web App :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.