Storing Outlook emails in an Access database


I have to build a system that stores outlook emails in an Access table simply
by emailing.

So in effect when in an email is sent the body and header of the mesage are saved in an Access table

Thank you
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
David LeeConnect With a Mentor Commented:
Here it is.  This code has to go in the ThisOutlookSession module.  Be sure to remember to enable macros.  This code saves the Subject and Body of every message that's sent.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim adoCon As Object, _
        strSQLCommand As String, _
        strSubject As String, _
        strMessage As String
    strSubject = Replace(Replace(Item.Subject, Chr(34), Chr(34)&Chr(34)), "'", "''")
    strMessage = Replace(Replace(Item.Body, Chr(34), Chr(34)&Chr(34)), "'", "''")
    'Change the database field names and associated message properties on the following line as desired
    strSQLCommand = "INSERT INTO SentItems (Subject, Message) VALUES ('" & strSubject & "', '" & strMessage & "')"
    Set adoCon = CreateObject("ADODB.Connection")
    With adoCon
        'Change the database connection string on the following line as needed
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Datafiles\eeTesting.Mdb;Persist Security Info=False"
        .CursorLocation = 2
        .Execute strSQLCommand
    End With
    Set adoCon = Nothing
End Sub
David LeeCommented:
Hi murbro,

This is possible, but there are obstacles.  First, the code will only work from Outlook.  If your people use OWA at all, then there is no practical solution for this unless you want to write an Exchange event-sink, not something for the faint of heart.  Two, accessing the body of a message is going to trigger Outlook's built-in security.  This will cause a dialog-box to pop-up warning that an application is accessing your mail and asking for permission to allow it to continue.  There are ways around this.  Two of these require the use of third-party tools.  Another is to write this as an Outlook add-in.  They're more difficult to write than macros.  The final way is to sign the code with a digital certificate.  Three, if this is for record keeping purposes, like Sarbanes-Oxley, then it's unreliable.  You should look for a third-party server based tool instead.  Macro code can be disabled or altered too easily.  On the other hand, if this is just for you, then that wouldn't be a problem.  

I can post macro code showing how to do this if you want to go that route.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:

Thanks for the info. Please would post that macro code. I think I'll take that route.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
David LeeCommented:
You're welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.