?
Solved

Grab info with Attachments from Outlook to Access table

Posted on 2014-03-13
4
Medium Priority
?
280 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:Stephen Roesner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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