VBA CODE

[sheetname]

Hello ,Below i have VBA code i created with forum help to keep tracking the customers in time for their payments. This code runs well but i am getting a problem. Because i work only three days i need to keep checking also in the days i miss because it is depending on due date.I need it to keep checking even i was not working yesterday for example.
Any suggestion?
Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A363:A400")) 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 = "example@eamil.com"

Email_Send_To = "example@email.com”

Email_Cc = " "

Email_Bcc = ""

Email_Body = "Please, Can you check if "


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 & " Customer with name " & Target.Offset(, 2).Value & " and Id - " & Target.Offset(, 1).Value & " have send any update about their payment."

.send

End With

End If

Application.EnableEvents = True

End If

Exit Sub

debugs:

If Err.Description <> "" Then MsgBox Err.Description

End Sub

 

[Dan_W]

The code above is predicated on there being some change to one of the cells in range A363:A400. So if you're not accessing the workbook, and not making any changes to that paricular range on that particular worksheet, it isn't going to run.

If it were me, I would:

1. take that code and put it in a separate subroutine;
2. call that subroutine from this Worksheet_Change event so that it still functions as it does currently;
3. create a vbscript file that executes excel and runs that new subroutine at certain times of the day using Windows Task Scheduler.

You would need to make some changes to the subroutine, though, because it wouldn't know which of the customers you want to to check. If you want it to check all customers, then you could just cycle through the range A363 to A400... and if you were going to do that, why have the code above triggered by the Worksheet_Change event at all?

 

[sheetname]

Hello Dan,
Thanks for your suggestion,would be better if would send me some all those changes in the code because i am beginner.
Thanks.