locked
Outlook 2007 transfer .msg files over a filestream RRS feed

  • السؤال

  • 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()
              
    27/جمادى الأولى/1431 10:36 م

الإجابات

  • 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
    • تم وضع علامة كإجابة بواسطة jtoddhanard 29/جمادى الأولى/1431 08:48 م
    28/جمادى الأولى/1431 01:46 م
  • 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
    • تم وضع علامة كإجابة بواسطة jtoddhanard 29/جمادى الأولى/1431 08:48 م
    29/جمادى الأولى/1431 08:44 م

جميع الردود

  • 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
    • تم وضع علامة كإجابة بواسطة jtoddhanard 29/جمادى الأولى/1431 08:48 م
    28/جمادى الأولى/1431 01:46 م
  • Thanks Ken, I was hoping there was a workaround but I'll just do what you suggested.
    28/جمادى الأولى/1431 11:15 م
  • 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
    • تم وضع علامة كإجابة بواسطة jtoddhanard 29/جمادى الأولى/1431 08:48 م
    29/جمادى الأولى/1431 08:44 م