Auto Send Row by Email when text value changes

sonny.thind

New member
Joined
Aug 24, 2011
Messages
7
Reaction score
0
Points
0
Location
London, Ontario
Hi Masters!

I have this spreadsheet which I am updating on a daily basis. The Formula in 'J2' updates based on the arrival time 'C2' to either "early" or "late" or "on time". I needed a way to send an email to multiple sources when the value say changes to "late" or "early" and The Whole Row needs to be copied and sent out in the body of the email.I have seen this Sheet macro around by Ron De Bruin but I cant seem to customize this to my sheet. I keep getting an error...


Please let me know what I am doing incorrectly?
see code below..


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim Cell As Range
    Dim rngCheck As Range
     uRows = Sheets(1).UsedRange.Rows.Count
     Set rngCheck = Intersect(Target.EntireRow, Range("J2:J" & uRows))
    If Not rngCheck Is Nothing Then
        For Each Cell In rngCheck.Cells
            If Cell = "Late" Then
                Dim OutlookApp As Object
                Dim Mess As Object, Recip
                Recip = Sheet(2).Range("B8")
               Set OutlookApp = CreateObject("Outlook.Application")
               Set Mess = OutlookApp.CreateItem(olMailItem)
               With Mess
                    .Subject = "Subject"
                    .Body = Cell.EntireRow
                    .To = Recip
                    .Display
                End With
                Exit For
            End If
        Next Cell
    End If
    
End Sub
 

Attachments

  • BOOks3.xlsm
    29 KB · Views: 45
Top