Today's entry in the Fun with Programming series is a macro to send a message every day when you first open Outlook. The user who asked for it wanted to send an email letting friends know he was still alive... because checking email is a good way to prove you are still alive and kicking. You can giggle over the reason a user asked for the macro, but there are many other reasons why someone might want to send a message every day or when you restart Outlook.

While you can send a message using a simple Application_Startup macro, if Outlook crashes or you need to restart Outlook for another reason, the macro will run and send a message, which may not be desirable, especially if you need restart Outlook multiple times.

The solution: write the time the last message was sent and only send a new message if it's the next day. The date (or date and time) is stored in the registry in decimal format so it's easy to compare the values.

With a couple of tweaks, the macro can send the message only if a specific number of days or hours passed since the last message was sent.

Note that this macro only runs when you start Outlook. If you never close outlook, it will not run.

If you need to send mail on a schedule whether or not Outlook was restarted, you can use a reminder to trigger the send. See Send an Email When a Reminder Fires for the instructions and macro to send an email when a reminder fires. Because this macro sets and checks the last send time, you can use both the startup and reminder macros to trigger the SendMailonOpen macro, so you are covered if you rarely close Outlook.

Or, you can add a button to the ribbon and send the message when you click the button.

This macro goes into ThisOutlookSession.

Send Message Macro

Private Sub Application_Startup()
   Call SendMailonOpen
End Sub

Sub SendMailonOpen()
Dim regDate As String
Dim Registry As Object
  
' get today's full date as a decimal.
  todayDate = CDec(Date)

' to use partial day
'  todayDate = CDec(Now)

Debug.Print "todayDate", todayDate
 
On Error Resume Next
Set Registry = CreateObject("WScript.Shell")

strLastSent = Registry.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\LastNotDeadSent")

Debug.Print "strLastSent", strLastSent

' Compare to the date in the registry
If todayDate > strLastSent Then

' to skip days add
 If todayDate > strLastSent + 1 Then

' send if 12 hours passed since last message sent
' If todayDate > strLastSent + 0.5 Then

Call CreateNewMessage

'Debug.Print "Updating!"
    Registry.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\LastNotDeadSent", todayDate
'Else
'   Debug.Print "Not Updating!"
End If
End Sub

Public Sub CreateNewMessage()
Dim objMsg As MailItem

' Use a new message form
Set objMsg = Application.CreateItem(olMailItem)

 With objMsg
  .To = "me@domain.com"
  .CC = "metoo@domain2.com"
  '.BCC = "Alias3@domain.com"
' Edit the text in these two fields as needed
  .Subject = "Hey, I'm still alive on " & Date
  .Body = "Just letting you know I'm not dead (yet). "

' Display it and send manually
 .Display
' Or send automatically.
'  .Send
End With

Set objMsg = Nothing
End Sub

Send on a Specific Day of the Week

If you only want to send on a specific day of the week, use Weekday function to get the day of the week. Add it to an If statement.

Sub SendMailonOpen()
Dim regDate As String
Dim Registry As Object
  
' get today's full date as a decimal.
  todayDate = CDec(Date)
  
  ' Saturday = 7
If Weekday(todayDate) = 7 Then

' code to check and update the registry
' goes here

End If
End Sub

Use a Template

If you need to send a longer message, you can use a template instead of a new message form. The address fields and subject field can be in the template, along with the message, so you wouldn't need to add those fields in the macro. Just open and send it.

Public Sub CreateNewMessage()
Dim objMsg As MailItem

' Use a template  
'Set objMsg = Application.CreateItemFromTemplate("C:\path\to\test-rule.oft")

objMsg. Send

Set objMsg = Nothing
End Sub

Manually compose Message

If you want to send the message manually, you only need this part of the macro. If you are not using it as part of the automatic macro, it can go into a Module.

Add a button to the Quick Access Toolbar or ribbon and click it to run.

Public Sub CreateNewMessage()
Dim objMsg As MailItem

' Use a new message form
Set objMsg = Application.CreateItem(olMailItem)

 With objMsg
  .To = "me@domain.com"
  .CC = "metoo@domain2.com"
  '.BCC = "Alias3@domain.com"
' Edit the text in these two fields as needed
  .Subject = "Hey, I'm still alive on " & Date
  .Body = "Just letting you know I'm not dead (yet). "
 ' Display it and send manually
 .Display
' Or send automatically.
'  .Send
End With

Set objMsg = Nothing
End Sub

How to use the macro on this page

First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.

To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Macros that run when Outlook starts or automatically need to be in ThisOutlookSession, all other macros should be put in a module, but most will also work if placed in ThisOutlookSession. (It's generally recommended to keep only the automatic macros in ThisOutlookSession and use modules for all other macros.) The instructions are below.

The macros on this page need to go into ThisOutlookSession.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the macro code in ThisOutlookSession:

1. Expand Project1 and double click on ThisOutlookSession.
2. Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)

More information as well as screenshots are at How to use the VBA Editor