Outlook / Email

D

Dean Spencer

Can anyone help? I am importing Emails from Outlook using the following
code:

Public Function ScanInbox(SubjectLine As String)
Dim TempRst As Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
If SubjectLine <> "" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ & SubjectLine
& """")
Else
Set InboxItems = Inbox.Items
End If
For Each Mailobject In InboxItems
With TempRst
On Error Resume Next
.AddNew
!Subject = Mailobject.Subject
!From = Mailobject.SenderName
!To = Mailobject.To
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
!Attachment = Mailobject.Attachment
.Update
'Mailobject.Delete
Mailobject.Read
End With
Next

Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
End Function


The emails which I would like to import contain an attachment. Does anyone
know how I can either save the attachement in Access as a memo or OLE
object? or even save a link to open the attachement?

I have tried adding !Attachment = Mailobject.Attachment but nothing is added
to the temporary table?

Thank you for your help.

Dean Spencer.
 
G

Geoff

Dean,
Can anyone help? I am importing Emails from Outlook using the
following code... The emails which I would like to import contain
an attachment... Does anyone know how I can either save the
attachement in Access as a memo or OLE object? or even save
a link to open the attachement? ... I have tried adding
!Attachment = Mailobject.Attachment but nothing is
added to the temporary table?

You may have a compelling reason for wanting to store emails in an Access
database. I came to the conclusion some time back that maybe it's not such
a good idea.

However, if you want to do this, below is a revision to your code.

It's easy to store basic email information in an Access database, like the
MailItem fields:- To, From, Subject, Body, etc. (The Body property gives
you the plain text of the email message, which could be stored in a memo
field.)

Attachments are problematic. It's probably best not to store these in the
Access database for the following reasons:

1. The database could become very large very quickly.
2. The database can take up hundreds of megabytes of disc space.
3. Everything would be in one file.
4. If you lose or corrupt that one file, you've lost everything.
5. It becomes problematic to backup regularly.

As an alternative, you could save the attachments to one or more hard disc
folders. You could store the path\name to the attachments in a hyperlink
field. I've not developed this in the demonstration code below but you'll
get the idea and will be able to progress it yourself.

Here's my revision of your code. But first a tip:
Consider using multiple Outlook data (or personal store) files to retain old
emails. These files are created using Outlook's File > New menu. You could
create a pst file for each year for archiving purposes, eg "Archive
2006.pst".



Public Sub TestScanInbox()
Call ScanInbox
End Sub

Public Function ScanInbox( _
Optional SubjectLine As String = "")

' This function does not set a return value.

Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object

Dim objMI As Outlook.MailItem
Dim objATTS As Outlook.Attachments
Dim objATT As Outlook.Attachment
Dim strSavePathName As String

Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
If SubjectLine <> "" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ _
& SubjectLine & """")
Else
Set InboxItems = Inbox.Items
End If

' Loop through all items in the Inbox:
For Each Mailobject In InboxItems
GoSub GetNextMailItem
Next

Set objATT = Nothing
Set objATTS = Nothing
Set Mailobject = Nothing
Set InboxItems = Nothing
If Not TempRst Is Nothing Then TempRst.Close
Set TempRst = Nothing
Set Inbox = Nothing
Set OlApp = Nothing

MsgBox "Finished"

Exit_Here:

Exit Function

GetNextMailItem:

' Ensure we've got a MailItem object:
If TypeName(Mailobject) <> "MailItem" Then Return

' Use an Outlook MailItem object so we
' can see its properties while coding:
Set objMI = Mailobject

' If you only want Mailitems that have attachments:
If objMI.Attachments.Count = 0 Then Return

With TempRst
On Error Resume Next
.AddNew
!Subject = objMI.Subject
!From = objMI.SenderName
!To = objMI.To
!Body = objMI.Body
!DateSent = objMI.SentOn

' The MailItem object does not have an Attachment
' (singular) property. It has an Attachments (plural)
' property, which returns an attachments collection.
' You would need to loop through the Attachments
' collection to process all attachments, in which case
' you'd better have a separate table with a one-to-
' many relationship to store possible multiple
' attachments. Alternatively, if you know there will
' only be one attachment or if you only want to store
' the first attachment, you could code for just the
' first attachment.
'
' I don't know how to use VBA to insert an
' OLE object into a field using DAO. If you write:
' !Attachment = objMI.Attachments(1)
' the attachment will not appear in a bound object
' frame on a form (for the table).
'
' On the other hand, if you save the attachment to
' a hard-disc folder and have a hyperlink field
' pointing to the path\filename, then it would work.
' Here's the code to save the attachments to the
' hard-disc:

' Save attachments:
Set objATTS = objMI.Attachments
For Each objATT In objATTS

' Use a hard-disc path for this attachment.
' Consider using a subfolder structure for
' [Year], [Month], [YYYYMMDD HHNN Sender Subject].
' Using the date in reverse order means the
' folders sort into chronological order
' automatically.
strSavePathName = "C:\MyEmails\" & objATT.FileName
objATT.SaveAsFile strSavePathName

' The next code line is incorrect if you are
' storing multiple attachments because each new
' attachment would overwrite the last in the
' same Attachments field. If the Attachment field
' is a hyperlink field, then it needs to be in a
' different table with a one-to-many relationship
' with this table and you need to add a new
' record to the related table for each attachment
' (ie you'd need to open a second recordset and
' don't forget to write the foreign key field).
' If the Attachment field is a Text field, then
' it can be in this table because you could
' concatenate file names (on separate lines),
' but then you wouldn't have a convenient way of
' opening each file.

' The next code line is incorrect?
' (See above notes)
' Store path\name in this table:
!Attachment = strSavePathName

Next

.Update
'objMI.Delete

' The MailItem object does not have a Read method:
'objMI.Read

End With

DoEvents
Return

End Function


Regards
Geoff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top