Windows任务管理器安排VBA宏使用OutLook每日手动而非自动运行发送电子邮件(Windows Task Manager Schedule VBA Macro to Send An Email Using OutLook Daily Running Manually and Not Automatically)
我有我正在运行的宏,为此我创建了一个vb脚本并使用Windows任务管理器来安排它每天运行。
每当我手动运行或尝试更改触发器中的时间时,我总是确保excel和outlook都没有运行。 如果我在Excel VBA中运行宏,它会发送电子邮件。 在安排任务每天运行之后,如果我去(在TaskScheduler中)查看 - >隐藏任务并手动单击运行,它就会作为测试发送电子邮件。
但是,如果我安排它在每天的特定时间运行,比如可能从今天起15分钟开始,它不发送电子邮件,最后一次运行结果是(0x0)。
我已在信任中心设置中启用了所有宏,并选择了“信任访问VBA项目对象模型”,这不是管理员权限的问题。 这是我的VB脚本
Dim ObjExcel, ObjWB Set ObjExcel = CreateObject("Excel.Application") Set ObjWB = ObjExcel.Workbooks.Open("C:\Users\myUser\MyLocation\MyFile.xlsm") ObjExcel.Visible = False ObjExcel.DisplayAlerts = False ObjExcel.AskToUpdateLinks = False ObjExcel.AlertBeforeOverwriting = False 'vbs opens a file specified by the path below 'either use the Workbook Open event (if macros are enabled), or Application.Run ObjExcel.Application.Run "MyFile.xlsm!main_macro" ObjWB.Save ObjWB.ActiveWorkbook.Close ObjExcel.Quit Set ObjWB = Nothing Set ObjExcel = Nothing WScript.Echo "Finished." WScript.Quit
另外,这是我的TaskScheduler设置:
参考:
如何使用Windows任务计划程序为xlsm文件设置重复计划
任务计划程序不运行Excel VBA代码以将PDF作为电子邮件附件发送
如果我将以下设置为
True
:ObjExcel.Visible = False ObjExcel.DisplayAlerts = False ObjExcel.AskToUpdateLinks = False ObjExcel.AlertBeforeOverwriting = False
Excel以只读模式打开,并说单击通知以接收通知,您现在可以编辑工作簿但我无法编辑它,另一位用户打开工作簿进行编辑。
I have my macro which is running, & for this I created a vb script and used windows task manager to schedule its run every day.
Whenever I run manually or attempt changing the time in the trigger, I always make sure that both excel and outlook are not running. If I run the macro in Excel VBA, it sends the email. After scheduling the task to run everyday, just as a test if I go to (in TaskScheduler) View -> Hidden Tasks and manually click Run, it sends the email.
However, if I schedule it to run at a specific time everyday, say maybe starting today, 15 minutes from now, it does not send the email & the last run result is (0x0).
I have enabled all macros in the trust center settings and selected 'Trust access to the VBA project object model', & it's not an issue about administrator privileges. This is my VB script
Dim ObjExcel, ObjWB Set ObjExcel = CreateObject("Excel.Application") Set ObjWB = ObjExcel.Workbooks.Open("C:\Users\myUser\MyLocation\MyFile.xlsm") ObjExcel.Visible = False ObjExcel.DisplayAlerts = False ObjExcel.AskToUpdateLinks = False ObjExcel.AlertBeforeOverwriting = False 'vbs opens a file specified by the path below 'either use the Workbook Open event (if macros are enabled), or Application.Run ObjExcel.Application.Run "MyFile.xlsm!main_macro" ObjWB.Save ObjWB.ActiveWorkbook.Close ObjExcel.Quit Set ObjWB = Nothing Set ObjExcel = Nothing WScript.Echo "Finished." WScript.Quit
Also, here are my TaskScheduler Settings:
Ref:
Sending email from excel automatically based on date
How to set recurring schedule for xlsm file using Windows Task Scheduler
How can you run an Excel macro through a schedule task
Task Scheduler does not run Excel VBA Code to send PDF as Email Attachment
If I set the below to
True
:ObjExcel.Visible = False ObjExcel.DisplayAlerts = False ObjExcel.AskToUpdateLinks = False ObjExcel.AlertBeforeOverwriting = False
Excel opens in read-only mode and says click notify to receive the notification that you can now edit the workbook but I cannot edit it, says another user has the workbook open for editing.
原文:
最满意答案
在查询中使用动态表名是使用Prepared Staments最好的,在mysql中也可以用于连接,函数是
concat
SET @id := '47'; SET @table := concat(@id,'_2013_2014_voucher'); set @qry1:= concat('select * from ',@table); prepare stmt from @qry1 ; execute stmt ;
您也可以为删除查询执行此操作
The usage of dynamic table names within the query is best with Prepared Staments, also in mysql for concatenation the function is
concat
SET @id := '47'; SET @table := concat(@id,'_2013_2014_voucher'); set @qry1:= concat('select * from ',@table); prepare stmt from @qry1 ; execute stmt ;
You can do it for the delete query as well
相关问答
更多-
SQL“in”语句中(SQL “in” statement)[2024-04-22]
请规范你的桌子。 阅读为什么在db列中存储逗号分隔值非常糟糕 。 如果你不被允许,使用FIND_IN_SET()函数: SELECT * FROM table1 WHERE FIND_IN_SET(3, column1) Please normalize your table. Read why storing comma separated values in a db column is really bad. If you are not allowed to, use FIND_IN_SET( ... -
您的EXEC在不同的上下文中执行,因此它不知道在原始上下文中声明的任何变量。 您应该可以使用临时表而不是表变量,如下面的简单演示所示。 create table #t (id int) declare @value nchar(1) set @value = N'1' declare @sql nvarchar(max) set @sql = N'insert into #t (id) values (' + @value + N')' exec (@sql) select * from #t d ...
-
这就是你需要这样做的方法: CREATE PROCEDURE dbo.uspFilter @Search varchar(300) = null, @Debug bit = 1 AS DECLARE @SQL nvarchar(max); DECLARE @Params nvarchar(max); SELECT @Params = N'@FirstName varchar(300)'; SELECT @SQL = N'SELECT * FROM [Table]'; IF @First ...
-
如何在sql create语句中使用变量名作为表名(How to use variable name as table name in sql create statement)[2023-08-24]
您可以用大括号括起变量,如下所示: $query = "CREATE TABLE {$_POST['name']} ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )"; You could surround the variable with curly brackets, like this: $query = "CREATE TABLE {$_ ... -
在这里做了一些猜测,因为它非常奇怪它没有拿起单引号: 首先,SQL最后缺少一个引号。 你使用管道的任何原因||? select_stm:= 'SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno where job = ' || UPPER(e_job) || ' and loc = ' || UPPER(e_loc) || '; 如果您使用如下所示的类似语句,是否会出现相同的错误? select_stm:= 'SELEC ...
-
您不能将变量用于表名。 当您select address from @table SQL中select address from @table期望@table是表类型的变量,而不是标量。 你想做这样的事情: ALTER PROCEDURE testTblName ( @id INT, @name VARCHAR(50) ) AS BEGIN DECLARE @table VARCHAR(50), @add VARCHAR(150), @ ...
-
当语句具有动态表名称时如何防止SQL注入?(How to prevent SQL injection when the statement has a dynamic table name?)[2023-12-04]
不幸的是,JDBC不允许你在语句内使表名成为绑定变量。 (这有其原因)。 所以你不能编写或实现这种功能: connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123); 并且将TUSER绑定到声明的表名。 因此,你唯一安全的方法就是验证用户输入。 然而,最安全的方式并不是验证它,并允许用户输入数据库,因为从安全角度来看,您始终可以指望用户比验证更智能。 永远不要相信一个动态的,用户生成的字符串,在您的语句中连接起来。 那么 ... -
这应该工作: declare @tablename varchar(100) declare @sql varchar(max) set @tablename = 'studentname' set @sql = 'merge ' + @tablename + ' as target using #temptable as source on (target.student_id = source.class_id) when not matched by target then insert( ...
-
动态SQL语句太长(Dynamic SQL Statement is too long)[2022-09-20]
我猜答案在这里: 所以我创建了包含表名的变量,并将这些变量与动态sql语句一起使用,因此我不需要更改我在这个更新脚本中添加的过程和函数中的表名,而只是更改内容变量。 我猜,你的动态T-SQL语句是使用字符串连接构建的。 所以,假设我们有这样的事情: DECLARE @DynamicSQLSTatement NVARCHAR(MAX); DECLARE @TableName01 NVARCHAR(128) = 'T01'; DECLARE @TableName02 NVARCHAR(128) = 'T02' ... -
sql语句中的动态表名(Dynamic table name at sql statement)[2024-03-16]
在查询中使用动态表名是使用Prepared Staments最好的,在mysql中也可以用于连接,函数是concat SET @id := '47'; SET @table := concat(@id,'_2013_2014_voucher'); set @qry1:= concat('select * from ',@table); prepare stmt from @qry1 ; execute stmt ; 您也可以为删除查询执行此操作 The usage of dynamic table nam ...