Application.OnTime Datevalue

[Afro_Cookie]

found a post, Application.ontime DateValue ???, that showed how to set a specif day of the week for an application.OnTime to run, but cannot get it to work for my file.

I want my macro to run every Friday @ 9:00AM but nothing happened when I changed the values to today in order to test. Can someone please help me out.

Sub Auto_Send()

Dim NextFridayDate As Date

NextFridayDate = Date - Weekday(Date) + vbWednesday - 7 * (vbWednesday <= Weekday(Date))

Application.OnTime NextFridayDate + TimeValue("13:08:00"), "MyMacro"

End Sub

 

[Anthony47]

NextFridayDate = Date - Weekday(Date) + vbWednesday - 7 * (vbWednesday <= Weekday(Date))

This will not calculate "NextFriday" but next Wednesday... If you wish "Friday" then you should not use "vbWednesday" but "vbFriday"

And how did you set the time to "today" for testing?
Try using

Application.OnTime Date + TimeValue("13:08:00"), "MyMacro"           '<<< Use the righ Timevalue

Bye