Outlook emails

[Derang]

Hi,

I'm using the below code to import the contents of an outlook folder into an access 97 database but need to also capture any attachments. I'm thinking along the lines of saving the attachments to disk and recording the file path as a hyperlink in a field within the recordset. Although i've no idea how to do this.

Could anyone show me how to do this?

Thanks in advance.


Sub ExportMailByFolder()
'Export specified fields from each mail
'item in selected folder.
Dim ns As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder
Dim Dbase As DAO.Database
Dim Conn As DAO.Connection
Dim RS As DAO.Recordset
Dim intCounter As Integer

Set Dbase = OpenDatabase _
("C:\Documents and Settings\901639\Desktop\Email test.mdb")
Set RS = Dbase.OpenRecordset("Email")
'Cycle through selected folder.
For intCounter = objFolder.Items.Count To 1 Step -1
With objFolder.Items(intCounter)
'Copy property value to corresponding fields
'in target file.
If .Class = olMail Then
RS.AddNew
RS("Subject") = .Subject
RS("Body") = .Body
RS("FromName") = .SenderName
RS("ToName") = .To
RS("Recd") = .ReceivedTime
RS("FromAddress") = .SenderEmailAddress
RS("FromType") = .SenderEmailType
RS("CCName") = .CC
RS("BCCName") = .BCC
RS("Importance") = .Importance
RS.Update
End If

End With
Next
RS.Close
Set RS = Nothing
Set Conn = Nothing
Set ns = Nothing
Set objFolder = Nothing
MsgBox "Import Succesful"
End Sub

 

[darbid]

Very simply this may help to save your attachments. Then save the path in your database field.

Dim out_att As Outlook.Attachment
'after your with statement
 
For Each out_att .Attachments
 
    Debug.Print out_att.DisplayName
 
 
out_att.SaveAsFile "C:\" & out_att.DisplayName
 
 
Next out_att

or are you looking for something more complex?

 

[Derang]

Thanks for the reply, most appreciated.

Although I'm having a problem with this line.

For Each out_att .Attachments

Getting compile error 'Expected: In'

 

[darbid]

my suggestion is not complete you will have to add your code to it.

could you show how you have implemented this into your code.

The attachments is a collection from an email item so you have to have a "with" that is an email item.

 

[pbaldy]

I think that syntax is slightly off. Mine looks like this:

For Each outAttachment In outItem.Attachments

 

[darbid]

yep if you cannot nest the "FOR" in the "WITH" that Derang shows in his original post you will need to start with the outlook MailItem.

 

[Derang]

Thanks for the help with this.

I've changed parts of the code and got it working. It works great now for emails with only 1 attachment, but if there is more than 1 attachment then it saves them all but overwrites the file path in the database with the last attachments path.

I'm thinking that I should have a seperate Attachment table within the database and link the attachments to the email ID field, so for each attachment I would write a new record with the Email ID and then display these via a sub form within my main form.


For Each itm In fld.Items
If itm.Class = olMail Then
Set msg = itm
rs.AddNew
rs("Subject") = msg.Subject
rs("Body") = msg.Body
rs("FromName") = msg.SenderName
rs("ToName") = msg.To
rs("Recd") = msg.ReceivedTime
rs("FromAddress") = msg.SenderEmailAddress
rs("FromType") = msg.SenderEmailType
rs("CCName") = msg.CC
rs("BCCName") = msg.BCC
rs("Importance") = msg.Importance
For Each Att In msg.Attachments
Att.SaveAsFile strFolder & Att.DisplayName
rs("Attachment") = strFolder & Att.DisplayName
Next Att
rs.Update
End If
Next itm
rs.Close

 

[darbid]

I've changed parts of the code and got it working. It works great now for emails with only 1 attachment, but if there is more than 1 attachment then it saves them all but overwrites the file path in the database with the last attachments path.

I'm thinking that I should have a seperate Attachment table within the database and link the attachments to the email ID field, so for each attachment I would write a new record with the Email ID and then display these via a sub form within my main form.

Yes you would be dealing with a 1 - N relationship ie One Email to N many attachments. Thus one way to do this would be to have a table just for your attachments.

Of course there might be simplier ways of doing it if you do not want to build a new table.

You could just save the attachment name in your field and split them with a ";"

eg. Att_name1;Att_name2;Att_name3

Then when you want to use them in the future you just need to use the SPLIT function to break these up into their individual names and of course add your path to them.

I learnt the hard way not to save the full path. IT changed the UNC name and so I had to change all my entries in the database.