Excel VBA 从日期开始每 28 天发送电子邮件提醒

Question

我已经有下面的代码可以工作了，但我想有一种比在 28 天内有很多 if 语句更好的方法。

有人可以告诉我如何说每 28 天根据创建日期发送电子邮件吗？

谢谢！

---

Sub Current28()
'
'
'
'Dim Answer As VbMsgBoxResult
'Answer = MsgBox("Are you sure you want to run?", vbYesNo, "Run Macro")
'If Answer = vbYes Then
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Dim created As String
'Dim position As String
'Dim branch As String
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
    With Mail_Object.CreateItem(o)
        .Subject = "Current Vendor Care 28 Days"
        .To = "[e-mail]"
        .Body = Range("D" & i).Value & " " & Range("E" & i).Value & " " & 
Range("F" & i).Value
        '.display
        ' Our data below
        created = Range("L" & i).Value
        'position = Range("N" & i).Value
        'branch = Range("T" & i).Value
' Branch logic
'If branch = "Herne Bay" Then .To = "mail@myemail"
'If branch = "Whitstable" Then .To = "whitstable@myemail"
' Send logic
If DateDiff("d", created, Date - 28) = 0 Then .send
If DateDiff("d", created, Date - 56) = 0 Then .send
If DateDiff("d", created, Date - 84) = 0 Then .send
If DateDiff("d", created, Date - 140) = 0 Then .send
If DateDiff("d", created, Date - 168) = 0 Then .send
If DateDiff("d", created, Date - 196) = 0 Then .send
If DateDiff("d", created, Date - 224) = 0 Then .send
End With
Next i
    'MsgBox "E-mails successfully sent", 64
    'Application.DisplayAlerts = False
Set Mail_Object = Nothing
' The End If below relates to the run yes or no box
'End If
End Sub

---

Answer 1

使用取模运算符来实现此目的。当您不想将来每 28 个地方发送一封邮件时，您可能需要注意条件。

If DateDiff("d", created, Date) Mod 28 = 0 Then .send