How to send email if a certain cell is modified in Excel?

This article is talking about sending email through Outlook if any cell in a certain range is modified in Excel.

Send email if cell in a certain range is modified with VBA code

Send email if cell in a certain range is modified with VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

If you need to create a new email automatically with active workbook attached when cells in range A2:E11 are modified in a certain worksheet, the following VBA code can help you.

1. In the worksheet which you need to send email based on its modified cell in a certain range, right-click the sheet tab and then click the View Code from the context menu. See screenshot:

2. In the popping up Microsoft Visual Basic for Applications window, please copy and paste below VBA code into the Code window.

VBA code: Send email if cell in a specified range is modified in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("A2:E11")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
        xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
            " in the worksheet '" & Me.Name & "' were modified on " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " by " & Environ$("username") & "."

        With xMailItem
            .To = "Email Address"
            .Subject = "Worksheet modified in " & ThisWorkbook.FullName
            .Body = xMailBody
            .Attachments.Add (ThisWorkbook.FullName)
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Notes:

1). In the code, A2:E11 is the range you will send email based on.

2). Please change the email body as you need in xMailBody line in the code.

3). Replace the Email Address with the recipient email address in line .To = "Email Address".

4). Change the email subject in line .Subject = "Worksheet modified in " & ThisWorkbook.FullName.

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

From now on, any cell in range A2:E11 is modified, a new email will be created with the updated workbook attached. And all specified fields such as subject, recipient and email body will be listed in the email. Please send the email.

Note: The VBA code is only working when you use Outlook as your email program.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

20 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
laya · 2 months ago
@Ioana hi Laona. do u find out how can do this?
- Reply
To post as a guest, your comment is unpublished.
Gurpreet singh · 2 months ago
@crystal What if we only want the updated comments in that cell an not the entire cell value
It should show only the latest comments added in the cell
- Reply
To post as a guest, your comment is unpublished.
HumAnCK · 3 months ago
Hello, i was trying to send emails on my sheet using one value changed on the sheet. If on column H the status will be changed to ="4" the Order ID in the left should be send to one user. The sheet works dynamicly so i have a Range from D9:D140 where order id's are stored and status changes are made in the same range on H9:H140. How can i reach the goal to do so and send the Order ID to my customer when status has been changed to ="4" ?
- Reply
To post as a guest, your comment is unpublished.
Owen · 8 months ago
@Pawel Can you please explain this further. How do you repeat the code to send to a different email based on another range being modified. I have tried copy and pasting the code below and changing as per your comment, but still only the first range seems to execute the command and write the email.
- Reply
To post as a guest, your comment is unpublished.
Pawel · 1 years ago
@joseph Edit linie:
1). In the code, A2:E11 is the range you will send email based on.
and
3). Replace the Email Address with the recipient email address in line .To = "Email Address".

Works fine.
- Reply
To post as a guest, your comment is unpublished.
joseph · 1 years ago
@james2706 Any help on this request? I am having same issue. I want to add multiple email address per row, but when I change one row, the entire worksheet changes. How can I limit the changes only to one row?
- Reply
To post as a guest, your comment is unpublished.
james2706 · 1 years ago
Hello. How do I modify the code so that an e-mail is sent to another e-mail address if another range of cells is edited?
- Reply
To post as a guest, your comment is unpublished.
Ioana · 2 years ago
hi; is there a way to change text displayed using information from other cells (from first row and first column)? for example, if I change cell K15, I want to include in the message info on cells A15 and K1? what should I change in the code? thank you very much
- Reply
To post as a guest, your comment is unpublished.
crystal · 2 years ago
@jimmy@globeways.com Hi Jimmy Joseph,
Please replace the line ".Display" with ".Send". Hope I can help. Thanks for commenting.
- Reply
To post as a guest, your comment is unpublished.
jimmy@globeways.com · 2 years ago
I have tried above VBA code: Send email if cell in a specified range is modified in Excel. This VBA works for me except sending email. When the data is modified in the given range an email is automatically generated with modified cell details. However, the email is not automatically sending to the recipient and the user has to click send button in the email. What I am looking here is, the email has to send to the recipients automatically when it is generated. Please help me to provide a code for this. Many thanks
- Reply
To post as a guest, your comment is unpublished.
crystal · 2 years ago
@Sonu Hi,
You can try the below VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xRg = Range("A2:E11")
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If Not xRgSel Is Nothing Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
xRgSel.Value & _
" in the worksheet '" & Me.Name & "' were modified on " & _
Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
" by " & Environ$("username") & "."

With xMailItem
.To = "Email Address"
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Reply
To post as a guest, your comment is unpublished.
sagar · 2 years ago
if i have to send that entire row then?
- Reply
To post as a guest, your comment is unpublished.
Brad · 2 years ago
Great information.
Question regarding the information that can be added to the email.
Using your example above....

If you had a value in F4, how would you include the F4 Value in the email that was generated when D4 was modified??
- Reply
To post as a guest, your comment is unpublished.
Sonu · 2 years ago
If i want to send the cell value instead of the address..then what shall I change in the code?
- Reply
To post as a guest, your comment is unpublished.
crystal · 2 years ago
@Mahadev Hi,
Can't help with this.
- Reply
To post as a guest, your comment is unpublished.
crystal · 2 years ago
@sjones Hi Sissy Jones,
The method in this article: How to automatically send email based on cell value in Excel?
https://www.extendoffice.com/documents/excel/4656-excel-send-email-based-on-cell-value.html may help you solve the problem.
- Reply
To post as a guest, your comment is unpublished.
Mahadev · 2 years ago
Dear Admin,

I need your help,

I have an excel to monitor daily work done details by our worker from the field, so is this possible to trigger a mail from excel sheet if that guy failed to update the data in that excel sheet on given time.
- Reply
To post as a guest, your comment is unpublished.
sjones · 2 years ago
I was able to create the macro, however I'm having a problem. I would like to automatically send an email when a cell reaches a certain threshold. The cell is a formula. When the calculation sum goes below the said threshold it doesn't do anything; however, if I type directly into the cell it will process the macro as planned. Does the formula mess up the macro?
- Reply
To post as a guest, your comment is unpublished.
crystal · 3 years ago
@pakxrohn@gmail.com Dear Pankaj Shukla,
Post your Excel question to our forum: https://www.extendoffice.com/forum.html to get more supports about Excel from our Excel professional.
- Reply
To post as a guest, your comment is unpublished.
pakxrohn@gmail.com · 3 years ago
I am stuck in below VB code. I am trying to get email notification to the user where the data has been changed. Email is working but when I make any change email initiated at once but I want email when excel sheet is saved and closed after making all the changes to all users who impacted. Also this should be working for any of the sheets in entire excel workbook.

Please help...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'****Declaration of objects and variables******

Dim xRgSel As Range Dim xOutApp As Object Dim xMailItem As Object Dim xMailBody As String Dim mailTo As String

On Error Resume Next

Sheets("TargetSheet").Range("TargetRange").Select

Application.ScreenUpdating = False Application.DisplayAlerts = False

'Set xRg = Range("A" & Rows.Count).End(xlUp).Row

Set xRg = Range("A2:DA1000")
Set xRgSel = Intersect(Target, xRg)

ActiveWorkbook.Save
'**********Outlook Application Opening***********

If Not xRgSel Is Nothing Then

Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)

xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
" in the worksheet '" & Me.Name & "' were modified on " & _
Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
" by " & Environ$("username") & "."
'***********Finding recipient List************

If Cells(xRgSel.Row, "A").Value = "Pankaj" Then

mailTo = "pank12***@gmail.com"

End If

If Cells(xRgSel.Row, "A").Value = "Nitin" Then

mailTo = "pank****@gmail.com"

End If

If Cells(xRgSel.Row, "A").Value = "Chandan" Then

mailTo = "pakxro**@gmail.com"

End If
'*************Email composing*************

With xMailItem

.To = mailTo
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
'.Attachments.Add (ThisWorkbook.FullName)
.Display

End With

Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing

End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Reply

Login to your account

Password Reset