After a Cell Value changed and Saved then Automatically send Email

kyleJohnson

New Member
Joined
Feb 28, 2019
Messages
1
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,897
Members
409,610
Latest member
db321

This Week's Hot Topics

Top