Schedule Run Outlook VBA Script At Time

This question is a different (simplified) version of this SO post (link).

I have a particular outlook VBA script I want to run automatically at 2 PM every day (and others at multiple other times). Running client-side is fine, since the computer is always on. How can I accomplish this reliably?

The (linked) SO post outlines a few approaches I tried, and this SO post is a general inquiry about approaches from which my specific question about running at a time was born.

1 Answer

Summary

We'll create a Powershell script that will send an email through Google's smtp server. This script can be run through task scheduler, which allows you to run it on a schedule. When the email is received in Outlook, a rule that looks for that email will trigger an Outlook VBA script. Viola.

Step 1: Create a secure credential file for the gmail account you will be using to send a "trigger" email to trigger the VBA script.

In Powershell, run

read-host -assecurestring | convertfrom-securestring | out-file $Home\autopass.txt

This will store a secure version of your password for that gmail account in your user directory. Note that if you use two factor authentication, you'll want to get an app password instead of your normal password for this credential, which you'll use in the following steps.

Step 2:This Powershell script will send an email through Google's smtp server using your own provided credentials. Set the fields appropriately, picking a particular very unique subject (eg, "@TRIGGERMYVBA21") that you will note in your Outlook rule.

 # You must paste your password after running the command: # read-host -assecurestring | convertfrom-securestring | out-file $Home\autopass.txt # before running the next part of the script. #Create credential file $password = get-content $Home\autopass.txt | convertto-securestring $credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "",$password #Send automatic email $From = "" $To = "" $Subject = "@TRIGGERMYVBA21" $Body = "some body" $SMTPServer = "smtp.gmail.com" $SMTPPort = "587" Send-MailMessage -From $From -to $To -Subject $Subject ` -Body $Body -SmtpServer $SMTPServer -port $SMTPPort -UseSsl ` -Credential ($credentials) # possible additions # $Cc = "" # $Attachment = "dir\attachment.txt" # both of which require the following tags in the Send-MailMessage command # -Attachments $Attachment -Cc $Cc

Save the script as a .ps1 file.

Step 3: Make sure you can make rules in Outlook that can "run script" as an action. If you can't, you'll need to use regedit to go to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\Security and add a new DWORD (32-BIT Value) titled, "EnableUnsafeClientMailRules" with value 1.

Step 4:Create a rule that finds:

• The gmail address of the gmail you'll be using in the Powershell script to trigger the rule
• The unique subject line you created in the Powershell script (eg, "@TRIGGERMYVBA21")

and then:

• Deletes the email,
• And runs a script

Step 5:Set up a Task Scheduler rule that fires the Powershell script at the time(s) you want to run it. You'll select "start a program" as the action, then type, "powershell" for the program, and finally "-File directory\script.ps1" in the add arguments box.

For the record, I'm using this to snooze emails until they are actionable using the folder moving VBA script. I move emails to particular folders and then they are moved back into the inbox when the rule triggers for that particular folder.

That script is included here for completeness:

Sub moveTheItems(item As Outlook.MailItem) Dim myNameSpace As Outlook.NameSpace Dim myInbox As Outlook.Folder Dim myDestFolder As Outlook.Folder Dim myItems As Outlook.Items Dim myItem As Object Set myNameSpace = Application.GetNamespace("MAPI") Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox) Set myItems = myInbox.Items Set myDestFolder = myInbox Set myItems = myInbox.Folders("Snooze Until 3 PM").Items For i = myItems.Count To 1 Step -1 'Iterates from the end backwards myItems.item(i).Move myDestFolder Next
End Sub