Store email into sql server 2008 via Outlook 2007

Posted on 2009-02-23
Last Modified: 2012-05-06
Hey all

I have written an application that stores files under the filestream datatype. It allows me to save any binary file onto the file system and to retrieve the file via the DB. I then also open the file in its respected application.

I did the following as a test to see whether database would save an entire email file:
I copied and saved an entire email message into a temp folder(with .msg extension), then stored it and successfully retrieved it via outlook again. So I know that this part actually works

The thing is that I want to enable the user to do this from outlook itself.

I want to allow the user to do the following:
-Click a custom button in outlook to load a form.
-This form will allow the user to connect to the database automatically and to choose a company-> Then choose an active project and task to which the email should belong.
-The email should then be saved as a .msg file in the database that refers to that specific task.

Is there any way that this could be done?

I would appreciate some samples, links and any other information.

Thank you
Question by:culminIT
    LVL 76

    Accepted Solution

    Hi, culminIT.

    "Is there any way that this could be done?"
    Yes.  Something like this should get the job done.
    Sub ExportMessagesToSQL(olkMessage As Outlook.MailItem)
        Dim adoCon As Object
        Set adoCon = CreateObject("ADODB.Connection")
        'Change the connection string on the next line'
        adoCon.Open "SQL Connection String Goes Here"
        'Change the file name and path on the next line'
        olkMessageSaveAs "C:\MyMessage.msg", olMSG
        'Edit this line for SQL.  I do not know the exact syntax for inserting a file into SQL'
        adoCon.Execute "INSERT INTO Messages (SomeFieldName) VALUES('Path to File')"
        Set adoCon = Nothing
    End Sub

    Open in new window


    Author Comment

    Sorry for asking another question with regards to this. Is there any way I can call the stored procedure I have written instead of inserting it. I am using bulk openrowset to do this. The code below is the code within the stored proc.

    	DECLARE @SQL varchar(max);
       SET @SQL = 'INSERT INTO OpenBLOBDB.dbo.Files (FileID,FileDesc,FileExt,FileContents)
        SELECT  NEWID(),''' + @File_Desc +  ''',''' +  @File_Ext + ''', BulkColumn FROM Openrowset( Bulk ''' + @File_Con + ''',SINGLE_BLOB) AS blob';
        exec sp_sqlexec @SQL

    Open in new window

    LVL 76

    Assisted Solution

    by:David Lee
    Sorry, I'm not an SQL expert.  I simply know that this can be done via ADO calls from Outlook.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
    Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    729 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

    17 Experts available now in Live!

    Get 1:1 Help Now