Hello everybody

I would need some help for a tool I would like to create

Here is the situation:

I'm working in a young company, producing movies in France. We got many artists coming back and forth. Some artists have short renewable fixed-term contract (2 weeks usually). As there are many artists, with various contract duration, I have to track down when contracts are almost finished. When a contract is almost over, I have to ask by mail the artists if he still wants to work with us and I have to ask my boss if he still wants to employ the artist.

I was doing this manually. But it was becoming tiring, and I know there are tools to automate this.

So, I wrote a macro I found on the net, allowing me to send a template mail by clicking button.

Now I'm looking for a way to automate this. The macro would daily check cells : if status in cells says ''contract almost finished", it would send a personal mail to employee and boss.

I saw that it's possible with Windows scheduled tasks.

Here is the macro for the button i'm using now :

Sub Envoi_mail_renouvellement_Guillaume()

Dim config As CDO.Configuration
Dim email As CDO.Message

Set config = New CDO.Configuration
With config.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = XX
.Item(cdoSendUserName) = XX
.Item(cdoSendPassword) = XX
.Item(cdoSMTPServerPort) = XX
.Update
End With

Set email = New CDO.Message
With email
Set .Configuration = config
.From = XXX
.To = XXX
.Subject = "Studio Angoulême - Renouvellement contrat Guillaume Gilbaud"
.TextBody = "Salut xxx, le contrat de Guillaume arrive bientôt à terme (fin le 30/04/2016). Quelle suite est prévue pour lui ? "
.Send
End With

End Sub


I tried to add conditions and schedule the excel sheet as a task :

If Range("F" & iRow).Value < "à renouveler ?" Then

but it doesn't work.

If somoene can help, I would be very grateful :)

Thanks