How to make Excel e-mail me if this condition is met?

[Marknot]

Hi there,

I am very new to Excel VBA and I would like to learn more about it. At the moment, I have something urgent to do using VBA and I would appreciate your help very much!

So, I have set up formulas to calculate time left to end of contracts, displaying how many days are left (D2:D199).

I would like Excel to notify few people via Outlook once the days left are 7 or fewer.

I read threads with similar issue as mine, but I always did something wrong and it didn't work. Now it is no time left for me to sort it out myself. Could you please provide me an exact solution?

THOUSAND THANKS

 

[TiaXL]

sub email()
 
Dim outapp As Object
Dim outmail As Object
 
if range("B6") <7 then
   Set outapp = CreateObject("Outlook.Application")
Set outmail = outapp.createitem(0)
With outmail
        .To = ""
         .CC = ""
        .Subject = "        
        .body = ""
        .Attachments.Add ""
        .display
        SendKeys "%{s}", True 
End With
Application.Wait (Now + TimeValue("00:00:05"))
SendKeys "%{s}", True 
Set outapp = Nothing
Set outmail = Nothing
 
endif

how often you run it is up to you and you obviously need to correct the logic behind the check.

 

[Marknot]

sub email()
 
Dim outapp As Object
Dim outmail As Object
 
if range("B6") <7 then
   Set outapp = CreateObject("Outlook.Application")
Set outmail = outapp.createitem(0)
With outmail
        .To = ""
         .CC = ""
        .Subject = "        
        .body = ""
        .Attachments.Add ""
        .display
        SendKeys "%{s}", True 
End With
Application.Wait (Now + TimeValue("00:00:05"))
SendKeys "%{s}", True 
Set outapp = Nothing
Set outmail = Nothing
 
endif

how often you run it is up to you and you obviously need to correct the logic behind the check.

Thank you a lot for the fast reply!

The logic should be

"if range (D2:D199) <7 then..." , right? I want to verify whether there is a number equal or smaller than 7 in the range D2:D199.

A further question is when is the e-mail sent. Can it be sent automatically when the condition is met?

Sorry for the potentially stupid questions but I am absolutely new to this

 

[Marknot]

Is someone able to answer my questions?

 

[TiaXL]

You need to set it up to run when you want to run it.

I would make it a scheduled task and set it to run as often as you want, base don the data I assume once a day would do it ?

 

[VoG]

This may help you http://www.rondebruin.nl/mail/change.htm