title: Send an email based on value in a cell only when workbook is saved
body: <p>I have a cell B8 that checks to see if data has been entered in a range of cells for the day and outputs a number depending on the count. It checks for blank entries and obviously at the start of filling out the sheet all the cells for the day will be blank, I want it to only perform the check once the worksheet has been saved.</p>
  
  <p>The code I've managed to Frankenstein together prepares an email the instant the condition is met and I'm not sure how to change it to suit my needs.</p>
  
  <pre><code>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" &amp; vbNewLine &amp; vbNewLine &amp; _
            "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 &gt; 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." _
       &amp; vbLf &amp; Err.Number _
       &amp; vbLf &amp; Err.Description
  
  End Sub
  </code></pre>