Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-04-07
4
Medium Priority
?
563 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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 …

610 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