Outlook 和 Excel VBA 任务计划程序 [英] Outlook and Excel VBA task Scheduler

查看：16
本文介绍了Outlook 和 Excel VBA 任务计划程序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我迫切需要帮助，因为整个系统"应该在本周启动，但作为 vba 脚本 和代码等的完全新手，我不知道如何执行任务.

我创建了一个 excel，它根据截止日期生成每日电子邮件提醒，并希望使用任务计划程序每天打开它.

我想要的:

  1. PC 在早上 745 点自动启动(很可能使用 bios 电源管理)
  2. PC 到达用户登录页面.
  3. 任务计划程序打开 Outlook，然后打开我的 excel 并在早上 8 点发送电子邮件.
  4. Excel 被保存并关闭.(这是否需要在 excel 中使用单独的宏或代码?)
  5. 使用任务计划程序关闭计算机.

从我从其他人提出的各种页面/问题中发现，必须编写 vbs/cmd 脚本，但一些消息来源指出，在任务调度程序中运行该脚本，我是不应该勾选无论用户是否登录都运行"的选项(也不知道如何编写它们，我所知道的是我必须在记事本中编写它并保存在文件名的特定扩展名中)希望有人可以为我提供有关如何执行上述任务的详细指南.另外，我尝试使用任务计划程序直接打开 Outlook 应用程序，但它似乎不起作用.它也需要脚本吗?

我的 excel 所需的其他帮助:目前，我的提醒宏仅在第一张纸上运行.它可以在所有工作表上运行吗?

excel 代码如下:

将 Bcell 调暗为范围Dim iTo、iSubject、iBody 作为字符串将重要性级别调暗为字符串公共子 CheckDates()对于范围内的每个 Bcell("c2", Range("c" & Rows.Count).End(xlUp))如果 Bcell.Offset(0, 5) <>Empty Then ' 如果电子邮件列不为空，则命令继续如果 Now() - Bcell.Offset(0, 6) >0.9875 然后 ' 如果当前时间在上次发送邮件的时间之后的 23.7 小时内，则不会发送邮件.' 示例:如果邮件在星期一上午 8 点发送，在星期一上午 8 点到星期二上午 7:18 之间，将不会发送邮件.If DateDiff("d", Now(), Bcell) = 60 Then ' 如果 c 列中的日期是 60 天后，将发送电子邮件' Debug.Print Bcell.Row &60"iTo = Bcell.Offset(0, 5)iSubject = "第一次提醒 - IN/SSGIFR 号" &Bcell.Offset(0, -2)iBody = "亲爱的" &vbCrLf &vbCrLf &_"IN/SSGIFR 号" &Bcell.Offset(0, -2) &"- " &Bcell.Offset(0, 1) &" (批次:" & Bcell.Offset(0, 3) & "，数量:" & _Bcell.Offset(0, 2) &")" &", 通知 " &Bcell.Offset(0, -1) &" 将在 " &_Bcell &."&vbCrLf &请确保货物在到期日之前关闭，并尽快转发关闭报告."&_vbCrLf &vbCrLf &谢谢" &vbCrLf &vbCrLf &问候"&vbCrLf &《YYY系》&_vbCrLf &XXX私人有限公司"发送电子邮件Bcell.Offset(0, 6) = 现在()万一If DateDiff("d", Now(), Bcell) = 30 Then ' 如果 c 列中的日期是 30 天后，将发送电子邮件' Debug.Print Bcell.Row &30"iTo = Bcell.Offset(0, 5)iSubject = "第二个提醒 - IN/SSGIFR 号" &Bcell.Offset(0, -2)iBody = "亲爱的" &vbCrLf &vbCrLf &_"IN/SSGIFR 号" &Bcell.Offset(0, -2) &"- " &Bcell.Offset(0, 1) &" (批次:" & Bcell.Offset(0, 3) & "，数量:" & _Bcell.Offset(0, 2) &")" &", 通知 " &Bcell.Offset(0, -1) &" 将在 " &_Bcell &."&vbCrLf &请确保货物在到期日之前关闭，并尽快转发关闭报告."&_vbCrLf &vbCrLf &谢谢" &vbCrLf &vbCrLf &问候"&vbCrLf &《YYY系》&_vbCrLf &XXX私人有限公司"发送电子邮件Bcell.Offset(0, 6) = 现在()万一If DateDiff("d", Now(), Bcell) = 7 Then ' 如果 c 列中的日期是 30 天后，将发送电子邮件' Debug.Print "ROW: " &Bcell.Row &7"iTo = Bcell.Offset(0, 5)iSubject = "最终提醒 - IN/SSGIFR 编号" &Bcell.Offset(0, -2)iBody = "亲爱的" &vbCrLf &vbCrLf &_"IN/SSGIFR 号" &Bcell.Offset(0, -2) &"- " &Bcell.Offset(0, 1) &" (批次:" & Bcell.Offset(0, 3) & "，数量:" & _Bcell.Offset(0, 2) &")" &", 通知 " &Bcell.Offset(0, -1) &" 将在 " &_Bcell &."&vbCrLf &请确保货物在到期日之前关闭，并尽快转发关闭报告."&_vbCrLf &vbCrLf &谢谢" &vbCrLf &vbCrLf &问候"&vbCrLf &《YYY系》&_vbCrLf &XXX私人有限公司"发送电子邮件Bcell.Offset(0, 6) = 现在()万一万一万一iTo = 空iSubject = 空iBody = 空下一个 Bcell结束子私人子 SendEmail()Dim OutApp 作为对象Dim OutMail 作为对象Dim strbody 作为字符串Set OutApp = CreateObject("Outlook.Application")设置 OutMail = OutApp.CreateItem(0)出错时继续下一步使用 OutMail.To = iTo.CC = "DEPARTMENT@EMAIL.COM" &";COLLEAGUE@EMAIL.COM".BCC = "".Subject = iSubject.Body = iBody.Importance = 重要性级别'你可以添加这样的文件'.Attachments.Add("C:	est.txt").展示结束于出错时转到 0设置 OutMail = 无设置 OutApp = 无结束子

解决方案

现在你已经运行了 Outlook，让我们创建一个带有提醒的重复任务项，并设置你想要调用 Excel 的时间.

代码转到ThisOutlookSession下的Outlook

Private Sub Application_Reminder(ByVal Item As Object)如果 TypeOf Item 是 Outlook.TaskItem 那么如果不是 Item.Subject = "发送报告" 那么退出子万一万一GetTemp Item '调用子结束子Private Sub GetTemp(ByVal Item As TaskItem)Dim xlApp 作为 Excel.ApplicationDim xlBook 作为工作簿设置 xlApp = 新建 Excel.ApplicationSet xlBook = xlApp.Workbooks.Open("C:TempExcel_File.xlsm") ' 用 Excel 名称更新xlApp.Visible = True'//在 Excel_File 中运行宏xlBook.Application.Run "Module1.CheckDates" ' 更新子名设置 xlApp = 无设置 xlBook = 无结束子

更新 Excel 路径

xlApp.Workbooks.Open("C:TempExcel_File.xlsm")

确保将 Excel 库对象添加到 Outlook 并启用宏安全性以运行

工具 - 参考然后查找 Microsoft Excel xxx 对象库

Am in desperate need for help as this whole "system" should be up by this week but being a totally novice to vba scripts and codes etc, I have no idea how to perform the tasks.

I have created an excel which generates daily email reminders based on due dates and would like to use task scheduler to get it opened daily.

What I want:

  1. PC to auto boot up at 745am (most likely using bios power management)
  2. PC reach user login page.
  3. Task scheduler opens outlook, followed by my excel and sent out the emails at 8am.
  4. Excel get saved and closed. (does this need a separate macro or code within the excel?)
  5. Computer shut down using task scheduler.

From what I found out from various pages/questions asked by others, a vbs/cmd script have to be written, but some sources stated that in the task scheduler to run that script, I am not supposed to tick the option to "run whether user is logged on or not" (have no Idea how to write them as well, all I know is that I have to write it in notepad and save in the specific extension for the file name) Hope someone could provide me with a detailed guide on how to perform the above tasks. Also, I tried to use task scheduler to open the outlook app directly but it doesn't seem to work. Does it require a script as well?

Other help needed for my excel: currently, my reminder macro is running on the 1st sheet only. Is it possible for it to run on all sheets?

The code of the excel is as below:

Dim Bcell As Range
Dim iTo, iSubject, iBody As String
Dim ImportanceLevel As String

Public Sub CheckDates()

 For Each Bcell In Range("c2", Range("c" & Rows.Count).End(xlUp))

If Bcell.Offset(0, 5) <> Empty Then ' if email column is not empty then command continues
    If Now() - Bcell.Offset(0, 6) > 0.9875 Then ' mail will not be sent if current time is within 23.7 hours from time of mail last sent.
    ' Example: if mail is sent at 8am monday, between 8am monday to tuesday 7:18am, mail will not be sent.

        If DateDiff("d", Now(), Bcell) = 60 Then ' if date in column c is 60days later, email will be sent
'       Debug.Print Bcell.Row & " 60"

        iTo = Bcell.Offset(0, 5)

        iSubject = "FIRST REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -2)

        iBody = "Dear all," & vbCrLf & vbCrLf & _
        "IN/SSGIFR No. " & Bcell.Offset(0, -2) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
        Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
        Bcell & "." & vbCrLf & "Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
        vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "YYY Department" & _
        vbCrLf & "XXX Pte Ltd."

        SendEmail
        Bcell.Offset(0, 6) = Now()

        End If


          If DateDiff("d", Now(), Bcell) = 30 Then ' if date in column c is 30 days later, email will be sent
'         Debug.Print Bcell.Row & " 30"

          iTo = Bcell.Offset(0, 5)

          iSubject = "SECOND REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -2)

          iBody = "Dear all," & vbCrLf & vbCrLf & _
          "IN/SSGIFR No. " & Bcell.Offset(0, -2) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
          Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
          Bcell & "." & vbCrLf & "Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
          vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "YYY Department" & _
          vbCrLf & "XXX Pte Ltd."

          SendEmail
          Bcell.Offset(0, 6) = Now()

        End If

        If DateDiff("d", Now(), Bcell) = 7 Then ' if date in column c is 30days later, email will be sent
'       Debug.Print "ROW: " & Bcell.Row & " 7"
        iTo = Bcell.Offset(0, 5)

        iSubject = "FINAL REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -2)

        iBody = "Dear all," & vbCrLf & vbCrLf & _
        "IN/SSGIFR No. " & Bcell.Offset(0, -2) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
        Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
        Bcell & "." & vbCrLf & "Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
        vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "YYY Department" & _
        vbCrLf & "XXX Pte Ltd."

        SendEmail
        Bcell.Offset(0, 6) = Now()

        End If
    End If
End If
            iTo = Empty
            iSubject = Empty
            iBody = Empty
    Next Bcell

End Sub



Private Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    With OutMail
        .To = iTo
        .CC = "DEPARTMENT@EMAIL.COM" & ";COLLEAGUE@EMAIL.COM"
        .BCC = ""
        .Subject = iSubject
        .Body = iBody
        .Importance = ImportanceLevel
        'You can add a file like this
        '.Attachments.Add ("C:	est.txt")
        .Display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

解决方案

Now that you have Outlook running, Lets create a Recurring Task Item with reminder and set the time that you would like to call Excel.

MSDN Application.Reminder Event (Outlook) Occurs immediately before a reminder is displayed.

Task Item with Reminder

Code goes to Outlook under ThisOutlookSession

Private Sub Application_Reminder(ByVal Item As Object)
    If TypeOf Item Is Outlook.TaskItem Then

        If Not Item.Subject = "Send Report" Then
            Exit Sub
        End If

    End If

    GetTemp Item ' call sub
End Sub

Private Sub GetTemp(ByVal Item As TaskItem)
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("C:TempExcel_File.xlsm") ' update with Excel name
    xlApp.Visible = True

'   // Run Macro in Excel_File
    xlBook.Application.Run "Module1.CheckDates" ' Update with subname

    Set xlApp = Nothing
    Set xlBook = Nothing
End Sub

Update Excel Path

xlApp.Workbooks.Open("C:TempExcel_File.xlsm")

Make sure to add Excel Library object to Outlook and macro security is enable to run

Tools - References then look for Microsoft Excel xxx Object Library

这篇关于Outlook 和 Excel VBA 任务计划程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆