• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

VB.net Excel Add-In Make sure an email is sent

Hi
I am using the following VB.net code in my Excel Add-in to make sure that the Send
button is clicked on an email that is displayed but nothing seems to happen

             oMsg = oOutApp.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem)

                    oMsg.Subject = "Tumbling Tigerz Invoice"
                    oMsg.Body = oBody

                    ' TODO: Replace with a valid e-mail address.
                    oMsg.To = oEmailAddress
                    oMsg.CC = ""

                    ' Add an attachment
                    Dim oAttachment As String = oInvoicePDF
                    Dim sSource As String = oAttachment 'eg "C:\Temp\Hello.txt"

                    'Dim sBodyLen As String = oMsg.Body.Length
                    Dim sBodyLen As Integer = oBody.Length

                    Dim oAttachs As Microsoft.Office.Interop.Outlook.Attachments
                    Dim oAttach As Microsoft.Office.Interop.Outlook.Attachment
                    'only attach if there is something there
                    If oAttachment <> "" And oAttachment <> Nothing Then
                        oAttachs = oMsg.Attachments
                        If oAttachment <> Nothing And oAttachment <> "" And System.IO.File.Exists(oAttachment) = True Then
                            oAttach = oAttachs.Add(sSource, , sBodyLen + 1)
                            'oAttach = oAttachs.Add(sSource, , sBodyLen + 1, sDisplayName)
                        End If
                    End If
                    ' Send Email
                    If Globals.Ribbons.Ribbon1.chkCheckEmail.Checked = True Then
                        'Manual
                        oMsg.Display()
                        'Note no cleanup code

                        'Now monitor whether Send Button was actually pushed
                        Dim CurrWatcher As Object
                        CurrWatcher = New EmailWatcher
                        CurrWatcher.BoolRange = Globals.ThisAddIn.Application.Cells(2 16)
                        CurrWatcher.TheMail = oMsg
                    Else
                        oMsg.Send()
                    End If

Open in new window



Public Class EmailWatcher

    Public BoolRange As Excel.Range
    Public DateRange As Excel.Range
    Public WithEvents TheMail As Microsoft.Office.Interop.Outlook.MailItem


    Private Sub TheMail_Send(Cancel As Boolean)
        If Not BoolRange Is Nothing Then
            BoolRange.Value = True
        End If
        If Not DateRange Is Nothing Then
            DateRange.Value = Now()
        End If
    End Sub

End Class

Open in new window

0
Murray Brown
Asked:
Murray Brown
1 Solution
 
regmigrantCommented:
I don't pretend to be an expert on your code, but if this is a reasonable summary then what do you expect to happen next?

 If Globals.Ribbons.Ribbon1.chkCheckEmail.Checked Then
   - display a message
   - set some values in Current Watcher
Else
  - Send mail
Endif

Presumably the message states that chkCheckEmail needs to be unchecked in order to send the mail (ie: the Else clause)  - In which case you need to re-check it after the message is displayed  and I can't see where you have that.
0
 
CodeCruiserCommented:
You need to subscribe your method

Private Sub TheMail_Send(Cancel As Boolean)

to the send event of the message using AddHandler.

Try changing your class to following

Public Class EmailWatcher

    Public BoolRange As Excel.Range
    Public DateRange As Excel.Range
    Private WithEvents _TheMail As Microsoft.Office.Interop.Outlook.MailItem

    Public Property TheMail As Microsoft.Office.Interop.Outlook.MailItem
    Get
          Return _TheMail
    End Get
    Set (value as Microsoft.Office.Interop.Outlook.MailItem)
         _TheMail = value
         AddHandler _TheMail.Send, AddressOf TheMail_Send
    End Set

    Private Sub TheMail_Send(Cancel As Boolean)
        If Not BoolRange Is Nothing Then
            BoolRange.Value = True
        End If
        If Not DateRange Is Nothing Then
            DateRange.Value = Now()
        End If
    End Sub
End Class

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now