excel - 仅在保存工作簿时根据单元格中的值发送电子邮件

Question

我有一个单元格 B8，用于检查当天的一系列单元格中是否输入了数据，并根据计数输出一个数字。它检查空白条目，显然在填写工作表开始时，当天的所有单元格都将为空白，我希望它仅在保存工作表后才执行检查。

我设法与弗兰肯斯坦一起编写的代码会在条件满足时立即准备一封电子邮件，但我不知道如何更改它以满足我的需要。

Sub Mail_with_outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim emlto As String, emlcc As String, emlbcc As String
Dim emlsub As String, emlbody As String

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

emlto = "email@abc.def"
emlcc = ""
emlbcc = ""
emlsub = "Raw Material Projection"
emlbody = "Good Day" & vbNewLine & vbNewLine & _
          "There might be an issue with the data inputed in today's sheet"


With OutMail
    .To = emlto
    .CC = emlcc
    .BCC = emlbcc
    .Subject = emlsub
    .Body = emlbody
    .Display    '  use .Send once tested
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub





Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 10

'range with the Formula that I want to check
Set FormulaRange = Me.Range("B8")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
    With FormulaCell
        If IsNumeric(.Value) = False Then
            MyMsg = "Not numeric"
        Else
            If .Value > MyLimit Then
                MyMsg = SentMsg
                If .Offset(0, 1).Value = NotSentMsg Then
                    Call Mail_with_outlook
                End If
            Else
                MyMsg = NotSentMsg
            End If
        End If
        Application.EnableEvents = False
        .Offset(0, 1).Value = MyMsg
        Application.EnableEvents = True
    End With
Next FormulaCell

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "Some Error occurred." _
     & vbLf & Err.Number _
     & vbLf & Err.Description

End Sub

Answer 1

我会把你的逻辑放在 BeforeSave 中事件。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   'your logic goes here
End Sub

如果您只是检查范围内现在是否有内容，而之前范围完全为空，请考虑使用 COUNTA/COUNT 函数。

注释:

Occurs before the workbook is saved.

Syntax expression. BeforeSave( SaveAsUI , Cancel )

expression A variable that represents a Workbook object.

Parameters

SaveAsUI: Required, Boolean, Description: True if the Save As dialog box will bedisplayed due to changes made that need to be saved in the workbook.

Cancel: Required, Boolean, Description: False when the event occurs. If the eventprocedure sets this argument to True , the workbook isn't saved whenthe procedure is finished.