Outlook, reminder before sending email

Reminder problem

This post will explain how to use VBA to get a reminder each time you try to send email with specific words in its subject. Let's say you have a mail with a subject "New products". You send this email every month. Someone informs you that, the next time when you send this email, you have to add "newmanager@company.com" to a list of recipients. Usually you would just hit "Replay All" button on the old email, make some changes, and then you would send newly made email. But, it is very hard to recall, in that specific moment, to add the new manager to recipient list. It would be great to get message like this that would remind us of a needed change.

VBA procedure

We can use VBA procedure to reminds us to make a change. In the sample files, at the end of this blog post, you will find such VBA procedure. This procedure has to be placed in the Outlook VBA environment in ThisOutlookSession. This procedure has hardcoded array that looks like this:

Array(Array("New", "products"), "Add newmanager@company.com to recipients. Click Cancel button to cancel email.")

What this array says is to look for an email that has both "New" and "products" words in its subject. Array could have as many searched words as we want. We will change this procedure by placing our searched terms in it. This is how we say to VBA procedure what are we looking for. After this first step, don't close your Outlook, we have to solve certification problem.

Certification problem

Outlook should always have macro security turned on. This, unfortunately means that our macro will be either disabled or we will get notifications each time we open Outlook. The only way out is to acquire a digital certificate. You can buy such certificate or you can create your own with "SelfCert.exe" program. It is not common to buy your own digital certificate so most people will use the one made with "SelfCert.exe". This is small program which is included in every Windows. Certificate created with it will be only valid for computer on which that certificate is created. This is enough to make our reminder VBA procedure fully operational.

We can find "SelfCert.exe" (1) in the same folder where Excel and Outlook are placed (2,3). When we find this program, we click on it and the window (3) is opened. Here, we have to give our certificate a name. After this, confirmation message is shown (4). This is how we create certificate.

Now we have to implement our certificate in Outlook. We go to VBA environment, and in Tools menu we choose "Digital Signature" option (1). New dialog will open. In it, we click on "Choose" button (2). Now, we will be offered with our previously created certificate and we will accept it (3). We return to dialog (4) again where we click on OK button.

Before closing Outlook, make sure that your macro security level is set to "Notifications for digitally signed macros, all other macros disabled".

After this, our VBA procedure is placed in correct place and it is certified. We can close Outlook now. We will get a confirmation dialog to save VBA project which we will confirm.

Sending an email

Open Outlook. Before fully open, Outlook will show us a message (1). On this message we have to click on "Trust all documents from this publisher". We have to do this only once.

Only now, can we try our VBA procedure. When we try to send email (1) with subject that has words "New" and "products", instead we will get a message box (2) with notification "Add newmanager@company.com to recipients. Click Cancel button to cancel email.".

If you click on Cancel (3), message box will close but email will not be send. Now, we can change recipient list. We can add "newmanager@company.com" to our mail (1).

After our mail is corrected, we will delete our Array from VBA procedure. Then we can send our email normally.

We can have several such arrays. Each array is a trap waiting for your email to be send. First trap to catch your email will decide what message to show to the user. If no traps catch something, that email will be send without notification.

Array( Array("New", "products"), "Add newmanager@company.com to recipients. Click Cancel button to cancel email.") _
, Array( Array("New", "and", "old"), "Add newemployee@company.com to recipients. Click Cancel button to cancel email.")

Problem with creating certificate

Sometimes we will have problem to create our certificate with "SelfCert.exe" program. We can try to delete our old certificates. Go to Run > mmc (1). In new dialog, click on File > Add/Remove Snap-in (2).

Sub dialog will then open. Select "Certificates" (3) and add them (4,5) into pane (6). Now, close sub dialog and return to the main dialog. There, click on "Certificates – Current User" (7), then on "Personal" (8), and finally on "Certificates" (9). Here, you will see your certificate and you can delete it (10).