vba code to only autogenerate 1 email please

[Kimberly47]

I have a code to autogenerate an email if a change is made to cells e11:a33. If I make several changes, I'd only like it to autogenerate the email once. How can I do this? Thanks,

<< Use Code -tags >>

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to cells E11:E33
If Not Intersect(Target, Range("E11:E33")) Is Nothing Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "LAC team," & vbNewLine & vbNewLine & _
"This LAC Event Management Macro for _ has a status update. Please review. Thanks."
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Event Planning Update"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[p45cal]

It's quite difficult, because how will the code 'know' that you've finished editing and you want an email created?

One way is to ask the user each time a change is made, for example, replacing your:

If Not Intersect(Target, Range("E11:E33")) Is Nothing Then
Call Mail_small_Text_Outlook
End If

with:

If Not Intersect(Target, Range("E11:E33")) Is Nothing Then
  If MsgBox("Create Email?", vbQuestion + vbOKCancel + vbDefaultButton2, "ProduceEmail?") = vbOK Then
    Call Mail_small_Text_Outlook
  End If
End If

This pops up a question after each change in that range, but the message has been configured so that just pressing the Enter key on the keyboard when it pops up default answers No to creating a new email (so that it's really easy to dismiss the question and continue editing the sheet). To answer OK you have to move the focus to the OK button with any cursor key, or the tab key, before pressing Enter, or use the mouse to click the OK button.

Another way is to ditch the WorkSheet_Change event altogether and add a button to the sheet which needs to be clicked by the user when he wants an email produced.