After a Cell Value changed and Saved then Automatically send Email

[kyleJohnson]

Hi,
I'm new to VBA. I wrote a code to send an email if there is a cell value change in column F, and the code works great. But I want to make sure the workbook is saved prior to sending out the email. The email text body must include the reference cell value, which is the value from column A of value changed row. Please do not use msg, it will not work with the workbook. The workbook has a form and it will load the input value to the designated cells. The code works fine with the form. But if someone goes into the sheet and manually update the value, the email will send out to users, which I don't want until the file is saved. Please help! Thanks in advance.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim s1, s2, s3, s4, s5, s6 As Range
Set s1 = Range("F1310:F1334")
Set s2 = Range("F1426:F1450")
Set s3 = Range("F1339:F1363")
Set s4 = Range("F1455:F1479")
Set s5 = Range("F1368:F1392")
Set s6 = Range("F1397:F1421")
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
'find value changed in column F
If Intersect(Target, Union(s1, s2, s3, s4, s5, s6)) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value <> "" Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xMailText As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
'index value from column A of the row that value changed to included in mail body
xMailText = Target.Offset(, -5).Value
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"Invoice Received for " & xMailText & vbNewLine & vbNewLine & _
"Thanks" & vbNewLine & vbNewLine & _
"Mr. J"
On Error Resume Next
With xOutMail
.To = "test@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Invoice Received"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End If
End Sub