Ask Your Question
4

How can I use Excel VBA to send an email, utilizing information from one sheet and an email address from another sheet, whenever a cell in a particular column is modified?

asked 2022-12-24 11:00:00 +0000

devzero gravatar image

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2023-01-22 00:00:00 +0000

scrum gravatar image

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.

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss

Add Answer


Question Tools

Stats

Asked: 2022-12-24 11:00:00 +0000

Seen: 14 times

Last updated: Jan 22 '23