To check if email was Sent or not sent and then do set of commands.

[subbby]

Has been cross posted on https://www.excelforum.com/excel-pr...-or-not-sent-and-then-do-set-of-commands.html

hello

Background :

The following code sends an reminder email. The code is designed to display the email (not automatically send)
Based on what the user chooses ("Send" or "Close the display") ; I want the code to do few things.

Code is

'adding SEND REMINDER
'============================================

If Target.Column = 11 And Target.Value = Worksheets("Reminder Flags").Range("B5") Then


Dim todaysdate1 As String '## capture the user input'
Dim myDate1 As Date '## the date you want to add to'
Dim bufferdays1 As Double
todaysdate1 = Date

bufferdays1 = 14
myDate1 = CDate(todaysdate)
Dim dateaddition1 As Date
dateaddition1 = DateAdd("d", bufferdays1, myDate1)

Dim oout15 As Object
Dim omsg15 As Object
Set oout15 = CreateObject("Outlook.Application")
Set omsg15 = oout15.CreateItem(0)

todaysdate1 = Date
bufferdays1 = 14
myDate1 = CDate(todaysdate1)

'Dim dateaddition As Date
'dateaddition = DateAdd("d", bufferdays, myDate)

With omsg15
.To = ""
.Subject = "DO NOT DELETE THIS EMAIL + Reminder to delete FTP Folder "
.Body = "Delete FTP Folder Package # " & Worksheets("Link").Range("A3")
.Importance = 2
.FlagRequest = "DELETE FTP FOLDER"
.FlagStatus = 2 'olFlagMarked
' .FlagStatus = olFlagMarked
' .FlagIcon = olRedFlagIcon
.ReminderTime = dateaddition1 & " 14:30"
' .FlagIcon = olYellowFlagIcon
.ReminderSet = True
.FlagDueBy = dateaddition1
.ReminderSet = True
.Display



End With
On Error Resume Next

If omsg15.Sent Then
MsgBox ("Reminder e-mail has been sucessfully sent")
Target.Value = "Reminder Sent"
ActiveCell.Interior.ColorIndex = 4
ActiveCell.Font.Color = RGB(0, 0, 0)
ActiveWorkbook.Save
Else: MsgBox (" E-Mail not sent. Task will be reset ")
Target.Value = Worksheets("Reminder Flags").Range("B4")
'Worksheets("Email_List").Range("A2:G50") = ""
ActiveWorkbook.Save
End If ' olEmail.sent
Set omsg15 = Nothing

End If 'Target.Column = 11 And Target.Value = Worksheets("Reminder Flags").Range("B5")

'===========================================================================

Problem

• When I send the email after putting recipients email, it follows whats in "ELSE" in the code
• When I close the display, it still does the same...

What I tried ?

I tried to put the "If omsg15.Sent Then.... End If ' olEmail.sent" inside the "With...End With". It didn't work as it should. It closed the email and did whats in Green in the code

 

[Deepak]

Hi,

Declare mail item object to public & then check.

Public omsg15 As Object
or
Public omsg15 As MailItem

 

[subbby]

Hi,

Declare mail item object to public & then check.

Public omsg15 As Object
or
Public omsg15 As MailItem

with

Public omsg15 as Object

i got this error : "Invalid Attribute in Sub or Function

same for

Public omsg15 As MailItem

 

[Deepak]

It should be on the top not inside the subroutine

Like this.

 

[subbby]

tried that too..code does what I described in my initial problem statement