Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


After a Cell Value changed and Saved then Automatically send Email

  • Kyle Johnson
  • Topic Author
  • Offline
  • New Member
  • Aelod Newydd
3 months 2 weeks ago #1462 by Kyle Johnson
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
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End If
End Sub

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to edit your message.
Moderators: jaychivoQiu hongkunJay Chivo