我想使用Excel VBA发送Outlook电子邮件.
I want to send Outlook emails using Excel VBA.
Sendupdate 代码在手动运行时有效.
The code Sendupdate works when run manually.
我的第二个宏 StartTimer 旨在在我不在办公桌前的指定时间执行上述操作.
My second macro StartTimer is intended to execute the above at a set time when I am not at my desk.
计算机锁定时,电子邮件不会发送.当我回到办公桌前时,电子邮件作为草稿挂在那里,我需要单击 send 按钮.
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 = "x@y" 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?
推荐答案我将分3步打破此教程"
I will break this "Tutorial" in 3 steps
1)编写Excel宏
1) Writing your Excel Macro
2)准备您的vbscript文件
2) Preparing your vbscript file
3)在Windows Task Scheduler中设置任务
3) Setting the task in Windows Task Scheduler
编写出色的宏
在Excel中打开一个新文件,然后在模块中粘贴此代码
Open a new File in Excel and in the module, paste this code
Option Explicit Const strTo As String = "abc@abc" Const strCC As String = "def@abc" '<~~ change "def@abc" to "" if you do not want to CC Const strBCC As String = "ghi@abc" '<~~ change "ghi@abc" 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如果您使用的是Excel 2007及更高版本,则将Excel文件另存为 C:\ Tester.Xlsm ,如果您使用的是Excel 2003,则另存为 C:\ Tester.Xlsm ,然后退出
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
准备VBSCRIPT文件
打开记事本,然后粘贴此代码.更改扩展名".xls".
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将文件另存为 Tester.vbs 并关闭
在Windows任务调度程序中设置任务
您可以确认Windows操作系统吗?– Siddharth Rout 36分钟前
Could you confirm your windows operating system? – Siddharth Rout 36 mins ago
Windows XP.它是我的工作计算机(因此具有通常的登录名等).– 18分钟前keynesiancross
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
点击下一步
单击浏览",然后选择我们先前创建的vbs文件,然后单击打开"
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
您完成了
锁定您的电脑,然后去喝咖啡休息时间;)当您回来时(取决于您在任务计划程序中设置的时间以及您休息的时间),电子邮件本来应该是已发送.
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
更多推荐
锁定计算机后如何发送电子邮件?
发布评论