Solved

Grab info with Attachments from Outlook to Access table

Posted on 2014-03-13
4
270 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:Grizbear51
  • 2
  • 2
4 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39928101
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
 
LVL 75
ID: 39928113
Graham ... instead of using the Attachment field, could we load into a OLE Object Unbound field instead ?

mx
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 39928287
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
 
LVL 75
ID: 39969901
"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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question