使用VBA Excel / Outlook在每个工作日发送刷新的Excel工作表

Question

每天下午3点，我都必须向同事发送Excel工作簿。工作簿中的宏只是将一张纸中的所有单元格和"选择性粘贴"复制到另一张纸并保存工作簿。我已经编写了宏来执行此操作并将其发送到电子邮件地址，但是我很难让它自动发送。我已经指示了计划任务，但我不知道如何在打开Excel，执行Marco，保存工作簿并将其发送给指定人员之间建立联系。代码如下 - 感谢您的帮助。

Sub Fixing()
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-9
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
      xlNone, SkipBlanks:=False, Transpose:=False
    Range("I7").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .to = ""
        .CC = ""
        .BCC = "my email address"
        .Subject = "Daily Email"
        .Body = ""
        .Attachments.Add ("F:Excel ModelsDaily Email.xlsm")
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Answer 1

创建VBScript file例如 start excel action.vbs内部使用以下代码：

    dim EXL
    set EXL = CreateObject("Excel.Application")
    'not required
    EXL.Visible = true
    'your file and macro    
    EXL.Workbooks.Open "full path to your excel file including extension here" 
    EXL.Run "Fixing"
    'close everything
    EXL.Quit
    Set EXL = Nothing

保存文件并将Windows Task Scheduler设置为 在下午 3 点运行该vbs文件。