Solved

saving received emails into ms sql server 2000

Posted on 2007-04-11
9
342 Views
Last Modified: 2010-04-08
Hi

I would like to save received email into ms sql server.  I have outlook 2000 and when i receive an email i would like it to go straight into one of my sql server 2000 database.

How can I do this?

Thanks

narmi2
0
Comment
Question by:narmi2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 18897267
Hi, narmi2.

This is possible, although there may be a few hoops to jump through to make it work.  A couple of questions first to get a clearer picture of what you're looking for.

1.  Do you want to save the message as an object or will the SQL table the message goes in have fields that mirror a message's properties?

2.  Is your mail on an Exchange server?  If yes, do you expect this process to work from the Exchange server or only from Outlook when it is open and running?
0
 
LVL 1

Author Comment

by:narmi2
ID: 18897639
Hi BlueDevilFan

1. I would like to save the mail properties into proper table fields, i.e. the person who sent the email should go into a column called "from", the subject should go into the "subject" column and so on.

2. Yes we have exchange server.  Preferably, I would like it to work from exchange server, so I don't have to go to every employees PC to modify their outlook.

Thanks for the reply.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18899848
Hello again.  I'm going to address the issues in reverse order

2.  Not easily done.  Doing this at the server requires writing something called an Exchange event-sink.  Event-sink are to Exchange as macros are to Outlook.  They are blocks of code that are triggered when certain events occur, like mail arriving in a mailbox.  However, they are more complicated to write and any errors in an event-sink could bring the Exchange server down entirely.  They are definitely not for the inexperienced or faint of heart.  I am not prepared to write an event-sink.  I can describe in general terms what it would need to do, but that's it.

1.  Easily done.  There is one hitch though.  Assuming that you do this at the Outlook client instead of at the Exchange server, then accessing any message property that can contain an email address is going to trigger Outlook's built-in security measures.  The result will be a pop-up dialog-box warning that an application is accessing your mailbox and asking you for permission to allow it to continue.  That would pretty much kill any sort of automatic process.  Outlook security cannot be turned off, but there are workarounds.  You can use a third-party tool like Click-Yes to click the Yes button for you each time the warnign appears, or use another third-party tool called Outlook Redemption to safely avoid Outlook security.  Another alternative is to write this as a COM add-in.  The result would be a DLL that you can distribute to your clients.  COM add-ins have different security constraints than macros do and are not subject to the warning message.  

Getting the message properties into an SQL table is easy.  It simply requires creating a connection to the SQL server using ADO and executing an insert statement.  The real issue is where the code will run at (client versus server) and security issues.  I don't have an SQL server at hand, but I can post a sample macro demonstarting the proof of concept using an Access database.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:narmi2
ID: 18904084
Thanks,

Looks like I will have to stick to the first approach.  How exactly do I do that, I do not know how or where to write my sql insert statement in outlook to get it to insert the email info into my table.  Do I have to use VBA?

If i am to use VBA, which trigger even do i need to detect that a new email has arrived?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18904470
Yes, you'll use VBA.  I'll post some sample code (it'll be later today/tonight) showing how to do this using VBA and ADO.  My example will use Access, you'll just need to change the connection string to use SQL Server.
0
 
LVL 1

Author Comment

by:narmi2
ID: 18904535
thanks

the sample code will be very useful
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 18909471
Ok, here's the sample.  This would work via a rule.  The rule could be set to fire for all messages or only ones meeting a specific condition.  

Sub StoreMessageInDatabase(Item As MailItem)
    Dim adoCon As ADODB.Connection, _
        strFields As String, _
        varValues As Variant, _
        strAttachments As String, _
        strRecipients As String
    'Enter the names of the Outlook properties you want to save into the database.  The field names I've used here are only a portion of what's available and I chose them for simply to demonstrate teh capability.
    strFields = "Attachments,BCC,Body,BodyFormat,Categories,CC,CreationTime,HTMLBody,ReceivedTime,Recipients,SenderEmailAddress,SenderName,Sensitivity,Subject,SentTo"
    With Item
        For Each objItem In .Attachments
            strAttachments = strAttachments & objItem.FileName & vbCrLf
        Next
        For Each objItem In .Recipients
            strRecipients = strRecipients & objItem.Name & vbCrLf
        Next
        varValues = "'" & strAttachments & "'," _
            & "'" & FixTextField(.BCC) & "'," _
            & "'" & FixTextField(.Body) & "'," _
            & .BodyFormat & "," _
            & "'" & FixTextField(.Categories) & "'," _
            & "'" & FixTextField(.CC) & "'," _
            & "'" & .CreationTime & "'," _
            & "'" & FixTextField(.HTMLBody) & "'," _
            & "'" & .ReceivedTime & "'," _
            & "'" & strRecipients & "'," _
            & "'" & FixTextField(.SenderEmailAddress) & "'," _
            & "'" & FixTextField(.SenderName) & "'," _
            & .Sensitivity & "," _
            & "'" & FixTextField(.Subject) & "'," _
            & "'" & FixTextField(.To) & "'"
    End With
    Set adoCon = CreateObject("ADODB.Connection")
    'Change the connection string on the next line to that of an SQL connection string
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\MDB\Mail.mdb;Persist Security Info=False"
    adoCon.Execute "INSERT INTO Messages (" & strFields & ") VALUES(" & varValues & ")"
    Set adoCon = Nothing
End Sub

Function FixTextField(varValue) As Variant
    FixTextField = Replace(Replace(varValue, Chr(34), Chr(34) & Chr(34)), "'", "''")
End Function
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 E3 dual authentication advice 1 29
exchange 2007, outlook 3 41
How do I reset the password in Outlook? 10 35
Email question 12 23
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…
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…
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…
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…

730 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