How to run macro in excel only on the recently updated cells

Question

I have macro which sends email when I enter Reject on particular cells.

I have added this so that that macro runs whenever something changes in that particular column

  Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UpdatedCells As Range
    Set UpdatedCells = Intersect(Me.Range("G3:G60"), Target)

    If Not UpdatedCells Is Nothing Then
        PSEC UpdatedCells
    End If
End Sub

Sub PSEC(ByVal UpdatedCells As Range)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In UpdatedCells
       If cell = "rejected" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "xxxx"
                .Body = "xxx"
                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

The problem is, it sends email to previously updated cells also. Is there a way to run macro only on the recently modified cells?

Since you are working with `Worksheet_Change`, you may want to read [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, May 18 '21 at 06:40
`I have macro which sends email when I enter Reject on particular cells.` Is it `Reject` or `rejected`? ;) — Siddharth Rout, May 18 '21 at 10:05
Your code works for me. Try this. In VBE press CTRL + G to launch the Immediate window. Type `Application.EnableEvents = True` and press ENTER key. Now try the code. Also you may want to change `If cell = "rejected" Then` to `If UCase(Trim(cell.Value2)) = "REJECTED" Then` . This will ensure that the code will run for REJECTED, Rejected, REjecTED etc etc i.e it becomes case insensitive. It also remove and leading and trailing spaces if any — Siddharth Rout, May 18 '21 at 11:24
@SiddharthRout Thanks for the response, I have enabled events but i don't know why it's not running. — humantorch47, May 19 '21 at 03:04
@Pᴇʜ, It worked, I changed this `.To=cell.value` to column holding the email address.Thanks for your response! — humantorch47, May 20 '21 at 11:41

score 1 · Accepted Answer · answered May 18 '21 at 06:31

1

First you need to get the intersection with your target set to a variable UpdatedCells so you know which cells in the desired range were updated.

Those UpdatedCells then need to be passed to your procedure PSEC so you can process all the updated cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UpdatedCells As Range
    Set UpdatedCells = Intersect(Me.Range("G3:G60"), Target)

    If Not UpdatedCells Is Nothing Then
        PSEC UpdatedCells
    End If
End Sub

Your procedure PSEC needs to be able to receive a parameter, so you can loop through all changed cells and process the data in that rows to send an email.

Public Sub PSEC(ByVal UpdatedCells As Range)
    Dim Cell As Range
    For Each Cell In UpdatedCells
        If Cell = "Rejected" Then
            ' send your email here!

            Debug.Print ThisWorkbook.Worksheets("Feedback").Cells(Cell.Row, "B").Value
            ' This will print the value of column B of the recent changed cell (just as an example how to access data in this row
        End If
    Next Cell
End Sub

answered May 18 '21 at 06:31

Pᴇʜ

56,719
10
49
73

Hi Peh, thanks for your response. When modified the code the macro is not sending any mails. I have added the whole to the question – humantorch47 May 18 '21 at 08:55
@humantorch47 debug your code. Go through it step by step using F8 and check where exactly it does not do what you expect. Actually your code does not send any emails because the code for sending is still missing. You create a new message but you never send anything. – Pᴇʜ May 18 '21 at 11:29
@Peh, I have purposefully redacted the mailing part from the above code. – humantorch47 May 19 '21 at 02:52
@humantorch47 well we can only help with code we see. And I see not how the code you show will send an email. So obviously we cannot tell *why* your code does not send emails because you don't show it. See [mcve]. – Pᴇʜ May 19 '21 at 06:18

How to run macro in excel only on the recently updated cells

1 Answers1