Reminder mail trigger only for the target row where the values are modifying

  • Dear Team,


    I am writing code for send Reminder mail to the customer .


    In sheet i am having customer, Machine and machine hours details.


    I am entering the machine hours details on Sheet "Master data", from Column L to S and In Column E to K I am using formula to calculate the values.


    Example,

    For "E10" the formula is =250 - (S10-L10). Where ever I am changing the value either S10 or L10, the calculated value is update on E10.


    If E10 value is less than 100, then I want to trigger mail only the customer who is in the row 10.


    If suppose i am entering the details on row 6 and E6 value is less than 100, then I want to trigger mail only the customer who is in the row 6.


    For that I wrote code. My code is


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. Dim r As Integer, cl As Integer
    3. If Not Application.Intersect(Target, Me.Range("L4:S999999")) Is Nothing Then
    4. r = Target.Row
    5. cl = Target.Column
    6. Call Remindermail(r, cl)
    7. End If
    8. End Sub


    But when i am checking by changing the value, the mail is not triggering.


    Can any one please help me for solving this issue.


    I attached my file here for your reference

  • A few possibilities for failure:


    Line 3: Duplicate declaration of the variable r

    Line 34: If the test fails (which it does in 5 of the 8 rows of data), no email will be created.

    Line 54: There is no range assigned the variable rng in the RangetoHTML function.


    I suggest adding Option Explicit to the top of your code modules and removing all On Error Resume Next statements to help in debugging your code.

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Dear Danglor,


    I modified the code as per your suggestion. Now Mail is created. New code is


    But i am getting error for mail attachment.


    Code
    1. 'Print '************************************************* ********
    2. 'Turns off screen updating
    3. Application.ScreenUpdating = False
    4. 'Makes a copy of the active sheet and save it to a temporary file
    5. Dim wks As Worksheet
    6. mySheet = sh.Cells(r, "C").Value
    7. TempFilePath = Environ$("temp") & "\"
    8. If Cells(r, "E").Value <= 100 Then
    9. TempFileName = mySheet & " Machine Engine, Transmission, Axle & Hydraulic Oil Service Spares.pdf"
    10. End If


    Code
    1. mySheet = sh.Cells(r, "C").Value

    Error is


    Can you please suggest me for clear this error

  • Dear


    I cleared the above said error.

    Modified Code is

    Code
    1. Dim mySheet As Integer
    2. Dim TempFilePath As String
    3. Dim TempFileName As String
    4. mySheet = sh.Cells(r, "C").Value
    5. TempFilePath = Environ$("temp") & "\"
    6. If Cells(r, "E").Value <= 100 Then
    7. TempFileName = mySheet & " Machine Engine, Transmission, Axle & Hydraulic Oil Service Spares.pdf"
    8. End If


    But now the error is coming on bellow code

    Code
    1. 'PDF Range And Format
    2. Dim FileFullPath As String
    3. FileFullPath = TempFilePath & TempFileName
    4. If Cells(r, 5).Value <= 100 Then
    5. Worksheets(mySheet).Range("B5:F30").ExportAsFixedFormat _
    6. Type:=xlTypePDF, FileName:=FileFullPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    7. End If


    Pls suggest me