Need Help with code to email - Giving me a Headache.

peez

New Member
Joined
Jan 18, 2005
Messages
15
What I am trying to do is when a worksheet opens if the value in column L (Due Dates) is greater than M6(Today's date) and if column N(not emailed already is not yes) then send an email. The problem is the only way I have been able to make it work is by using SelectionChange. The sheet auto calculates all information so changes aren't really made just calculated. I have conditional formatting that turns cell red if they are overdue. What I want to do is if any cell in column L is past M6 to send an email unless column N indicates YES. Here is the code I have so far:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    
    If (Me.Range("A" & Target.Row).Value <= Me.Range("e1").Value) Then
        If (StrComp(Me.Range("H" & Target.Row).Value, "Yes", vbTextCompare) <> yes) Then
        Send_Email
        Else
        Exit Sub
        End If
    Else
    Exit Sub
    End If
    

Application.EnableEvents = True
End Sub

Any and All help is appreciated. Thanks in Advance. This problem has been racking my brain
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

To capture changes that are caused by Calculation, you can use Worksheet_Calculate instead of Change.

Hope that helps,

Smitty
 

peez

New Member
Joined
Jan 18, 2005
Messages
15
Thanks for the reply but,

When I used Calculate it didn't work. I have tried this code on another worksheet that physically takes changes and it works but when I try calculate in my sheet it does nothing.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post what you came up with Calculate?

Note that the Calculate event doesn't use the Target argument, so you have to test differently.

Smitty
 

peez

New Member
Joined
Jan 18, 2005
Messages
15
Just to let you know I am a beginner in VBA but here is what I did:

Code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

    
    If (Me.Range("A" & Target.Row).Value <= Me.Range("e1").Value) Then
        If (StrComp(Me.Range("H" & Target.Row).Value, "Yes", vbTextCompare) <> yes) Then
        Send_Email
        Else
        Exit Sub
        End If
    Else
    Exit Sub
    End If
    

Application.EnableEvents = True
End Sub
 

Forum statistics

Threads
1,147,688
Messages
5,742,627
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top