?
Solved

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

Posted on 2014-04-07
4
Medium Priority
?
550 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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:Murray Brown
ID: 39997322
Thanks very much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

801 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