While reading through emails, sometimes there might be situation that you are not able to immediately react to the email and that you need to do some work offline before you can react. One way to handle that is to flag the email so that in future you come back and check the flagged email and take action. I have a better choice. We can have a Outlook VBA Macro which will flag a message, add reminder for the start date and assigns new start and End Date.

This is a very simple macro. This can very well be the first VBA macro. No worries, if you have not ever developed or used a macro .If you are not familiar with Outlook macro or do not know how start with it, Here is an article which will quickly on-board you on macro world : Getting started with Outlook Macro. If you do not need insight of the code and and the FORM and interested in downloading, please head on to relevant section

How Outlook VBA macro for setting reminder and due date work

  1. Select an email item in the main outlook screen or open an email in new window
  2. Run Macro from Developers tab by selecting the macro setDue. if you have already created a shortcut for the macro in the ribbon, you can run it directly from there. You may check Create shortcut for a macro, if do not know how to do it
  3. Type number of days from today when you want get reminded to start working on the topic
  4. Type the number of days it might take - a due date basically
  5. click on "Okay" and your email is flagged with reminder, Start date and a Due date
set-reminder.png

Working of Outlook VBA macro for setting reminder and due date

The small macro designed in two parts. The code that actually does the work and a VBA Form and associated event handler code that will capture the input from users. When the macro is called, it invokes the FORM

1. User form and Associated code

The FORM is created in the Visual Basic invoked from the Developer tab. It consists of few Frame, Labels, Text Boxes and Buttons. The form is created and you can import the FORM file DueDateForm.frm. This file is included in the zip file that you can download it from here. The associated code for the FORM can be seen by right-click and click on view code

Once you have imported the Form file in the Visual Basic, you can see the code. The form updates the date as and when you change the days count in the form. it also validates if this is number or not. You can see the following codes. Whenever there is a change in dueEnd or dueStart fields, a smalll block of codes, first validates the input and if those are acceptable, it will calculate the corresponding date and show up in the FORM itself.

Cancel and OK actions must hide the FORM, so that the remaining codes in the macro can start

due-form.png
Code behind User form
Private Sub dueCancel_Click()
    Me.Hide
    Me.dueCancel.cancel = True
End Sub

Private Sub dueEnd_Change()
    If IsNumeric(Me.dueEnd.Value) And IsNumeric(Me.dueStart.Value) Then
        DueDateForm.dueEndLabel.Caption = "( " & Date + CInt(Me.dueEnd.Value) + CInt(Me.dueStart.Value) & " )"
    End If
End Sub

Private Sub dueOK_Click()
    Me.Hide
End Sub

Private Sub dueStart_Change()
    If IsNumeric(Me.dueStart.Value) And IsNumeric(Me.dueEnd.Value) Then
        DueDateForm.dueStartLabel.Caption = "( " & Date + CInt(Me.dueStart.Value) & " )"
        DueDateForm.dueEndLabel.Caption = "( " & Date + CInt(Me.dueEnd.Value) + CInt(Me.dueStart.Value) & " )"
    End If
End Sub

2. Macro to set reminder, Start and Due date

Finally the Macro code part.

We have defined GetItem() function. This function returns the current email item as an object. This item will be worked subsequently for setting reminder

function getItem()
Function GetItem() As Object
    On Error Resume Next
    Dim App As Object
    Set App = CreateObject("Outlook.Application")
    
    Select Case TypeName(App.ActiveWindow)
        Case "Explorer"
            Set GetCurrentItem = App.ActiveExplorer.Selection.Item(1)
        Case "Inspector"
            Set GetCurrentItem = App.ActiveInspector.CurrentItem
        Case Else
            ' anything else will result in an error, which is
            ' why we have the error handler above
    End Select
    Set App = Nothing
End Function

Now lets focus on the core part of the code that is Public Sub setdue(). This is the sub procedure that you will define as shortcut in the ribbon for quick access. the first thing the Sub does is that it identifies the the outlook email Item that will be worked on by calling the GetItem function. If no email is selected it will give a pop-up informing and then exit. If it looks good, set initial value for date and show the FORM. the procedure will wait until OK or Cencel is pressed. If cancel pressed, it will exit the Sub procedure. If OK is pressed, it will first

  • Flag the email item
  • Set reminder to the day corresponding to the nth day from today. value of n is taken from the text box dueStart.
  • Same n is also used as start of activities
  • Set the due date to the date corresponding to mth day from the start date. value of m taken from the text box dueEnd
  • Exit after successful completion

The script follows

Public Sub procedure setdue
Public Sub setdue()
    Set objMsg = GetItem()
    If objMsg Is Nothing Then
        blah = MsgBox("Please select a mail item first", vbOKOnly)
        Exit Sub
    ElseIf TypeOf objMsg Is MailItem Then
        DueDateForm.dueStartLabel.Caption = "( " & Date & " )"
        DueDateForm.dueEndLabel.Caption = "( " & Date & " )"
        DueDateForm.Show
        If DueDateForm.dueCancel.cancel = True Then
            Unload DueDateForm
            Exit Sub
        End If
        
        If IsNumeric(DueDateForm.dueStart.Value) And IsNumeric(DueDateForm.dueEnd.Value) Then
            reminddays = CInt(DueDateForm.dueStart.Value)
            countdays = CInt(DueDateForm.dueEnd) + reminddays
        Else
            blah = MsgBox("Reminders must be numeric values", vbOKOnly)
            DueDateForm.Show
     
        End If

        With objMsg
            .MarkAsTask olMarkThisWeek
            ' sets a specific due date
            .FlagRequest = "Flagged"
            .TaskStartDate = Now + reminddays
            .TaskDueDate = Now + countdays
            .ReminderSet = True
            .ReminderTime = Now + reminddays
            .Save
        End With
    End If
End Sub

Finally, once again you need to set a shortcut to the setDue procedure. You may refer to Create shortcut for a macro.