VBA Excel

[sheetname]

I am trying to remind my self for due dates by using below code.After i run it sends me emails for all cases when date is achieved but when i put another due date it did not work if i do not run again.Please i need some help.
Option Explicit

Sub email()

Dim r As Range
Dim cell As Range
Set r = Range("A361:A370")

For Each cell In r

If Date - cell.Value = 30 Then

Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant

Email_Subject = "Reminder"
Email_Send_From = "rdube02@gmail.com"
Email_Send_To = "rdube02@gmail.com"
Email_Cc = ""
Email_Bcc = ""
Email_Body = "Please remind "

On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With

End If

Next


Exit Sub

debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[ranman256]

it only checks today vs the date in cells.
[If Date - cell.Value = 30]

How are you issuing a new due date?

 

[sheetname]

in A column (A362) i add another date which correspond 30 days before today just to test it and i do not see any email.If i click run macro it works.So my point is how to make running automatically?

 

[MARK858]

Try putting the (untested) code below in the worksheet module (right click the sheet tab and click view code). You will still need the other macro.
I have also corrected the dim statements

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A361:A370")) Is Nothing and Target.Count = 1 Then
        Application.EnableEvents = False

        If Date - Target.Value = 30 Then

            Dim Email_Subject As String, Email_Send_From As String, Email_Send_To As String, _
            Email_Cc As String, Email_Bcc As String, Email_Body As String
            Dim Mail_Object, Mail_Single

            Email_Subject = "Reminder"
            Email_Send_From = "rdube02@gmail.com"
            Email_Send_To = "rdube02@gmail.com"
            Email_Cc = ""
            Email_Bcc = ""
            Email_Body = "Please remind "

            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            With Mail_Single
                .Subject = Email_Subject
                .To = Email_Send_To
                .cc = Email_Cc
                .BCC = Email_Bcc
                .Body = Email_Body
                .send
            End With

        End If
        Application.EnableEvents = True
    End If
    Exit Sub

debugs:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[sheetname]

Hello ,
First thanks for you help,
When you say i need other macro do you mean my first code i posted or i need to use just your code?
Thanks.

 

[MARK858]

You need your original macro for doing the initial run to loop through the range then you need the code that I posted to run when you make changes to a cell (please note that I have made a small addition to the code that I posted).

 

[sheetname]

You need your original macro for doing the initial run to loop through the range then you need the code that I posted to run when you make changes to a cell (please note that I have made a small addition to the code that I posted).

Hello,
Thanks a lot for your help,it worked perfectly.

 

[MARK858]

Happy it helped and welcome to the board.

 

[sheetname]

Hello Mark,sorry but i have another question,
Until now everything works fine but also i would like that in email body or email subject to see which customer (Customers number id is in B column) had achieved due date because in that way i do not need to see manually from which row from with date pass the email was sent and which customer id it correspond.
Thanks.

 

[MARK858]

Try...

.Body = Email_Body & " Customer ID is " & Target.Offset(, 1).value