أعلى مُجيب
Outlook 2007 transfer .msg files over a filestream

السؤال
-
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 م