Outlook 2007 transfer .msg files over a filestream

Question

Hi all... I need some guidance on how I might be able to complete this task.  I'm using VS 2008 Pro and want to create a VSTO Add-in for Outlook 2007 to save the given message that's open in the current Inspector window in .msg format and push it into a filestream which is on a SQL server.  The purpose is I have written a custom document control system using filestream technology in SQL Server 2008 and VB.net for my frontend.  It allows my employees to access documents that would normally be stored on a file server but through VB.net interface which has more complex security, check-in/out concepts, extra metadata fields and tracking/audit information when the files are modified. 

One major aspect of this software that I haven't implemented yet is that ability to save our incoming emails from our customers to be stored as a permanent record in our document control software.  Right now, they can do a 'Save As' manually in outlook to a .msg file, then upload it.  Or they can convert it into a PDF and upload it.  Either way you slice it, it requires alot of steps.  I would like to be able to click a button on the outlook ribbon or commandbar and automatically grab the selected email, retain it in .msg format and be prompted to select what customers and part the email is related to and it suck it over to the SQL Server.  I just don't know how to tell outlook to select the current message, convert it to .msg and allow me to push it into a filestream.

The MailItem.SaveAs method doesn't work because it's expecting to write directly to a file system path.  I need it to loop through a filestream buffer instead. 

Below is my working code for my file uploads.  But what I want the outlook add-in to do select the current email and put itself into the iStream loop as a .msg binary file.

Try
            imp.ImpersonateStart()
            Dim FileSize As Integer
            UploadCommand.CommandText = "INSERT INTO HQMS_Files (DocGUID, FirstUploadDate, FileBlob) VALUES ('" & NewUploadGUID.ToString & "', '" & FirstUploadDate & "', CAST('' AS VARBINARY(MAX)))"
            UploadCommand.Connection = UploadConnection
            UploadCommand.Connection.Open()
            UploadCommand.ExecuteNonQuery()
            UploadCommand.CommandText = "SELECT FileBlob.PathName() FROM HQMS_Files WHERE DocGUID = '" & NewUploadGUID.ToString & "'"
            Dim filePath As String = Nothing
            Dim pathObj As Object = UploadCommand.ExecuteScalar()
            If Not pathObj.Equals(DBNull.Value) Then
              filePath = DirectCast(pathObj, String)
            Else
              Throw New System.Exception("FileBlob.PathName() failed to read the path name for the FileBlob column.")
            End If

            Dim Transaction As SqlTransaction = UploadConnection.BeginTransaction("mainTransaction")
            UploadCommand.Transaction = Transaction
            UploadCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
            Dim obj As Object = UploadCommand.ExecuteScalar()
            Dim txContext As Byte() = Nothing
            If Not obj.Equals(DBNull.Value) Then
              txContext = DirectCast(obj, Byte())
            Else
              Throw New System.Exception("GET_FILESTREAM_TRANSACTION_CONTEXT() failed")
            End If
            Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Write)
            Dim iStream As System.IO.Stream
            Dim buffer(16384) As Byte
            Dim length As Long
            Dim datatoread As Long
            Dim file_path As String = filesEnum.Current.ToString
            imp.ImpersonateStop()
            iStream = New FileStream(file_path, FileMode.Open, FileAccess.Read, FileShare.Read)
            imp.ImpersonateStart()
            Try
              FileSize = iStream.Length
              datatoread = iStream.Length
              StatusStrip_ProgressBar.Maximum = iStream.Length
              StatusStrip_ProgressBar.Step = iStream.Length
              StatusStrip_ProgressBar.Value = 0
              While datatoread > 0
                length = iStream.Read(buffer, 0, 16384)
                sqlFileStream.Write(buffer, 0, length)
                sqlFileStream.Flush()
                iStream.Flush()
                ReDim buffer(16384)
                datatoread = datatoread - length
                StatusStrip_ProgressBar.Value += 16384
              End While
            Catch ex As Exception
              'no nothing
            End Try
            sqlFileStream.Close()
            iStream.Close()
          

Answer 1

You will need to save the item as a MSG file using SaveAs and then take that file to send as your stream, then delete the saved MSG file. That's the only way I see it working unless you want to reinvent the wheel and design your own save method that takes all properties you are interested in and streams them.

 

For the current item in the active window (Inspector) you can use olApp.ActiveInspector() to get the current active Inspector, assuming olApp is the Outlook.Application object. From there Inspector.CurrentItem gets you that mail item.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

 

 

"jtoddhanard" <=?utf-8?B?anRvZGRoYW5hcmQ=?=> wrote in message news:7f8e3e98-3e69-4b8f-b6ab-f1bb1782017c...

Hi all... I need some guidance on how I might be able to complete this task.  I'm using VS 2008 Pro and want to create a VSTO Add-in for Outlook 2007 to save the given message that's open in the current Inspector window in .msg format and push it into a filestream which is on a SQL server.  The purpose is I have written a custom document control system using filestream technology in SQL Server 2008 and VB.net for my frontend.  It allows my employees to access documents that would normally be stored on a file server but through VB.net interface which has more complex security, check-in/out concepts, extra metadata fields and tracking/audit information when the files are modified. 

One major aspect of this software that I haven't implemented yet is that ability to save our incoming emails from our customers to be stored as a permanent record in our document control software.  Right now, they can do a 'Save As' manually in outlook to a .msg file, then upload it.  Or they can convert it into a PDF and upload it.  Either way you slice it, it requires alot of steps.  I would like to be able to click a button on the outlook ribbon or commandbar and automatically grab the selected email, retain it in .msg format and be prompted to select what customers and part the email is related to and it suck it over to the SQL Server.  I just don't know how to tell outlook to select the current message, convert it to .msg and allow me to push it into a filestream.

The MailItem.SaveAs method doesn't work because it's expecting to write directly to a file system path.  I need it to loop through a filestream buffer instead. 

Below is my working code for my file uploads.  But what I want the outlook add-in to do select the current email and put itself into the iStream loop as a .msg binary file.

Try
      imp.ImpersonateStart()
      Dim FileSize As Integer
      UploadCommand.CommandText = "INSERT INTO HQMS_Files (DocGUID, FirstUploadDate, FileBlob) VALUES ('" & NewUploadGUID.ToString & "', '" & FirstUploadDate & "', CAST('' AS VARBINARY(MAX)))"
      UploadCommand.Connection = UploadConnection
      UploadCommand.Connection.Open()
      UploadCommand.ExecuteNonQuery()
      UploadCommand.CommandText = "SELECT FileBlob.PathName() FROM HQMS_Files WHERE DocGUID = '" & NewUploadGUID.ToString & "'"
      Dim filePath As String = Nothing
      Dim pathObj As Object = UploadCommand.ExecuteScalar()
      If Not pathObj.Equals(DBNull.Value) Then
       filePath = DirectCast(pathObj, String)
      Else
       Throw New System.Exception("FileBlob.PathName() failed to read the path name for the FileBlob column.")
      End If

      Dim Transaction As SqlTransaction = UploadConnection.BeginTransaction("mainTransaction")
      UploadCommand.Transaction = Transaction
      UploadCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
      Dim obj As Object = UploadCommand.ExecuteScalar()
      Dim txContext As Byte() = Nothing
      If Not obj.Equals(DBNull.Value) Then
       txContext = DirectCast(obj, Byte())
      Else
       Throw New System.Exception("GET_FILESTREAM_TRANSACTION_CONTEXT() failed")
      End If
      Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Write)
      Dim iStream As System.IO.Stream
      Dim buffer(16384) As Byte
      Dim length As Long
      Dim datatoread As Long
      Dim file_path As String = filesEnum.Current.ToString
      imp.ImpersonateStop()
      iStream = New FileStream(file_path, FileMode.Open, FileAccess.Read, FileShare.Read)
      imp.ImpersonateStart()
      Try
       FileSize = iStream.Length
       datatoread = iStream.Length
       StatusStrip_ProgressBar.Maximum = iStream.Length
       StatusStrip_ProgressBar.Step = iStream.Length
       StatusStrip_ProgressBar.Value = 0
       While datatoread > 0
        length = iStream.Read(buffer, 0, 16384)
        sqlFileStream.Write(buffer, 0, length)
        sqlFileStream.Flush()
        iStream.Flush()
        ReDim buffer(16384)
        datatoread = datatoread - length
        StatusStrip_ProgressBar.Value += 16384
       End While
      Catch ex As Exception
       'no nothing
      End Try
      sqlFileStream.Close()
      iStream.Close()
     

---

Ken Slovak MVP - Outlook

Answer 2

Thanks Ken, I was hoping there was a workaround but I'll just do what you suggested.

Answer 3

Here's some follow up code that's working for me now.  I stored the Subject, ReceivedDate in a couple global variables that my upload code will reference in the windows form.  Then I created the windows form with my filestream stuff to upload the file and finally delete the old file after it's done (havn't coded that yet).  This code is using an explorer button vs an inspector button, i'll code an inspector version later.  But if anyone wants to use this an example, feel free.  Some of code was borrowed from other examples online and VSTO 2007 from Eric Carter/Eric Lippert's book.

Public Class ThisAddIn
  Private WithEvents btnUpload As Office.CommandBarButton
  Private HQMSUploadForm As Form
  Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    Dim explorer As Outlook.Explorer = Application.ActiveExplorer()
    If explorer IsNot Nothing Then
      Dim bar As Office.CommandBar = explorer.CommandBars.Add("HQMS Upload", , , True)
      bar.Visible = True
      bar.Position = Office.MsoBarPosition.msoBarTop
      btnUpload = DirectCast(bar.Controls.Add(Office.MsoControlType.msoControlButton, , , , True), Office.CommandBarButton)
      AddHandler btnUpload.Click, AddressOf bar.btnUpload_Click
      btnUpload.Caption = "HQMS Upload"
      btnUpload.Tag = "OutlookAddin1.btnUpload"
      btnUpload.Style = Office.MsoButtonStyle.msoButtonCaption
    End If
  End Sub
  Private Sub btnUpload_Click(ByVal ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles btnUpload.click
    Dim SelectedFolder As Outlook.Folder = Application.ActiveExplorer().CurrentFolder
    Try
      If Application.ActiveExplorer.Selection.Count > 0 Then
        Dim SelObject As Object = Application.ActiveExplorer.Selection.Item(1)
        If (TypeOf SelObject Is Outlook.MailItem) Then
          Dim Email As Outlook.MailItem = TryCast(SelObject, Outlook.MailItem)
          Dim MessageGUID As String = System.Guid.NewGuid.ToString()
          Dim EmailSubject As String = Email.Subject
          gblEmailSubject = EmailSubject
          Dim EmailReceivedDate As Date = Email.ReceivedTime
          gblEmailReceivedDate = EmailReceivedDate
          gblCurrentMsgGUID = MessageGUID
          Email.SaveAs(gblTmpDir & "\" & MessageGUID.ToString & ".msg", Outlook.OlSaveAsType.olMSG)
        End If
      End If
    Catch ex As Exception
      MsgBox(ex.Message)
    End Try
    HQMSUploadForm = New frmHQMSUpload
    HQMSUploadForm.Show()
  End Sub
  Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

  End Sub
#Region "VSTO"
  Private Sub InternalStartup()
    AddHandler Me.Startup, AddressOf ThisAddIn_Startup
    AddHandler Me.Shutdown, AddressOf ThisAddIn_Shutdown
  End Sub
#End Region

End Class