Solved

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

Posted on 2014-04-07
4
527 Views
Last Modified: 2014-04-13
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
Comment
Question by:murbro
4 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39985557
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39985611
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
 

Author Closing Comment

by:murbro
ID: 39997322
Thanks very much
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now