At work I have to send several emails out each day. One of these is exactly the same each day with the exception of the date. I added a reminded in Outlook’s built in calender which reminds me every day at 4pm, but I wondered if I could make this even easier. Turns out that while there isn’t a built-in way (which is pretty sad since this is version 15 or something of Outlook), you can get it working with a little Visual Basic help.
After searching online, I found a walkthrough on Super User which works. The gist of the method is:
1.) You set a daily reminder in the calender
2.) Use the reminder form as an email form
3.) Have visual basic create an email and insert the Subject, Body, Address from the reminder.
4.) Have visual basic send the email.
I like this method, but I ran into issues because the recipients go into the reminder’s “Location” input box, and if you are sending to a lot of people, you run out of space. I realized though, that I could use part of the above method and integrate a template for a much better solution.
Step 1: Create Template
Start a new email and fill in all needed information (subject, body, recipients). If you have a footer saved in Outlook, which Outlook inserts into new emails, delete this from the email.
Click on File -> Save As
Give it whatever title you want, and then save it. Make sure though, that you save it as an Outlook Template (*.oft). For this example I called it “My Subject.oft”
Step 2: Create Reminder
Open the Outlook calender
Double clicking on a date.
Click on Recurrence in the ribbon.
Set the start time, set the duration to 0 minutes (End should be automatically changed to match Start), and then set the Recurrence pattern to Daily. I also set it to every weekday instead of every day, but you can do either.
Once you do, you should see it appearing in all days. You do need to do an extra step here of setting it to a category that your visual basic script can use to identify it. Any of the default categories will work, but I went ahead and created my own, using the same name was suggested in the Super User method: “Automated Email Sender”.
Step 3: Create Visual Basic Script
I only have two changes from the method I found, and the method I’m using here. The first is that instead of having my visual basic script start with a blank email, I have it start with the template we just created. The second change is that I remove the code which pulls in the information for the email from the Reminder form, because we no longer need it. Line three is where we call the template. This assumes that the template is saved to the default location. You’ll also need to make sure the title matches what you saved it as.
Private Sub Application_Reminder(ByVal Item As Object) Dim objMsg As MailItem Set objMsg = Application.CreateItemFromTemplate("C:UsersUserAppDataRoamingMicrosoftTemplatesMy Subject.oft") If Item.MessageClass "IPM.Appointment" Then Exit Sub End If If Item.Categories "Automated Email Sender" Then Exit Sub End If objMsg.Send Set objMsg = Nothing End Sub
This will automatically send out an email created from the template that you saved, whenever the reminder goes off. You can set your reminder to be daily, weekly, or only once.
Step 4: (Bonus) Autofill in the Date
I want to put today’s date in my email each time it goes out, specifically in the Subject line. Turns out there are no shortcodes you can use in the saved template. We have to add a new line to the Visual Basic script. Right before the line where we sent the email, we put in this:
objMsg.Subject = objMsg.Subject & " - " & WeekdayName(Weekday(Date)) & " " & Month(Date) & "-" & Day(Date) & "-" & Right(Year(Date), 2)
What this does is says the Subject is now equal to the Subject plus today’s date, in the format of
Weekday Month-Date-Year (Ex: Thursday 1-8-15). If you note, the year has an extra wrapper function. This is because
Year() is outputted as four digits, and I personally wanted only two.
These steps will get you a daily email set out automatically, based on your saved template, with the date automatically added each time. And you can send it to as many people as you could with a regular email because it’s just a template; there’s no using reminder input boxes for recipient input boxes.
Leave a Reply