excel - 使用 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 Models\Daily Email.xlsm")
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Answer 1

创建 VBScript 文件 例如启动 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 任务计划程序 设置为在下午 3 点运行该 vbs 文件。