vba - 打开问题时自动运行

Question

首先，在 VBA 编程方面，我不是很精通。过去我用 Java、C++、C# 和其他一些语言做过很多编程，所以我对一般的编程并不完全陌生。

我当前的问题是，我有一个库存 list ，我希望在某个数量达到某个值后通过电子邮件发送通知。电子邮件通知已设置并且工作正常，除非工作表关闭然后重新打开，如果值未超过先前设置的值，将再次发送通知。例如，如果数量等于或低于 1，则会通过电子邮件发送通知。如果在保存和关闭工作表之前该数量没有更改为 2+，则下次打开工作表时，将再次发送通知。

我想限制这一点，以便只有在第一次更改数量时才会发送通知。

下面的代码:

Public Function EmailNotification(model As String, color As String, cell1 As Range)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range


    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup

        If cell1.Value <= 1 Then

            'For Each cell In Columns("R").Cells.SpecialCells(xlCellTypeConstants)
                'If cell.Value Like "?*@?*.?*" And _
                    'LCase(Cells(cell.Row, "S").Value) = "yes" Then

                       Set OutMail = OutApp.CreateItem(0)
                       On Error Resume Next
                       With OutMail
                           .To = "username@domain.com"
                           .Subject = "Excel Notification: Toner Renewal"
                           .Body = "Dear Team," & _
                               vbNewLine & vbNewLine & _
                               "Please prep an order for " & color & " toner for a Dell " & model & vbNewLine & vbNewLine & _
                               "Quantity Remaining: " & cell1 & vbNewLine & vbNewLine & _
                               "Notification Sent: " & Now() & " from " & ActiveWorkbook.FullName


                           .Send
                       End With
                       On Error GoTo 0
                       Set OutMail = Nothing
                   'End If
               'Next cell

        End If

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Function

cell1 是在函数中检查的数量。

我不确定是否有办法在启动时停止此自动运行，然后在更改其中一个数量时允许它再次运行。

在此先感谢您的帮助!

Answer 1

我会在您的工作簿中取出一个单元格(例如 cell2 )并存储一个 bool 值来说明电子邮件是否已经发送过一次。

你的函数看起来像这样:

Public Function EmailNotification(model As String, color As String, cell1 As Range, cell2 As Range)

  Dim notifSent As Boolean: notifSent = cell2.Value '<-- get the value of cell2
  If cell1.Value <= 1 And Not notifSent 'if quantity below threshold and no notification yet sent
      'you send the email for the first time...
      cell2.Value = True 'and you set this information in your cell
  ElseIf cell1.Value > 1 And notifSent 'if quantity above threshold and notification has been sent before
      cell2.Value = False 'reset notification to false
  End If