Solved

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

Posted on 2014-04-07
4
533 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

948 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

22 Experts available now in Live!

Get 1:1 Help Now