Results 1 to 1 of 1

Thread: Auto Send Row by Email when text value changes

  1. #1
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0

    Auto Send Row by Email when text value changes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •