Solved

Storing Outlook emails in an Access database

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

Suggested Solutions

Title # Comments Views Activity
Outlook 2016 backdated email without sending 6 41
How to restrict users sending out emails to all 1 37
excel exchange 7 41
exchange, calendar, outlook 1 35
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
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 …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

831 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