sonny.thind
New member
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..
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