4

I am looking (and so far not finding any) for a solution to archive e-mail items from my Outlook into SQL Server.

My PST is beginning to get really really big, and I'd love to extract my older e-mail into SQL Server in a way so I can still easily find mails if needed. I would prefer SQL Server as the storage medium since I'm familiar with it, and it's rock solid - I don't want to have a collection of PST files or CHM files or anything like that.

Does anyone know of such a solution?

3 Answers 3

1

This probably isn't exactly what you're looking for, but it's a start (and free!). It's an Outlook macro I wrote a while back to do just this. The biggest issue is it doesn't do attachments which could be a showstopper for you, but thought I'd share anyway. You click on a folder you want to export and it sends the message info to a SQL table. It's easy enough to modify this if you want more fields.

Sub ExportMsgData()
    Dim objConn As New ADODB.Connection
    Dim rsMsgs As New ADODB.Recordset
    Dim strDBFile As String

    On Error Resume Next
    Set objConn = New ADODB.Connection
    With objConn
        .ConnectionString = "Provider=SQLOLEDB;data source=MySQLSvr;initial catalog=MyMail;integrated security=SSPI"
        .Open
    End With

    Set objExp = Application.ActiveExplorer
    If objExp.Selection.Count > 0 Then
        If objExp.Selection(1).Class = Outlook.OlObjectClass.olMail Then
            For Each objMsg In objExp.Selection
                With rsMsgs
                    .CursorLocation = adUseClient
                    .Open "SELECT TOP 1 * FROM t_Msg", objConn, adOpenDynamic, adLockBatchOptimistic
                    .AddNew
                    !MsgFrom = objMsg.SENDERNAME
                    !MsgFromEmail = objMsg.SenderEmailAddress
                    !MsgDateSent = objMsg.SentOn
                    !MsgDateReceived = objMsg.ReceivedTime
                    !MsgSubject = objMsg.Subject
                    .UpdateBatch
                    rsMsgs.Close
                End With
            Next
        End If
    End If

    objConn.Close
    Set rsMsgs = Nothing
    Set objConn = Nothing
End Sub
1
  • Thanks for sharing - I'm still hoping to find a full-fledged solution. I'm amazed there doesn't seem to be anything out there - I would imagine a lot of people are faced with similar situations.
    – marc_s
    Apr 6, 2010 at 18:24
0

I'm still amazed no one has really been able to come up with an answer.... what do you guys do to archive your e-mails? Toss 'em out of Outlook or Thunderbird after 6 months and forget about them?? Store them as .txt or .eml files somewhere??

Anyway, the best solution I've found so far isn't using SQL Server - but it still looks quite powerful and useful: MailStore Home - free for personal / home use, and quite nifty at that. Too bad it doesn't support direct .pst file import :-( - that's only in the $$$$$ pro server version, but so far, that's the only bitter-sweet moment with this app.

0

Easy - sort of - solution I use to archive emails out of Outlook is a follows:

  1. In Outlook, do NOT do an EXPORT. Instead, open the folder from which you want to archive messages.
  2. Sequence the messages as desired. I order by date sent to allow for an arbitrary time frame.
  3. Highlight the messages to be copied out. (Click, Shift+Click or press Ctrl +A for all.)
  4. In the File menu, click Save As and enter an appropriate file name. This will give you a very simple text file of continuous text for all selected emails. If you want separate messages, unfortunately you will need to select one message at a time.
  5. In SQL Server, create a table containing an identity column followed by a varchar(4000) column. Import your text file into this table, which preserves physical sequence via the identity, and a text column for each line. Depending on your SQL skills, you may parse this table as desired. The many SQL functions such as, but not limited to, LTRIM, RTRIM, LIKE, ISNUMERIC, SUBSTRING, LEFT, RIGHT, LEN, PATINDEX, CHARINDEX etc. allow you to extract into variables from the text strings.

I use this method to extract daily investment holding price quotes from my broker's daily emails and create price update files for Quicken instead of relying on my broker's data and Quicken's arbitrary online capabilities.

If you just want to archive messages, the Mail Store Home free application is terrific, and it also has various file creation options too. Try it. And you can even put messages BACK INTO outlook folders with it too.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .