Email excel data range when target cell changes

Question

This macro works on line 5 ,so i need this macro to work on all lines in one sheet instead of one macro for each line. Row X and email range A:L are copy paste in all lines i.e.( X1 A1:L1 | X2 ,A2:L2 ...)

 Dim X5 As Variant

    Private Sub Worksheet_Change(ByVal Target As Range)
         If Range("X5").Value = 1 And X5 <> 1 Then

    ActiveSheet.Range("A5:L5").Select


    ActiveWorkbook.EnvelopeVisible = True


        With ActiveSheet.MailEnvelope
         .Introduction = " send thru macro "
         .Item.To = "email@gmail.com"
         .Item.Subject = "ALERT"
         .Item.Send
    End With
    End If
         X5 = Range("X5").Value

    End Sub

If you can store the previous value in a hidden column on the sheet then you wouldn't need the variable at all. — Jerry Jeremiah, Nov 21 '13 at 10:05
Dont now about variable but thanx Jerry for solving my previous problem ,as you can see im taking one step at a time,maybe you would be so kind to solve this one to. — Deskom88, Nov 21 '13 at 11:17

score 1 · Answer 1 · edited May 23 '17 at 11:43

Not sure if you got your answer or not so I am attempting to answer this question.

To make it flexible for any row, you can store the row of the current cell in a variable using Target.Row and then simply use that to construct your range.

Also to understand how Worksheet_Change works, you may want to see THIS

Is this what you are trying?

Dim X5 As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    '~~> Check if the chnage happened to multiple cells
    If Target.cell.CountLarge > 1 Then Exit Sub

    Dim Rw As Long

    '~~> Get the row number of the cell that was changed
    Rw = Target.Row

    If Range("X" & Rw).Value = 1 And X5 <> 1 Then
        Application.EnableEvents = False

        Range("A" & Rw & ":L" & Rw).Select
        ActiveWorkbook.EnvelopeVisible = True

        With ActiveSheet.MailEnvelope
             .Introduction = " send thru macro "
             .Item.To = "email@gmail.com"
             .Item.Subject = "ALERT"
             .Item.Send
        End With
    End If
    X5 = Range("X" & Rw).Value

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Email excel data range when target cell changes

1 Answers1