Excel VBA - 当计算机被锁定时电子邮件不发送 [英] Excel VBA - Email Does not Send When Computer is Locked

问题描述

我在使用Excel VBA发送outlook电子邮件时遇到问题。我有代码来做 - Sendupdate - 当我手动运行宏时，它的工作正常。我的第二个宏 StartTimer 的目的是在我不在我的桌子的设定时间执行上述。

但是，当计算机被锁定时，电子邮件不发送。当我回到我的办公桌时，电子邮件挂在那里作为草稿，我需要点击发送按钮。

这是我的代码：

  Sub SendUpdate（）
收件人=[e-mail]
 Subj =update
 Dim msg As String 
 msg =hello
 
 HLink =mailto： &？？
 HLink = HLink&subject =&&
 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 
  / pre> 
 
 
有没有办法编码宏来确保邮件被推送？
解决方案
我将在3个步骤中打破这个教程
 
 
 
 1）编写Excel宏
 
 
）准备您的vbscript文件
 
 
 
 3）在Windows任务计划程序中设置任务
 
 
 
 
 
 
 写出EXCEL MACRO  
 
 
 
 
 
 
在Excel中打开一个新的文件，并在该模块中粘贴此代码
  Option Explicit 
 
 Const strTo As String =[e-mail]
 Const strCC As String = [e-mail]'< ~~ [e-mail]如果你不想要CC 
 Const strBCC As String =[e-mail]'<〜 〜将[e-mail]改为如果您不想要BCC 
 
 Sub Sample（）
 Dim OutApp作为Object，OutMail As Object 
 Dim strbody As String，strSubject As String 
 
 strSubject =Hello World
 strbody =这是身体的消息
 
 Set OutApp = CreateObject（Outlook应用程序）
 
设置OutMail = OutApp.CreateItem（0）
 
错误恢复下一步
带OutMail 
 .To = strTo 
 .CC = strCC 
 .BCC = strBCC 
 .Subject =这是主题行
 .Body = strbody 
。发送
结束Wi th 
 On Error GoTo 0 
 
 Set OutMail = Nothing 
 Set OutApp = Nothing 
 End Sub 
  
将Excel文件另存为 C：\Tester.Xlsm 如果您使用的是Excel 2007或$ $ c> C：\Tester.Xls 如果您使用Excel 2003并退出
 
 
 
 
 
 
 准备VBSCRIPT文件 
 
 
 
 
 
 
打开记事本，然后粘贴此代码。根据需要更改扩展名.xls。
  Dim xlApp 
 Dim xlBook 
 
设置xlApp = CreateObject（Excel.Application）
设置xlBook = xlApp.Workbooks.Open（C：\Tester.xls，0，True）
 xlApp.RunSample
 xlBook.Close 
 xlApp.Quit 
 
设置xlBook =没有
设置xlApp =没有
  
将文件另存为 Tester.vbs 并关闭它
 
 
 
 < img src =https://i.stack.imgur.com/oTlIO.pngalt =enter image description here> 
 
 
 
 
 
 
 在WINDOWS任务调度器中设置任务 
 
 
 
 
 
 
 
你可以确认你的Windows操作系统吗？ -  Siddharth Rout 36分钟前
 
 
 
 Windows XP。它是我的工作电脑（所以有通常的登录等）。 -  keynesiancross 18分钟前
 
 
 
 
 
 
点击开始按钮|所有程序|配件|系统工具|计划任务获取此窗口
 
 
 
  
 
 
 
双击添加计划任务以获取此窗口
 
 
 
  
 
 
 
点击下一步
 
 
 
  
 
 
 
点击浏览并选择我们之前创建的vbs文件，然后单击打开
 
 
 
您获得的下一个窗口至关重要，因为我们需要提到脚本需要运行
 
 
 
  
 
 
 
完成需要后，点击下一步。
 
 
 
  
 
 
 
在此窗口中，输入您的登录详细信息，以便s即使您的屏幕被锁定，脚本也可以运行。
 
 
 
完成后单击下一步，然后在下一个窗口中单击完成。您的任务调度程序现在看起来像这样
 
 
 
  
 
 
 
你完成了
 
 
 
 
 
 
锁定你的电脑并且休息一下;）当您回来（根据您在任务计划中设置什么时间以及您的休息时间），电子邮件将被发送。
 
 
 
 HTH 
 
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 StartTimer is intended to execute the above at a set time when I am not at my desk.

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 send button. 

Here is my code:
Sub SendUpdate()
Recipient = "[e-mail]"
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? 
解决方案
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 = "[e-mail]"
Const strCC As String = "[e-mail]"  '<~~ change "[e-mail]" to "" if you do not want to CC
Const strBCC As String = "[e-mail]" '<~~ change "[e-mail]" 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

                        
这篇关于Excel VBA  - 当计算机被锁定时电子邮件不发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！