Solved

Storing Outlook emails in an Access database

Posted on 2006-06-23
5
182 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks very much
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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: …

744 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

12 Experts available now in Live!

Get 1:1 Help Now