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

问题描述

我绝对需要帮助，因为这个整个系统应该在本周结束，但作为一个完全新手的 vba脚本和代码等，我不知道如何执行任务。

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.

我已经创建了一个excel，根据到期日生成每日电子邮件提醒，并希望使用任务调度程序每天打开它。

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.

我想要的：

1. PC在745am自动启动（最有可能使用bios电源管理） li>
   
2. PC到达用户登录页面


3. 任务调度程序打开前景，接着是我的excel，并在上午8点发送电子邮件。


4. Excel保存并关闭。 （这是否需要在Excel中单独的宏或代码？）


5. 使用任务调度程序关闭计算机。

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.

从我从别人提出的各种页面/问题中发现的内容，必须写一个 vbs / cmd 脚本，但有些消息来源指出，在任务调度程序中运行该脚本，我不应该勾选运行用户是否登录的选项（没有想法如何写它们，所有我知道的是，我必须写在记事本中，并保存在特定的扩展名为文件名）
希望有人可以向我提供有关如何执行上述任务的详细指南。
此外，我试图使用任务调度程序直接打开outlook应用程序，但似乎不起作用。它需要一个脚本吗？

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?

我的excel需要其他帮助：目前，我的提醒宏仅在第一页上运行。

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?

excel的代码如下：

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 = "[e-mail]" & ";[e-mail]"
        .BCC = ""
        .Subject = iSubject
        .Body = iBody
        .Importance = ImportanceLevel
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

推荐答案

现在，您已经有Outlook运行，现在创建一个循环任务项目提醒，并设置您要调用Excel的时间。

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

具有提醒的任务项目

代码转到Outlook下的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:\Temp\Excel_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

更新Excel路径

xlApp.Workbooks.Open("C:\Temp\Excel_File.xlsm")

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

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

工具 - 引用然后查找Microsoft Excel xxx对象库

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