Automated recurring email with Outlook (Office 365) without admin rights
Assume you meet with your friends on every first Wednesday in each month to play card games. And you want to send them an email reminder on the Monday before. For a similar purpose, I tried to set-up an automated recurring email with Outlook in Office 365. I had already sent automated emails easily from my private Ubuntu server. But the task was difficult in my office environment because of two issues. Firstly, Outlook has no ready available function for automated emails. And secondly, I am just ordinary user and have no admin authorization.
I tried first the method described in the Microsoft documentation. This is designing a custom form with a VBA script in conjuction with a recurring Outlook task. But this method cannot be applied without an admin account, because running custom form scripts are since 2017 desabled by default and it requires setting registry keys to re-enable it. I also tried to realize the automated email with Microsoft Flow, but could not find a suitable template for that.
The path to success is a method which I found in a tutorial of ExtendOffice.com. In the first step, a recurring appointment with reminder is created in the Outlook calendar. For this appointent, a new category must be used. I have named the category „Recurring email“. Finally, a short VBA script is created for the standard object „ThisOutlookSession“. Scripts for this object run automatically when Outlook starts. The script detects when the reminder for an appointment of the category „Recurring email“ is closed, composes then an email with the appointment title as email subject, the text string in appointment location as email recipients, and the appointment details as email body, and finally displays (VBA command: .display), or sends (VBA command: .send) this email.
It should be noted that VBA scripts are blocked in the default security settings of Office 365 unless the code is authorized by a self-signed digital certifcate.
Result and limitations:
When the reminder for the appointment pops up and is quit by hitting „dismiss“ or „dismiss all“, the email is either created and displayed for further edits ,or directly sent. But because the VBA script is running on the local device (e.g. a desktop computer), it works only if the local device is powered on with Outlook running. Otherwise, the sending is delayed until Outlook on the device has been started again, the missed appointment displays the reminder pop-up and is quit. It is not sufficient to access Outlook on a synchronized mobile phone or through a web interface, because the necessary VBA script is only available on the local device. I found no way to run the script on the Office 365 server, which would allow full automation without the need to have a local application running and to manually quit a reminder to trigger the email. Another issue is the limitation of the length for the appointment location entry to 255 characters, which is not sufficient for the „To:“ line in case of multiple email addresses. I could resolve this by using group email addresses, another option would be to modify the VBA script and record the email recipients in a structured way in the appointment details section. The latter would also allow emails with more attributes, such as different entries for „To:“, „Cc:“ and „Bcc:“. But despite of the above mentioned limitations, I found the current solution for automated recurring emails in Outlook helpful and have it in use.
The example mentioned at the beginning of this article is a little tricky. The card game evening is on the first Wednesday in each month, and the reminder email should be sent on the Monday before. But this is not necessarily the first Monday of the month. If the new month starts on a Tuesday or Wednesday, then the Monday before the first Wednesday is the 4th or 5th Monday of the previous month. Nevertheless, this can be realized with Outlook. The recurring appointment is scheduled for every first Wednesday in each month, and the reminder is set to 2 days prior to this appointment. Since the quitting of the reminder triggers the email, and not the appointment itself, the email will be sent on the correct day, even it is in the previous month.