Send Email From Excel At Specific Time 1

[VincentCrimmins]

Hi,

I've used the code from the FAQ to send an email from Excel.

Is it possible to set it up that it sends the email at, for example, 17:00, each day?

Thanks,

Vincent

 

[xlbo]

Will the file be open at 17:00 every day?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

Apologies, yes it will be open

 

[xlbo]

Have a look at the ontime method in VBA

How it is implemented depends on how the workbook is openend and whether anyone will need to actually use it while it is open and waiting for 5pm to come around

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

Great. Will I have to put the code into the 'ThisWorkbook' Object?

Thanks,

 

[xlbo]

As I say - depends on the process that is around how the workbook is opened and whether there is any user interaction after it is opened...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

The workbook receives data from an external source that updates automatically every hour.

I want the worksheet to send an email at 1 minute past the hour.

 

[xlbo]

How does it receive the data? via vba? If this is done in a seperate procedure, you may need to look at using doevents to allow both things to happen...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

No, it's a DDE link to individual cells, it's not a separate procedure

 

[xlbo]

In which case you may still need to use DoEvents to allow the DDE link to work as otherwise the macro will freeze whatever else is happening (as far as I know - not actually implemented this kind of thing with DDE before)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

Where can I find out more about using 'DoEvents'?

 

[xlbo]

DoEvents is pretty straightforward - it just allows other processes to happen whilst vba code is running:

From VBA help:

DoEvents Function


Yields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VincentCrimmins]

That's great, thanks a lot for your help!