Solved

Storing Outlook emails in an Access database

Posted on 2006-06-23
5
184 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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