Excel VBA - 计算机锁定时不发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10211250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Email Does not Send When Computer is Locked
提问by keynesiancross
I'm having problems sending outlook emails using Excel VBA. I have the code to do it - Sendupdate- and it works fine when I manually run the macro. My second macro StartTimeris intended to execute the above at a set time when I am not at my desk.
我在使用 Excel VBA 发送 Outlook 电子邮件时遇到问题。我有代码可以做到这一点 -Sendupdate当我手动运行宏时它工作正常。我的第二个宏旨StartTimer在在我不在办公桌前的设定时间执行上述操作。
However, 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 sendbutton.
但是，当计算机被锁定时，电子邮件不会发送。当我回到我的办公桌时，电子邮件作为草稿挂在那里，我需要单击send按钮。
Here is my code:
这是我的代码：
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 code the macro to make sure the email gets pushed?
有没有办法对宏进行编码以确保电子邮件被推送？
回答by Siddharth Rout
I will break this "Tutorial" in 3 steps
我将分 3 个步骤打破这个“教程”
1) Writing your Excel Macro
1) 编写你的 Excel 宏
2) Preparing your vbscript file
2) 准备你的 vbscript 文件
3) Setting the task in Windows Task Scheduler
3) 在 Windows Task Scheduler 中设置任务
WRITING THE EXCEL MACRO
编写 Excel 宏
Open a new File in Excel and in the module, paste this code
在 Excel 中打开一个新文件，然后在模块中粘贴此代码
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.Xlsmif you are using Excel 2007 onwards or C:\Tester.Xlsif you are using Excel 2003 and exit
像C:\Tester.Xlsm使用 Excel 2007 或以后C:\Tester.Xls使用 Excel 2003一样保存 Excel 文件并退出
PREPARING THE VBSCRIPT FILE
准备 VBSCRIPT 文件
Open Notepad and then paste this code. Change the extension ".xls" as applicable.
打开记事本，然后粘贴此代码。根据需要更改扩展名“.xls”。
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.vbsand close it
将文件另存为Tester.vbs并关闭它
SETTING UP THE TASK IN WINDOWS TASK SCHEDULER
在 WINDOWS 任务调度程序中设置任务
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
你能确认你的Windows操作系统吗？– Siddharth Rout 36 分钟前
视窗 XP。它是我的工作计算机（所以有通常的登录等）。– 凯恩斯克罗斯 18 分钟前
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"
单击“浏览”并选择我们之前创建的 vbs 文件，然后单击“打开”
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
HTH
回答by Bhanu Sinha
I used HTML and JavaScript setInterval to run vba code to overcome such problem. Code:
我使用 HTML 和 JavaScript setInterval 来运行 vba 代码来解决这个问题。代码：
<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>
回答by deusxmach1na
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
SendKeys 可能是罪魁祸首。我仍然使用 CDO 通过 Excel VBA 发送电子邮件。这应该让你开始：http: //www.rondebruin.nl/cdo.htm
