vba - 如果单元格中的日期比当前日期早 2 年，则发送邮件

Question

我创建了一个宏，其中列出了 Excel 工作表中所有文件和子文件夹的名称、路径、创建日期、修改日期，该宏工作得很好。

但是现在我想创建一个宏，它可以检查文件和文件夹的整个列表，如果任何列出的文件/文件夹的创建日期(F 列)距离当前日期超过 2 年，它应该发送一封自动邮件，将文件名、路径以及创建和修改信息(基本上复制该特定行的所有信息)发送给我。

请在下面找到我收到错误(运行时错误)的代码这一行:

Sub send_files()
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
Dim sDate As String

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set sh = ThisWorkbook.Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
sDate = Date

For Each cell In sh.Columns("F").Cells
    Days = DateDiff("yyyy", Cells.Value, sDate)
    If Days > 2 Then
                Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .to = cell.Value
            .Subject = "Testfile"
            .Body = "Hi " & cell.Offset(0, -1).Value
            .send

        End With
    End If
Next cell

Set OutApp = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

错误发生在:

Days = DateDiff("yyyy", Cells.Value, sDate)

任何人都可以帮助解决此错误吗？

Answer 1

尝试改变

Days = DateDiff("yyyy", Cells.Value, sDate)

至

Days = DateDiff("yyyy", cell.Value, sDate)