Last Update: 2017 - 11 - 14
How to send Outlook Emails with attached Flag and Reminder in VBA
by Philipp Stiefel, originally published June 10th, 2017
This week I received an interesting inquiry from a reader of the Access & Email section on my website. He asked whether it is possible to use VBA in Access to automatically send an email with an attached reminder for the recipient.
This is a very interesting idea. I use Outlook intensively to manage my tasks and frequently I add a follow-up-flag and a reminder to emails in by inbox to make sure I act upon them within due time if required.
A predefined follow-up-reminder already set by the sender of an email would indeed be helpful to not miss any important task. - On the other hand, if someone I hardly know sent me an email with an attached reminder, I would be seriously annoyed.
Obviously, this whole question rang with me and I invested some time to come up with a possible solution to this.
The general functioning of Flags and Reminders for Email
It is easy to add a reminder manually to an Outlook email. Either for yourself or for the recipient of an Email. To do this, click the Add Reminder item from the Follow-Up dropdown in the email windows ribbon.
If you closely examine this screenshot, it should become obvious that it is possible so set the Follow-Up with or without the reminder. So the reminder is essentially a different thing than the Follow-Up-Flag.
Side note: I refer to this Flag as the Follow-Up-Flag, which is not entirely correct. Basically it is just a flag and can have multiple different values other than “Follow up”, like “Review”, “Do not forward”, “Reply” “For your information” and several others. But I have hardly seen any other value than “Follow-up” ever being used, so I will continue to use the term “Follow-up-Flag”.)
As it is often the case, the Outlook documentation is lacking detail in regard to specifying in which exact scenarios these options will work.
Follow-Up-Flag itself is translated to an X-Message-Flag email header. This header’s value is the type of flag like “Follow up” or “Review”. Headers starting with X- are application defined headers and it’s up to each individual email client to decide how to interpret them, if at all. The optionally added reminder date will be transmitted in a Reply-By email header as mentioned in section 2.1.51 of RFS 4021. Microsoft Email applications will interpret the Reply-By as an additional info to the X-Message-Flag.
With this information in the email headers, it is fairly safe to assume they will reach the recipient of the email message. However, the recipient himself will not see any of this unless his email client does display it. And this is in fact a weakness of this approach.
My own tests suggest that Follow-Up-Flags are displayed by all of Microsoft’s email clients like Microsoft Outlook, Mail on Windows 8 and 10 and even the Outlook web application. But not all of them display the date connected to the flag. With other email clients you cannot be sure this is supported and the recipients will see your Follow-up-Flag at all.
Only Microsoft Outlook is able to display a Reminder-Pop-Up at the reminder time from the email. But it will do so only if it is connected to a Microsoft Exchange Server and the Email was sent and received locally on the very same server. If there was any external transport required for the email, the follow-up time will be displayed in the email details but there will no reminder created for the email.
The insights so far are, Follow-Up-Flags can be used for a range of scenarios and recipients while Reminders will only be transmitted if you use Exchange Server and only to recipients inside your organisation.
The Follow-Up-Task and Reminder you set for yourself is of course completely independent of the recipient and will always work the way you are used to in Outlook.
Sending an Email with Flag and Reminder with VBA
Now we established how and when to use these settings for emails sent manually. But the essence of my writings about Access/VBA and Email is sending emails right from any VBA-enabled application without further interactions required from the user in the email client. Thus we need to figure out how to add these reminders automatically using VBA code.
Several properties of the Outlook.MailItem-Object jump right to my attention when viewing the MailItem class in the VBA Object Browser. The most promising is a set of properties starting with the term Flag….
Pay attention to all the Flag-Properties except FlagRequest being greyed out. These are hidden properties and you will not see them in Object Browser unless you enable the Show Hidden Members option form the context menu.
These greyed out properties are not mentioned in the VBA documentation for the current versions of Outlook anymore and the documentation of the corresponding Microsoft.Office.Interop.Outlook library for .Net states: “This object, member, or enumeration is deprecated and is not intended to be used in your code.”
Despite thorough research I could not find any other properties or methods that would work as a replacement. Fortunately the deprecated FlagDueBy property still works.
Even though it is not recommend, we have no choice than to use this deprecated property. But keep in mind that this property might get removed in a future version of Microsoft Outlook.
Here is a little example how to send an email with the Follow-Up-Flag and a reminder for the recipient in two days from now:
Public Sub sendMailWithFollowUpReminder() Dim myMail As Outlook.MailItem Dim myOutlApp As Outlook.Application Set myOutlApp = New Outlook.Application Set myMail = myOutlApp.CreateItem(olMailItem) With myMail .To = "'email@example.com'" .Subject = "Here is an email with Flag and Reminder sent by Outlook-Automation" .Body = "Pay attention to the Follow-Up flag and the due date in the info bar." ' We set the Follow up Flag .FlagRequest = "Follow up" ' We set the due date for the reminder two days from today .FlagDueBy = DateAdd("d", 2, Date) .Send End With Set myMail = Nothing Set myOutlApp = Nothing End Sub
Before you ask: The FlagStatus property has no effect on outgoing email and the FlagIcon property has no effect at all anymore. It was replaced by Outlook’s category system that can be used to color-code emails in your local mailbox.
Set up tasks and reminders for sent emails with VBA
We learned how send emails with reminders for the recipient of an email. Having the recipient of an email being reminded to follow-up is nice. But what about the other side of the communication link? If the sender of the email is responsible for the job being completed on time, it makes sense to create a reminder for him as well.
I still find it hard to believe there is no easy way to achieve that, but adding a reminder for the sender of an email with VBA seems not to be as easy as doing the same manually. All the properties of the MailItem that are relevant for the Flags and Reminders either cannot be set or have no effect on the copy of the sent email stored in your SentItems-Folder.
The workaround I’m going to show here works, but I think it is fairly complex for seemingly simple task.
I created a class module with a public method to register any MailItem to get a reminder after it is actually sent. That method extracts the SentItems-Folder from the MailItem and handles its ItemAdd-Event. This event is fired when a new item is added to an Outlook folder.
The event handling code requires a reference to the Outlook-Object-Library in your VBA-Project. Event handling will not work with Late Binding. (If you unfamiliar with the concept of events and event handling, you might like to read my introduction to event handling in VBA.)
The workaround with handling the event is necessary because only after the email is finally sent, we can add the reminder and mark the item as a task. To do this we use the MarkAsTask-Method of the MailItem and the ReminderSet- /ReminderTime-Properties. - Other than the Flag…-properties these are not deprecated so I rather use them for the local email.
We add a UserPropery to the MailItems registered for the reminders to be able to distinguish the VBA-created emails from other emails that are sent while our event handler for the ItemAdd-Event is in place.
I named this class module FollowupHandler. Here it is:
Option Compare Database Option Explicit Private Const USERPROP_FOLLOWUP_DATE As String = "AddFollowUpDate" Private WithEvents m_SentItems As Outlook.Items Public Sub RegisterMailForFollowup(ByRef mail As Outlook.MailItem, ByVal FollowUpDate As Date) If m_SentItems Is Nothing Then Set m_SentItems = mail.SaveSentMessageFolder.Items End If mail.UserProperties.Add USERPROP_FOLLOWUP_DATE, olDateTime, False mail.UserProperties(USERPROP_FOLLOWUP_DATE).Value = FollowUpDate End Sub Private Sub m_SentItems_ItemAdd(ByVal Item As Object) Dim up As UserProperty With Item Set up = .UserProperties.Find(USERPROP_FOLLOWUP_DATE) If Not up Is Nothing Then .MarkAsTask olMarkNoDate .TaskDueDate = up.Value .ReminderSet = True .ReminderTime = up.Value .Save End If End With End Sub Private Sub Class_Terminate() Set m_SentItems = Nothing End Sub
We need a little helper property-method to easily create an instance of this class. The property EmailFollowupHandler should be in a general module for the m_EmailFollowupHandler to be independent of any open form in Access.
Option Compare Database Option Explicit Private m_EmailFollowupHandler As FollowupHandler Public Property Get EmailFollowupHandler() As FollowupHandler If m_EmailFollowupHandler Is Nothing Then Set m_EmailFollowupHandler = New FollowupHandler End If Set EmailFollowupHandler = m_EmailFollowupHandler End Property
The whole magic will only work as long as the instance of the FollowUpHandler in the private variable m_EmailFollowupHandler is set. If your project is reset by an unhandled error or by you stopping the execution, emails being sent will be no longer marked as tasks.
Now with the above code in place, we only need to add a single line of code to the method generating and sending the emails.
Public Sub sendMailWithFollowUpReminder()
Dim myMail As Outlook.MailItem Dim myOutlApp As Outlook.Application Set myOutlApp = New Outlook.Application Set myMail = myOutlApp.CreateItem(olMailItem) With myMail .To = "'firstname.lastname@example.org'" .Subject = "Here is an email with Flag and Reminder sent by Outlook-Automation" .Body = "Pay attention to the Follow-Up flag and the due date in the info bar." ' We set the Follow up Flag .FlagRequest = "Follow up" ' We set the due date for the reminder two days from today .FlagDueBy = DateAdd("d", 2, Date)EmailFollowupHandler.RegisterMailForFollowup myMail, DateAdd("d", 2, Date)
.Send End With Set myMail = Nothing Set myOutlApp = NothingEnd Sub
So, if you are using Outlook Automation already to send emails from your application, you can easily extend the existing functionality by copying the class module and the helper property to your project. In the actual code sending your emails, you just add the single line to register your email to get a local Reminder and Task.
Adding Follow-Up information for the recipient of an email and creating a task for the sender can improve the workflow connected to automated emails significantly. The code in this article demonstrates how to achieve this functionality with VBA code.
Always keep in mind that the recipient of an email with attached Follow-Up-Flag might not see this information at all if his email client does not support it or he might get annoyed if he was not expecting this.
Sample code download
For your convenience here is my sample database with the email-follow-up-code from this article.
© 1999 - 2017 by Philipp Stiefel