VB.NET Visual Studio Outlook 2013 Addin Project - events appear not to be firing

Question

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.

Answer 1

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.

What you do about it is covered well, google "VSTO exception handling". One such hit is this MSDN article, I'll copy-paste the relevant section:

Visual Studio Tools for Office can write all errors that occur during startup to a log file or display each error in a message box. By default, these options are turned off for application-level projects. You can turn the options on by adding and setting environment variables. To display each error in a message box, set the VSTO_SUPPRESSDISPLAYALERTS variable to 0 (zero). You can suppress the messages by setting the variable to 1 (one). To write the errors to a log file, set the VSTO_LOGALERTS variable to 1 (one). Visual Studio Tools for Office creates the log file in the folder that contains the application manifest. The default name is .manifest.log. To stop logging errors, set the variable to 0 (zero)

Answer 2

Sorry, dont have enough points to leave a comment...

is it possible that your SentMailTimestamp() routine is saving info for KPI's on a part of the network that end users don't have acces to? I have seen applications fail without warning or an error because of this.

Another thing to check is if the SentMailTimestamp() should be using "Call" keyword at all as it can interfere with your results. e.g.

Private Sub Application_ItemSend(ByVal oItem As Object, ByRef Cancel As Boolean) Handles Application.ItemSend
MsgBox("Application.ItemSend Event")
SentMailTimestamp()
End Sub

Based on this: https://msdn.microsoft.com/en-us/library/dz1z94ha.aspx

From Above Link: "You can use the Call keyword when you call a Sub procedure, but that keyword isn't recommended for most uses. For more information, see Call Statement (Visual Basic)."

This is link it forwards to: https://msdn.microsoft.com/en-us/library/sxz296wz.aspx

Answer 3

Is your add-in enabled on the end user PCs?

The ItemAdd event of the Items class is not fired when a large number of items are added to the folder at once (more than 16). This is a known issue in Outlook.