VBA- excel电子邮件提醒帮助 [英] VBA- excel email reminders help

问题描述

大家好！

我目前有一个excel根据每一行的截止日期生成电子邮件提醒，每当打开excel日志时都会将其发送出去。我打算每隔8点使用一个任务调度程序自动打开日志。但是，当我想要
打开日志时，问题表面仅用于编辑目的。 

I currently have an excel that produces email reminders based on each row`s due date which sends them out whenever the excel log is opened. I plan to use a task scheduler to open the log automatically on every 8am. However problem surfaces when I want to open the log solely for editing purposes. 

希望有人可以为我提供解决方案，可能是通过增加公式/代码的时间这样电子邮件只会发送一次或仅在早上8点到8点30分之间发送。

Hope someone could provide me with a solution, maybe by adding time to the formula/code such that emails will only be sent once or only be sent between 8am - 830am.

对任何其他解决方案也开放！ （：

Am open to any other solutions as well! (:

excel的格式：

当前代码（已用非机密字替换条款）：

Current Code (have replaced terms with non-confidential words) :

Dim Bcell As Range
Dim iTo, iSubject, iBody As String
Dim ImportanceLevel As String

Public Sub CheckDates()
    
    For Each Bcell In Range("D2", Range("D" & Rows.Count).End(xlUp))

        If Bcell.Offset(0, 5) <> Empty Then

            If DateDiff("d", Now(), Bcell) = 60 Then
'               Debug.Print Bcell.Row & " 60"
                iTo = Bcell.Offset(0, 5)
                iSubject = "FIRST REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -3)
                iBody = "Dear all," & vbCrLf & vbCrLf & _
                 "IN No. " & Bcell.Offset(0, -3) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
                 Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
                 Bcell & " . Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
                 vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "____________Department" & _
                 vbCrLf & "Company Pte Ltd."
                
                ImportanceLevel = olImportanceNormal
                SendEmail
            End If

            If DateDiff("d", Now(), Bcell) = 30 Then
'                Debug.Print Bcell.Row & " 30"
                 iTo = Bcell.Offset(0, 5)
                 iSubject = "SECOND REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -3)
                iBody = "Dear all," & vbCrLf & vbCrLf & _
                 "IN No. " & Bcell.Offset(0, -3) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
                 Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
                 Bcell & " . Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
                 vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "______________Department" & _
                 vbCrLf & "Company Pte Ltd."
                 
                 ImportanceLevel = olImportanceNormal
                 SendEmail
            End If

            If DateDiff("d", Now(), Bcell) = 7 Then
'               Debug.Print "ROW: " & Bcell.Row & " 7"
                iTo = Bcell.Offset(0, 5)
                iSubject = "FINAL REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -3)
                iBody = "Dear all," & vbCrLf & vbCrLf & _
                 "IN No. " & Bcell.Offset(0, -3) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
                 Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
                 Bcell & " . Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
                 vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "____________ Department" & _
                 vbCrLf & "Company Pte Ltd."
                
                ImportanceLevel = olImportanceHigh
                SendEmail
            End If
        End If
        
            iTo = Empty
            iSubject = Empty
            iBody = Empty
    Next Bcell

End Sub

Private Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
        
    With OutMail
        .To = iTo
        .CC = "[e-mail]" & "; [e-mail]"
        .BCC = ""
        .Subject = iSubject
        .Body = iBody
        .Importance = ImportanceLevel
        .Send
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

推荐答案

您好SakuraiHiro，



根据您的描述，您可以编写一个简短的vbscript来启动Excel，加载您的工作簿和使用Application.Run来运行宏。让你的计划任务运行vbscript。



欲了解更多信息，请参阅
使用Excel vba宏来运行Windows计划任务



免责声明：此回复包含对第三方万维网站点的引用。 Microsoft提供此信息是为了方便您。 Microsoft不控制这些网站，也未测试在这些网站上找到的任何软件或信息;因此，Microsoft不能就其中发现的任何软件或信息的质量，安全性或适用性做出任何陈述。使用互联网上的任何软件都存在固有的危险，微软提醒您在从互联网上检索任何软件之前确保您完全了解风险的
。



感谢您的理解。

Hi SakuraiHiro,

According to your description, you could write a short vbscript which launches Excel, loads your workbook and uses Application.Run to run the macro. Have your scheduled task run the vbscript.

For more information, please refer to Using Excel vba Macro to be run through Windows Schedule Task

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Thanks for your understanding.

这篇关于VBA- excel电子邮件提醒帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！