Quick Application.OnTime question...

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,291

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• Jul 16, 2009

• #2

Presumably CDO_Mail_Small_Text reschedules the macro. What code does it use?

 

Upvote 0

A

a-drift

New Member

Joined

Oct 13, 2003

Messages

28

• Jul 16, 2009

• #3

Thanks for the reply,

I don't have the CDO_Mail_Small_Text reschedule the macro. I 'assumed' this wasn't necessary because I don't have the Application.OnTime using a date, just a time.

What code would I need to add at the end of the CDO_Mail_Small_Text macro so that when it executes today at 2:47PM, it will reschedule the Application.OnTime for 2:47PM tomorrow? Will this fix also prevent it from automatically executing at midnight tonight?

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jul 16, 2009

• #4

You can specify an exact date & time to run (say in 24 hours) with:

Code:

Application.OnTime Date + 1 + TimeValue("14:47:00"),"Your Macro Name Here"

Date + 1 returns tomorrows date.

 

Upvote 0

A

a-drift

New Member

Joined

Oct 13, 2003

Messages

28

• Jul 16, 2009

• #5

I'm sorry, but I just need to get a bit clearer on your reply and what this means:

So... then the Application.OnTime DOES assume today's date even when no date parameter is supplied to it (like in my original post)?

Also... then it is possible that the Application.OnTime can be reset for tomorrow (like in your last post) within the CDO_Mail_Small_Text macro, which is in a standard module, even though the original Application.OnTime is set in the Public module?

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,291

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• Jul 16, 2009

• #6

Unless you are rescheduling the macro or reopening the workbook, your macro should only run once. I think we need to see what the called macro does.

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jul 16, 2009

• #7

a-drift said:

So... then the Application.OnTime DOES assume today's date even when no date parameter is supplied to it (like in my original post)?

Click to expand...

That is certainly what the Help file suggests. I have only ever used OnTime with a specific future time, however.

Also... then it is possible that the Application.OnTime can be reset for tomorrow (like in your last post) within the CDO_Mail_Small_Text macro, which is in a standard module, even though the original Application.OnTime is set in the Public module?

Click to expand...

Yes. When you say Public Module, do you mean the ThisWorkbook module? OnTime can be scheduled from a standard module.

 

Upvote 0

A

a-drift

New Member

Joined

Oct 13, 2003

Messages

28

• Jul 16, 2009

• #8

Yes, sorry, ThisWoorkbook, not Public.

I have inserted the additional Application.OnTime statement to the end of my CDO_Mail_Small_Text macro in my standard module, and will wait to see what happens at midnight tonight and at 2:47PM tomorrow. Here's what I added:

Application.OnTime Date + 1 + TimeValue(Sheets("E-mailTab").Range("A21").Text), "CDO_Mail_Small_Text"

I'll post again with what happens.

Thank you very much for all your help.

 

Upvote 0

A

a-drift

New Member

Joined

Oct 13, 2003

Messages

28

• Jul 20, 2009

• #9

This fix made everything work the way I needed it to work.

Thanks again for your help.

 

Upvote 0