Save an outlook mail to access

[kbsudhir]

Hi Guys,

Can we save a mail in outlook to a database in access through a macro.

1. Will Ole Object Data Type work.....??????


Please guide as I am in a great need of that.

I created a macro for that see below:

Sub Cap_Dat_Tim()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim fld_path As Object
Dim flagclr As Integer
Dim str As String, dat As String, tme As String, ad As Date
Set abd = Outlook.GetNamespace("MAPI")
Set fld_path = abd.Folders("Mailbox - Balakrishna, Sudhir - OSP").Folders("Inbox")
count = 1
For Each Item In fld_path.Items


flagclr = Item.FlagIcon
If flagclr = 6 Then

str = Item.ReceivedTime
ad = Item.ReceivedTime
'MsgBox (ad)

tme = Trim(Right(str, 11))
dat = Trim(Replace(str, tme, ""))

Range("G1").Value = dat
Range("G2").Value = tme

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Q:\Product Management\Purchasing\Catalog Specialists\Sudhir\Audited Daily Reports\Dec\Projects\MailsDB.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "select * from Table_Mails", cn, adOpenStatic, adLockOptimistic

rs.AddNew
rs.Fields("Rec_Time") = tme
rs.Fields("Rec_Date") = CDate(dat)
rs.Fields("Mails") = Item

rs.Update

Exit Sub
End If

Next Item
End Sub

Here I am getting an error - "Multistep Ole DB operation generated error. Check each OLE DB status value, if available. no Work was done."
Run-Time Error '-2147217887(80040e21)'

Please guide me in this regard.

[mattj]

Why not just link the Outlook messages to Access as a linked table? Then if you want to store the messages in another table, use an append query.

[kbsudhir]

Thanks Matt,

But I am new to access hence I am not able to understand you correctly can you please elaborate, so that I am understand it better.

Secondly I want to save only those mails which have been flagged red.

Thanks for your time.

[mattj]

If you choose File > Get External Data > Link tables, you can change the "Files Of Type" to Outlook. It will bring up a wizard where you can link your inbox as an Access table. If you then wanted to move the message data to a local Access table, you can use an append query (look it up) to move the data from one table to another.
If the message has not been read, the "Content Unread" field should have a value.

[kbsudhir]

Thanks I have created the table as you instruced.
I have few questions -
1. Will the table will get updated when a new mail arrives, if no what code should be exceted update it.
2. Between two updations, if a mail's attributed is added ex. a mail has been flagged, will the updation update the same in the database......???
3. Can we save the whole mail itself as we can copy paste it in word.(attaching it for your reference)

[kbsudhir]

I am trying to attach but the Manage Attachments used to attach files in the forum is not working 4 me at the moment.

Basically I just copied the mail and pasted it in word. Now u can doule click it and open it from word.

I will attach it ASAP

Thanks for your time

[kbsudhir]

:bow:

i have uploaded the file for your reference.

Thanks
Sudhir

[mattj]

For question 1, 2, and 3 - I have no idea, test it and find out.

[kbsudhir]

For the Question
1. We have write a code to do that.
2. It will not capture flag status, we have do it manually using code.
3. Still researching on it, if anyone has any info on the same pls let me know.

Thanks
Sudhir

[kbsudhir]

Hi

All who want to capture incoming mails and outgoing mails from your outlook pls refer:

http://vbaexpress.com/forum/showthread.php?t=16688
As this link will discuss and slove ur issues.

Regarding saving the mails directly from outlook to access I am still researching on it

Thanks
Sudhir