Thanks for taking the time to view my first post.
I'm developing an Outlook Addin (with Visual Studio community 2013) to allow users to easily populate email templates for our customer service dept. The addin is working well however I needed to add some functionality to record some data (to track KPIs) to a db once the email is actually sent.
After some research I implemented a Class level event handler for the Applications itemSend event.
This works perfectly on the development machine, however when I publish and install the app on a client the event appears not to be firing as the code that records data to a database does not get called.
As i couldnt get it working i tried a work around and subsequently changed this to use the .ItemAdd event of the sent folder. (code was borrowed from Pranav here: https://easyvsto.wordpress.com/2010/07/27/how-to-save-mail-content-when-a-mail-is-sent-from-outlook/)
This also works perfectly on the development machine but again, the event seems to fail to fire once the addin is installed on the client macine.
As am now witnessing the same behaviour with the two different methods it leads me to think there is something obvious am not doing. Am I missing something or not taking something into account?
A sample of the code is as follows:
Imports Microsoft.Office.Tools.Ribbon
Imports System.Runtime.InteropServices
Imports System.IO
Imports MySql.Data.MySqlClient
Imports System.Diagnostics
Public Class ThisAddIn
Public WithEvents oSentFolder As Outlook.Folder
Public WithEvents oSentItems As Outlook.Items
Private Sub ThisAddIn_Startup() Handles Me.Startup
oSentFolder = Globals.ThisAddIn.Application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
oSentItems = oSentFolder.Items
End Sub
Private Sub InsertSurveyTimestamp() Handles oSentItems.ItemAdd
System.Windows.Forms.MessageBox.Show("oSentItems.ItemAdd Event")
End Sub
Private Sub Application_ItemSend(ByVal oItem As Object, ByRef Cancel As Boolean) Handles Application.ItemSend
System.Windows.Forms.MessageBox.Show("Application.ItemSend Event")
log.WriteToErrorLog("Application ItemSend Reached", "", "Event")
If TypeOf oItem Is Outlook.MailItem Then Call SentMailTimestamp(oItem)
System.Windows.Forms.MessageBox.Show("end of Itemsend Event")
End Sub
Private Sub SentMailTimestamp(oitem As Outlook.MailItem)
log.WriteToErrorLog("SentMailTimestamp Sub Reached", "", "Subroutine Flag")
Try
'Dim oitem As Outlook.MailItem = oSentItems.Item(oSentItems.Count) 'for use with oSentItems.ItemAdd event
'Check the CSOSurvey property exists to make sure its a CSO Survey email, exit if not a CSOSurvey Email
Dim o = oitem.ItemProperties("CSOSurvey")
If (o IsNot Nothing) AndAlso (o.Value IsNot Nothing) Then
System.Diagnostics.Debug.WriteLine("CSOsurvey email: " & o.Value.ToString)
log.WriteToErrorLog("Email was CSO Survey", "Value: " & o.Value.ToString, "Email Property")
Else
System.Diagnostics.Debug.WriteLine("CSOsurvey email: Null")
log.WriteToErrorLog("Email was NOT CSO Survey", "", "Email Property")
oitem = Nothing
o = Nothing
Exit Sub
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
'Save the timedatestamp of the email template being sent
Using sqlCommand As New MySqlCommand
Dim querystr As String = "INSERT INTO cs_survey_emaillog (SRID,exec,datetimestamp) values(@srid,@exec,@datetimestamp)"
Dim mysqlconn As MySqlConnection
Try
mysqlconn = New MySqlConnection(GlobalVariables.connstr)
mysqlConn.Open()
Catch ex As MySqlException
System.Windows.Forms.MessageBox.Show("Unable to write to Log. Please contact bst.uk@chep.com" & vbCrLf & ex.Message)
log.WriteToErrorLog(ex.Message, ex.StackTrace, "MySQL Error")
Exit Sub
End Try
If mysqlconn.State = Data.ConnectionState.Open Then
Try
With sqlCommand
.Connection = mysqlconn
.CommandText = querystr
.CommandType = Data.CommandType.Text
.Parameters.AddWithValue("@srid", ThisAddIn.templateSRID)
.Parameters.AddWithValue("@exec", ThisAddIn.GetUserName())
.Parameters.AddWithValue("@datetimestamp", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
Dim numrec As Integer = .ExecuteNonQuery()
If ThisAddIn.GetUserName = "mclachd" Then System.Windows.Forms.MessageBox.Show("Query executed. " + numrec.ToString + " Records sent")
End With
Catch ex As MySqlException
log.WriteToErrorLog(ex.Message, ex.StackTrace, "MySQL Error")
Finally
mysqlconn.Close()
mysqlconn.Dispose()
End Try
Else
log.WriteToErrorLog("Could not open db connection", "", "MySQL Error")
mysqlconn.Dispose()
End If
End Using
End Sub
In this scenario, the SentMailTimeStamp routine in never reached.
If I remove the call then both messageboxes will be displayed as a result of both events.
Please let me know if there is any more detail you need.
Kind regards,
David
UPDATE Have modified the code as per Cadogi comments. Thank you.
UPDATE2
Have wrapped the call to the SentMailtimeStamp routing in a Try Catch and I do indeed have an error in the client.
Private Sub Application_ItemSend(ByVal oItem As Object, ByRef Cancel As Boolean) Handles Application.ItemSend
System.Windows.Forms.MessageBox.Show("Application.ItemSend Event")
log.WriteToErrorLog("Application ItemSend Reached", "", "Event")
Try
If TypeOf oItem Is Outlook.MailItem Then Call SentMailTimestamp(oItem)
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
System.Windows.Forms.MessageBox.Show("end of Itemsend Event")
End Sub
The error message is:
Could not load file or assembly 'MySQL.Data, version=6.8.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot fine the file specified
Awesome, if feel am making progress. I have assumed the published project would have everything required included.
Update 3 I have resolved this issue with thanks to Hans who pointed me to some very simple lessons in debugging - a hard lesson learnt as this has taken me a couple of weeks to resolve.
Hans absolutely spot on.
There is something obvious you are missing, Office programs are not that accommodating to add-ins that misbehave. By default any exception they throw in an "awkward" place is swallowed without a diagnostic. The only way to tell that this happened is that code you think should have a side-effect is just not doing its job.
The reason that the Addin works on the development machine and not the client is that the MySQL.Data reference needed the Copy Local property set to True.
For those who ever end up in a similar position to me the fix was to go to your project properties->References. Highlight MySQL.Data and change the Copy Local property to True.
Thank you all for contributing it has all helped with my learnings and helped narrow down the issue.
