Menu
I want to send Outlook emails using Excel VBA.
The code Sendupdate works when run manually.
My second macro StartTimer is intended to execute the above at a set time when I am not at my desk.
When the computer is locked the email does not send. When I come back to my desk the email is hanging there as a draft, and I need to click the send button.
Sub SendUpdate()
Recipient = "[email protected]"
Subj = "update"
Dim msg As String
msg = "hello”
HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%s"
End Sub
Sub StartTimer()
Application.OnTime TimeValue("18:00:00"), "SendUpdate"
End Sub
Is there a way to make sure the email gets pushed?
664
Unfortunately your computer does need to be on and Outlook does need to be open for the message to go after hours but the system can be locked without affecting the send. When you close Outlook with messages that haven't been sent, Outlook will ask if you're sure you want to close and you can safely close Outlook.
If you use the web outlook then it will send regardless of whether your computer is on or not.
While composing a message, select the More options arrow from the Tags group in the Ribbon. Under Delivery options, select the Do not deliver before check box, and then click the delivery date and time you want. Click Close. When you're done composing your email message, select Send.
In Outlook the "delay send" requires the application be open and connected to the internet to send at the scheduled time.
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 Explicit
Const strTo As String = "[email protected]"
Const strCC As String = "[email protected]" '<~~ change "[email protected]" to "" if you do not want to CC
Const strBCC As String = "[email protected]" '<~~ change "[email protected]" to "" if you do not want to BCC
Sub 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 = Nothing
End 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 xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Tester.xls", 0, True)
xlApp.Run "Sample"
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set 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
115
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 time
if(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 w
function 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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
