时间戳

首页 > 解决方案 > 打开文件时如何多次停止发送电子邮件

excel - 打开文件时如何多次停止发送电子邮件

问题描述

我需要代码方面的帮助才能每天只发送一次电子邮件。

打开文件时，代码设置为自动发送电子邮件并基于截止日期。但是，该文件可以在一天中多次打开。我需要它每天只发送一封电子邮件（第一次打开文件），但我不知道如何正确编码。

    For i = 2 To lRow

    If Cells(i, 8).Value <> "Completed" Then 
        If Cells(i, 2) <> "" Then
            toDate = Replace(Cells(i, 5), ".", "/")
             If Left(Cells(i, 18), 5) <> "Mail" And toDate - Date <= 7 Then
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                toList = Cells(i, 7)
                eSubject = "ACTION ITEM - " & Cells(i, 3) & " is due on " & Cells(i, 5)
                eBody = "NOTICE for " & Cells(i, 6) & vbCrLf & vbCrLf & "This is a reminder that you have task(s) that are due or ones that are past due. Please complete your tasks as soon as possible, then notify the Quality Administrator when the task is complete."
                On Error Resume Next
                With OutMail
                    .To = toList
                    .CC = ""
                    .BCC = ""
                    .Subject = eSubject
                    .Body = eBody
                    .bodyformat = 1
                    '.Display
                    .Send
                End With
                On Error GoTo 0
                Set OutMail = Nothing
                Set OutApp = Nothing
                Cells(i, 9) = "Mail Sent " & Date + Time
    End If
        End If
           End If 
    Next i

标签： excelvba

解决方案

像这样：

For i = 2 To lRow
    If Cells(i, 8).Value <> "Completed" And _
       Cells(i, 2) <> "" And _
       Cells(i, 9) <> Date Then

            'send the mail
            Cells(i, 9) = Date '<<< store the date sent

    End If
Next i

推荐阅读