PDA

View Full Version : Email Watcher Help For .display



hk129
08-18-2015, 07:39 AM
Hello,

I am trying to send out emails from Excel and I would like to use .display instead of .send. However, I would like to monitor if the user did follow through and hit send on the email. I searched for some code online and tried to implement it but I'm have trouble. Here is some of my code:

Sub Send_Mail(quote, email, contact)

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

If (Not Trim(email & vbNullString) = vbNullString) Then 'Check to see if there is an email address entered
If (Not Trim(contact & vbNullString) = vbNullString) Then 'If the contact cell is not empty
With OutMail
.To = email
.CC = ""
.BCC = ""
.Subject = "AUTOMATED MESSAGE"
.Body = "Hi " & contact & "," & vbCrLf & vbCrLf & "We are emailing you in regards to the quote number: " + ActiveCell.Text
.OriginatorDeliveryReportRequested = True
.Display
'.Send
'After sending the email, we want to unhighlight the cells and put the date in the appropriate column
ActiveCell.Offset(0, 4).Interior.ColorIndex = xlNone
ActiveCell.Offset(0, 5).Interior.ColorIndex = xlNone
ActiveCell.Offset(0, 6).Interior.ColorIndex = xlNone
ActiveCell.Offset(0, 6).Value = Date
ActiveCell.Offset(0, 6).Locked = "True"


End With
Set cw = New EmailWatcher
MsgBox "HERE1"
Set cw.BoolRange = Range("B6213")
MsgBox "HERE2"
Set cw.TheMail = OutMail
MsgBox "HERE3"

Else
MsgBox "Please enter a valid contact name for the quote: " & quote
End If
Else
MsgBox "Please enter a valid email address for the quote: " & quote
End If

Set OutMail = Nothing
Set OutApp = Nothing


End Sub






In a class module, I have the following:
Public BoolRange As Range
Public DateRange As Range
Public WithEvents TheMail As Outlook.MailItem


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


I used the msgbox HERE1,2,3 to help to monitor where the flow of logic is. I would get HERE2 but not HERE 3. Also, I am not able to see the msgbox "INSIDE FUNCTION". Can you please help me out.

Thanks in advance.