Solved

Storing Outlook emails in an Access database

Posted on 2006-06-23
5
183 Views
Last Modified: 2010-04-08
Hi

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
0
Comment
Question by:murbro
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 16970986
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.

Cheers!
0
 

Author Comment

by:murbro
ID: 16982264
Hi

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

Cheers
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 16982658
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
        .Open
        .Execute strSQLCommand
        .Close
    End With
    Set adoCon = Nothing
End Sub
0
 

Author Comment

by:murbro
ID: 16982662
Thanks very much
0
 
LVL 76

Expert Comment

by:David Lee
ID: 16987506
You're welcome.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

If you don't know how to downgrade, my instructions below should be helpful.
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

862 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now