.NET Save Outlook Email to Database as Byte Array

In a previous article I wrote about how to create a custom add-on for Outlook that will archive emails based on a keyword in the Email’s subject line. I will expand on this concept to show how to convert the sent email from an Interop.Outlook.MailItem COM object to a save-able .MSG email message. The ultimate goal of this is to convert the sent email into a format that can be saved to a database table as a VarBinary datatype.

Background Info: Why Convert the Email to .MSG Format?

Using the Application_ItemSend method of a Visual Studio 2010 Outlook Add-in project, one can capture the Email message being sent using the Outlook.MailItem object. Although in theory it would make sense that one could directly convert this object to a Byte array to be saved in a database table, this is in fact not a viable option. The Outlook.MailItem object is in fact a COM object that cannot be dynamically saved/loaded. To achieve the desired save/load functionality the Outlook.MailItem object will first need to be converted to a .MSG file.

How can an Outlook.MailItem object be programmatically converted to a .MSG file?

This operation cannot be achieved entirely within working memory, and requires access to the physical hard-drive. In order for this to work, I add code that creates a temporary folder on the system drive as a place for the Email format changes to happen. This can be done fairly easily using the System.IO.Directory object. As an example the following code checks for a folder name and if it does not exist it will create the folder:

Private Sub SaveEmailToFile(ByRef miMailItem As Outlook.MailItem)
 If Not Directory.Exists(SAVE_FOLDER) Then
 Directory.CreateDirectory(SAVE_FOLDER)
 End If
 miMailItem.SaveAs(m_strSaveFullPath, Outlook.OlSaveAsType.olMSG)
End Sub

In the code above, also note that the Outlook.MailItem object is being converted to a .MSG file using the SaveAs function.

One thing to note is that the save path of the email object uses the subject line as the name of the new file on the hard disk drive. In order for this to work without generating errors, the naming conventions for Windows file names need to be enforced by removing any special characters that could cause problems.

I do this with a simple regular expression from the System.Text.RegularExpressions Namespace as follows:

Dim strFileName As String = Regex.Replace(mailItemCopy.Subject, "[\/\\\:\?\*\<\>\|]", "") & ".msg"

Once the file has been saved to the hard disk drive, it can be read into the logic of the application and can be converted directly to a Byte array for saving to the database. Below is an example of how to do so:

Dim tmpFile As New FileStream(m_strSaveFullPath, FileMode.Open, FileAccess.Read)
Dim btSaveFile(tmpFile.Length) As Byte
tmpFile.Read(btSaveFile, 0, tmpFile.Length)
tmpFile.Close()

Review of Converting and Saving Outlook.MailItem as a .MSG file

So really the conversion code is quite straightforward. In review here are the steps:

1. First the email has to be captured as an Outlook.MailItem object in the Application_ItemSend event.
2. Once this is in place, the Outlook.MailItem object needs to be converted to a .MSG format file in order for it to be usable (ie: for saving in a database or as a flat-file).
3. To convert an Outlook.MailItem object to .MSG format requires temporarily saving to the hard disk drive.
4. As part of setting the path and name of the file, make sure that illegal characters are removed from the name of the new file using the System.Text.RegularExpressions Namespace
5. Use the System.IO.Directory method to create a temporary working directory in which to save the email object.
6. Then use the SaveAs method of the Outlook.MailItem object to perform the actual conversion.
7. Once the .MSG file exists on the hard disk drive, it can be read into a Byte array for saving to a database.

Sample Code

Below is an example of how to save an Outlook.MailItem object to a .MSG format file, then read the file from the FileSystem into a Byte array, and finally save the Byte array to a database table:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.IO
Imports System.Security.Principal
Public Class ThisAddIn
 Const FOLDER_NAME As String = "spec"
 Const SAVE_FOLDER As String = "c:\temp"
 Private m_strSaveFullPath As String = ""
Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
 End Sub
 Private Sub Application_ItemSend(Item As Object, ByRef Cancel As Boolean) Handles Application.ItemSend
 Dim mailItem As Outlook.MailItem = TryCast(Item, Outlook.MailItem)
 If (mailItem IsNot Nothing) Then
 If (mailItem.Subject).ToLower().Contains(FOLDER_NAME) Then
 Dim mailItemCopy As Outlook.MailItem = TryCast(mailItem.Copy, Outlook.MailItem)
 Dim inbox As Outlook.MAPIFolder = Me.Application.GetNamespace("MAPI").GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
 Dim folder As Outlook.MAPIFolder = Nothing
 Try
 folder = inbox.Folders(FOLDER_NAME)
 Catch ex As System.Runtime.InteropServices.COMException
 Catch ex As Exception
 MsgBox(ex.Message)
 End Try
 If (folder Is Nothing) Then
 folder = inbox.Folders.Add(FOLDER_NAME, Outlook.OlDefaultFolders.olFolderInbox)
 End If
 mailItemCopy.Move(folder)
Dim strFromAddress As String = WindowsIdentity.GetCurrent().Name
 Dim strToAddress As String = mailItemCopy.To
 Dim strCCAddress As String = mailItemCopy.CC
 Dim strBCCAddress As String = mailItemCopy.BCC
 Dim strEmailFileSize As String = CStr(mailItemCopy.Size)
If String.IsNullOrEmpty(strFromAddress) Then strFromAddress = ""
 If String.IsNullOrEmpty(strToAddress) Then strToAddress = ""
 If String.IsNullOrEmpty(strCCAddress) Then strCCAddress = ""
 If String.IsNullOrEmpty(strBCCAddress) Then strBCCAddress = ""
Dim strFileName As String = Regex.Replace(mailItemCopy.Subject, "[\/\\\:\?\*\<\>\|]", "") & ".msg"
 m_strSaveFullPath = SAVE_FOLDER & "\" & strFileName
SaveEmailToFile(mailItemCopy)
 SaveEmailToDb(mailItemCopy.Subject, mailItemCopy.Body, strFileName, strEmailFileSize, strFromAddress, strToAddress, strCCAddress, strBCCAddress)
File.Delete(m_strSaveFullPath)
End If
 End If
 End Sub
 Private Sub SaveEmailToDb(ByRef strSubject As String, ByRef strBody As String, ByRef strMailName As String, ByRef strEmailFileSize As String, _
 ByRef strFromAddress As String, ByRef strToAddress As String, ByRef strCCAddress As String, ByRef strBCCAddress As String)
 Dim tmpFile As New FileStream(m_strSaveFullPath, FileMode.Open, FileAccess.Read)
 Dim btSaveFile(tmpFile.Length) As Byte
 tmpFile.Read(btSaveFile, 0, tmpFile.Length)
 tmpFile.Close()
Dim strConnectionString As String = "Data Source=databaseServer;Initial Catalog=databaseName;Persist Security Info=True;User ID=username;Password=userpwd;"
 Dim strSQLInsert As String = "INSERT INTO MailStore " & _
 " (MailText,MailSubject,MailSavePath,MailSentDate,MailFileName,MailSentFrom,MailSentTo,MailSentCC,MailSentBCC,FileObject,FileObjectSize) " & _
 " VALUES " & _
 " (@Subject, @Body, @SavePath,@MailSentDate,@MailFileName,@MailSentFrom,@MailSentTo,@MailSentCC,@MailSentBCC,@File,@strEmailFileSize)"
Dim connection As New SqlConnection(strConnectionString)
 Dim cmd As New SqlCommand(strSQLInsert, connection)
 cmd.Parameters.Add(New SqlParameter("@Subject", SqlDbType.VarChar)).Value = strSubject
 cmd.Parameters.Add(New SqlParameter("@Body", SqlDbType.VarChar)).Value = strBody
 cmd.Parameters.Add(New SqlParameter("@SavePath", SqlDbType.VarChar)).Value = m_strSaveFullPath
 cmd.Parameters.Add(New SqlParameter("@MailSentDate", SqlDbType.DateTime)).Value = DateTime.Now
 cmd.Parameters.Add(New SqlParameter("@MailFileName", SqlDbType.VarChar)).Value = strMailName
 cmd.Parameters.Add(New SqlParameter("@MailSentFrom", SqlDbType.VarChar)).Value = strFromAddress
 cmd.Parameters.Add(New SqlParameter("@MailSentTo", SqlDbType.VarChar)).Value = strToAddress
 cmd.Parameters.Add(New SqlParameter("@MailSentCC", SqlDbType.VarChar)).Value = strCCAddress
 cmd.Parameters.Add(New SqlParameter("@MailSentBCC", SqlDbType.VarChar)).Value = strBCCAddress
 cmd.Parameters.Add(New SqlParameter("@File", SqlDbType.VarBinary)).Value = btSaveFile
 cmd.Parameters.Add(New SqlParameter("@strEmailFileSize", SqlDbType.VarChar)).Value = strEmailFileSize
 connection.Open()
 cmd.ExecuteNonQuery()
 connection.Close()
 connection.Dispose()
 cmd.Dispose()
End Sub
 Private Sub SaveEmailToFile(ByRef miMailItem As Outlook.MailItem)
 If Not Directory.Exists(SAVE_FOLDER) Then
 Directory.CreateDirectory(SAVE_FOLDER)
 End If
 miMailItem.SaveAs(m_strSaveFullPath, Outlook.OlSaveAsType.olMSG)
 End Sub
End Class

Resources:

• CodeProject.com – Save all Outlook Mail Items to Separate Files
• CodeProject.com – Outlook Address Collector
• CodeProject.com – Outlook Email Extractor
• Forums asp.net – checking if folder exists 
• Forums asp.net – save outlook data into sql database table
• Stackoverflow.com – how to save binary file to database
• Techrepublic.com – Retrieve current Windows user information using VB.NET
• MSDN.Microsoft.com – MailItem Object
• MSDN.Microsoft.com – Outlook 2007 transfer .msg files over a filestream
• MSDN.itags.org – Save MailItem as MSG file (Microsoft Visual Studio)