Set Reminder after e-mail is sent

Question

Is there a way to set a reminder after sending e-mail?

I have VBA code that creates an e-mail and sets a reminder.

Sub RendaFixaAplicação()

Dim texto As String

Dim OutApp As Object
Dim OutMail As Object

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

texto = Range("J2").Value & ",insert some text here"

With OutMail
    .Display
    .to = Range("J3").Value
    .CC = Range("J4").Value
    .Subject = "Insert a subject here " & Range("E2").Value
    .HTMLBody = texto & .HTMLBody
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Call alerta1

End Sub

The reminder is made with the "Call alerta1" at the end of the code and is programmed like this:

Sub alerta1()
Dim objOutlookApp As Outlook.Application
Dim objTask As Outlook.TaskItem
Dim hora As String
Dim wd As WorksheetFunction
Set wd = Application.WorksheetFunction
Dim diautil As Date
diautil = wd.WorkDay(Date, 1)

If Time > "15:00:00" Then
    hora = diautil & " 14:00:00"
Else
    If Time < "14:00:00" Then
        hora = Date & " 14:00:00"
    Else
        hora = Date & " 14:45:00"
    End If
End If

Set objOutlookApp = CreateObject("Outlook.Application")
Set objTask = objOutlookApp.CreateItem(olTaskItem)
objTask.Subject = "Insert a Subject Here - " & Range("E2").Value
objTask.Display

objTask.body = "Cliente: " & Range("K2").Value & vbNewLine & "Email cliente: " & Range("J3").Value
objTask.ReminderSet = True
objTask.ReminderTime = hora
objTask.DueDate = hora
objTask.Close (olSave)

End Sub

It sets the reminder whether I send the e-mail or not.

I can't use .Send on the e-mail because there needs to be some editing in the e-mail.

Is there a way to make Excel wait for the e-mail to be sent then run the "alerta1"?

check out the [MailItem.Send](https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/mailitem-send-event-outlook) event. You can then set your alarm to only be created when an email is sent — Marcucciboy2, Jun 13 '18 at 17:00
You could move the reminder code into Outlook in an ItemAdd event for the SentItems folder https://stackoverflow.com/questions/11263483/how-do-i-trigger-a-macro-to-run-after-a-new-mail-is-received-in-outlook. I suggest you could set up a specific subject, text, userproperty to indicate the email to be processed. You could try to keep all the code in Excel like this https://stackoverflow.com/questions/28610291/how-to-trap-outlook-events-from-excel-application. — niton, Jun 13 '18 at 21:51

score 0 · Answer 1 · answered Jun 17 '18 at 04:49

First of all, there is no need to create a new Outlook Application instance in each method.

Ideally, you could hook up the ItemAdd event on the Sent Items folder in Outlook. But items may be deleted after sending without being saved to the Sent Items folder. The DeleteAfterSubmit property allows to set a Boolean value that is True if a copy of the mail message is not saved upon being sent, and False if a copy is saved in Sent Items folder.

So, I'd suggest handling the ItemSend event of the Application class which is fired whenever an Microsoft Outlook item is sent, either by the user through an Inspector (before the inspector is closed, but after the user clicks the Send button) or when the Send method for an Outlook item, such as MailItem , is used in a program. In the event handler you will get access to the item being sent and can create a new task for it.

Set Reminder after e-mail is sent

1 Answers1