excel - 自动重新启动宏以每天运行一次

Question

我需要在某个截止日期前 7 天发送提醒。

在帮助下，我设法创建了这段代码：

Private Sub Workbook_Activate()
  
    Dim i As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String
        
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
        
    For i = 2 To Range("e65536").End(xlUp).Row
        If Cells(i, 9) <> "Y" Then
            If Cells(i, 5) - 7 < Date Then
                    
                strto = Cells(i, 7).Value 'email address
                strsub = Cells(i, 1).Value & " " & Cells(i, 2).Value & " compleanno il " & Cells(i, 5).Value 'email subject
                strbody = "Il compleanno di " & Cells(i, 1).Value & " " & Cells(i, 2).Value & " sarà il " & Cells(i, 5).Value & vbNewLine 'email body
                    
                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    .Send 
                End With
                    
                Cells(i, 8) = "Mail Sent " & Now()
                Cells(i, 9) = "Y"
                    
            End If
        End If
    Next
        
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

我必须每天手动启动它，因为即使更新了截止日期，宏也不会自行重新启动。

我尝试用 Sub Workbook_SelectionChange(ByVal Target As Range) 替换 Sub Workbook_Activate()。

标签： excelvba