Store email into sql server 2008 via Outlook 2007

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
Who is Participating?
David LeeCommented:
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

culminITAuthor Commented:
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

David LeeCommented:
Sorry, I'm not an SQL expert.  I simply know that this can be done via ADO calls from Outlook.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.