This is a small aside from our calculator project, but what something that I did recently and wanted to share. So today we are going to build a small automation macro to calculate build and send an email every week on a specific day.
Requirements – Send an email every Wednesday requesting the next Tuesday an Thursday as work from home days.
Assumptions – User is using Microsoft as email provider, outlook is installed on their computer.
For this we will use Excel and task scheduler. With this you may notice that our excel workbook is set up so that we can add more macros if needed.
Worksheets –
We will have two sheets for this example, but it would be possible if you had multiple days that specific macros should run to have more.
Main Sheet –
This is where we will add our first macro, with the date added, name of the macro, the reference sheet that it will be using, if it is active or not and then two response fields for the day it was ran and if it was successful.
Weekly_Wens –
This sheet will have every Wednesday for the next few weeks on it. The simple formula ‘ =(A2 + 7) ‘ can be used to get every Wednesday from a specified starting Wednesday (A2). Then use the green corner to drag down how ever many dates we need to quickly calculate our run dates.
If we plan on sending the automatic email past 9/8 our sheet will need to be updated. However we could just add a second response sub to add another date every time our macro ran. For now we will not however just so we can keep an eye on things.
Back end set up –
To access the VBA code editor you can ether use the developer tab at the top menu or use the short cut key Alt+F11 since we don’t have any buttons to directly take us there this time. For future organization we are going to create two new modules, the first ‘ Functions ‘ will hold all of our reused functions, the second ‘ Subs ‘ will hold specific macros.
Now we will start building our functions.
We will need a LastRow function to return the last row in a given range, this function will take the column number we want to count, the worksheet we are using, and the minimum row that should be present. It will then return as a data type long the number of the last row on the specified worksheet. This will allow us to loop easily through all of the cells in a vertical range.
Next we will need a function that will set the configuration for our email and send it. This will take the email subject, message, and recipients as strings.
The hard part is out of the way and we can move over to checks and balances. A few things to keep in mind, we only want the email to be sent if the corresponding macro is 1. Active, and 2. If today is the correct day for it to be sent. Within our WFH_Email Sub we will build this out as our checks.
Variables needed
Check is active and is it scheduled today? We are using the built in function of Now() combined with the Format() function to get today’s date, and the pulling from the main sheet if the macro is active or not. Within the active loop we will use the LastRow function to get the last row of the Wednesday scheduled sheet, then loop through each cell to see if today matches one of those dates. If there is a match then we will set the rundate variable to double check, and exit the loop, if there are not matches then worksheet will be updated, and we will exit the sub in the rundate check loop.
The runDate checkloop will get our weekof date, and our first and second requested WFH dates to be populated into the email subject and body strings. The subject and body strings will then be passed into the CDO_Mail function call that we built earlier along with our recipients. Once that is complete the main worksheet will be updated with the date it ran and advise that it was successful in sending. Because we set the configuration of the email to HTML, we can use basic HTML/CSS to build out the body layout and style of the email, be careful here and make sure to always test before you send the live email however as one wrong character in the HTML here and all of it will be exposed.
We have a macro with possibly too many controls and double checks in place, but to make it even more automated we have to have a trigger of some sort. Still from Excel we want to edit the on open command so that this macro will run every time it is opened.
** A note here, with this automatic call on open in order to open the workbook again without running the macro ensure you do it from within a blank Excel file, using the browse function while holding shift.
With the macro and workbook set we can set a new task in task schedular to simply open the excel workbook every Wednesday.
Make sure to replace the program/script with the executable path for excel, and the argument should be the complete file path for the macro workbook.
And that’s that! We have successfully made an automatic email that will be sent every week on Wednesday requesting the following Tuesday and Thursday as WFH days. This is a simple application, but I hope you can see how it can cut out and help to automate something in your daily workflow.
