How to Send Email When Computer is Locked?
I will break this "Tutorial" in 3 steps
1) Writing your Excel Macro
2) Preparing your vbscript file
3) Setting the task in Windows Task Scheduler
WRITING THE EXCEL MACRO
Open a new File in Excel and in the module, paste this code
Option ExplicitConst strTo As String = "abc@abc.com"Const strCC As String = "def@abc.com" '<~~ change "def@abc.com" to "" if you do not want to CCConst strBCC As String = "ghi@abc.com" '<~~ change "ghi@abc.com" to "" if you do not want to BCCSub Sample() Dim OutApp As Object, OutMail As Object Dim strbody As String, strSubject As String strSubject = "Hello World" strbody = "This is the message for the body" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = strTo .CC = strCC .BCC = strBCC .Subject = "This is the Subject line" .Body = strbody .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = NothingEnd Sub
Save the Excel File as C:\Tester.Xlsm
if you are using Excel 2007 onwards or C:\Tester.Xls
if you are using Excel 2003 and exit
PREPARING THE VBSCRIPT FILE
Open Notepad and then paste this code. Change the extension ".xls" as applicable.
Dim xlAppDim xlBookSet xlApp = CreateObject("Excel.Application")Set xlBook = xlApp.Workbooks.Open("C:\Tester.xls", 0, True)xlApp.Run "Sample"xlBook.ClosexlApp.QuitSet xlBook = NothingSet xlApp = Nothing
Save the File as Tester.vbs
and close it
SETTING UP THE TASK IN WINDOWS TASK SCHEDULER
Could you confirm your windows operating system? – Siddharth Rout 36 mins ago
Windows XP. Its my work computer (so has the usual logins etc). – keynesiancross 18 mins ago
Click on the Start Button | All Programs | Accessories | System Tools | Schedule Tasks to get this window
Double click on "Add Scheduled Task" to get this window
Click Next
Click on "Browse" and select the vbs file that we created earlier and click on "open"
The next window that you get is crucial as it is here we need to mention when script needs to run
After you have done the needful, click on next.
In this window, enter your login details so that the script can run even when your screen is locked.
Click "Next" when done and then click "Finish" in the next window. Your task scheduler now looks like this
And you are done
Lock your pc and go take a coffee break ;) When you come back (depending on what time you set in the task scheduler and how much time is your break), the email would have been sent.
HTH
I used HTML and JavaScript setInterval to run vba code to overcome such problem.Code:
<html><script>var flag = false;var xlApp;var xlBook;var i = 0;function processExcel(){//Open the excel containing macro for the first timeif(flag==false){ xlApp = new ActiveXObject ( "Excel.Application" );//Your Excel containing VBA code xlBook=xlApp.Workbooks.Open("Count1.2.xlsm")}// "a" is the VBA macro xlApp.Run("a");flag=true; i++; window.status="Last Updated " + new Date();}var wfunction run(){processExcel(); w= setInterval("processExcel()",120000);}function stop(){ clearInterval(w);}</script><body ><input type="button" value="Start" onclick="run()" /><input type="button" value="Stop" onclick="stop()"/></body></html>
SendKeys is likely the culprit. I still use CDO to send emails via Excel VBA. This should get you started: http://www.rondebruin.nl/cdo.htm