Run your VBA Program Daily & Automatically!

My Client has a Query!

He wants to receive an email as a notification just before a 15 days before his employee's vacation period starts.

here is his explanation in his words : "I am thinking about a script that opens the spreadsheet once a day iterate through all the employees (about 20) then check if today's date is equal to the day that each employee will complete one year minus 15 days, and, if the result is true, send the notification. But I don't know how to implement that with code."

Here is the solution how to make this automation happened.

1. Create a workbook

2. Add Worksheet

3. Name it as Employee Manager

4. Create a Table and enter the Employees Details in line by line

5. Create a VB Program which finds the Joining Date of Employee and if it matches the criteria then It will Email you just before the vacation period starts for him/her.

6. Supposed, Employee joined on 1st Jan 2018 then on the 15th December 2018, you will receive an email about the employee and his vacations details.

7. Now, it's very normal to create process mentioned in Point 6. (If it still thought for you then don't worry... let us know, we will provide you the solution for that module too...)

Now guess, up to the point 7 everything goes according to the plan. Now, it's time to automate it!

Now suppose in the D drive, in some folder, your this workbook is placed and it isn't open even. But the Module with VB code is also there inside. You will be required to use TWO other services of Microsoft Windows for this process to automate the VB code in the module. How? Here is the process.

8. The First thing you required is to create a VB Script. yes a file with an extension of ".VBS". You have to open a Notepad and write some code and you have to save it as any name with .VBS extension. which will further create a VB Script file.

9. Now, if you click the VB script file, if everything is okay with it, then it will find the workbook, then it will find the module and then it will run the sub-procedure which will roam through each line of employee details and if your current date is 15th December 2018, then It will trigger the email procedure and will send the email to you automatically without opening the excel file. isn't its a great thing?

Following is the YouTube link to create a VBS file to trigger the sub-procedure.

Run Macro Automatically without Opening Excel File

Now the question is how can we create code to click on that VBS file automatically on each day so you don't have to do manually on each day?

10. Here is the answer, Use Windows Task Scheduler Tool. Just Target The .VBS file you have to create and configure it in such a way that it will trigger the VBS file on each day when your computer is open.

Following is the YouTube link to trigger the VBS file by Task Scheduler on daily basis.

Launch VB Script Using Task Scheduler

If you guys liked my idea then please let me know in the comment and please do like and share this article.

Thank you.

Kamal Bharakhda