Here's an example Excel VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim EmailAddress As String
Dim EmailBody As String
Dim EmailSubject As String
Dim Mail_Object As Object
If Target.Column = 3 Then 'Column C is being monitored for changes
EmailAddress = Sheets("Email Addresses").Range("A2").Value 'Change A2 to the location of the email address cell in the Email Addresses sheet
EmailSubject = "Update Notification" 'Change the email subject to your preference
EmailBody = "A change has been made in the sheet." & vbCrLf & _
"Cell address: " & Target.Address & vbCrLf & _
"New value: " & Target.Value 'The email body includes information about the changed cell
Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
.Subject = EmailSubject
.To = EmailAddress
.Body = EmailBody
.Send
End With
Set Mail_Object = Nothing
End If
End Sub
In this example, the code is attached to the worksheet where you want the email notification to occur. It monitors changes in column C by checking if the modified cell's column is equal to 3. If so, it retrieves the email address from cell A2 in the "Email Addresses" sheet, creates an email with the specified subject and body, and sends it using Microsoft Outlook.
Make sure to enable macros and allow Outlook access if prompted when running the code. Also, modify the code as necessary to suit your needs, such as changing the monitored column, email address cell location, email subject, and body.
Asked: 2022-12-24 11:00:00 +0000
Seen: 19 times
Last updated: Jan 22 '23