Automatically send file daily without opening excel

[niggszo]

Hi ,
I am trying to send this file daily at 10am using the code by Ron De Bruin with 'OnTime' however it is not working. the Code runs when I run the macro manually but not automatically.
anything I am doing wrong.??
So I tried using the Private Sub Workbook_Open() to call the Macro and then using the windows scheduled tasks to open the workbook at the specified time.
However here is the glitch. I only want the file to be sent at a particular time and not every time I open the sheet as this needs to be modified by multiple users.(its on a Shared drive). and I want it to run on weekends when I am not at the desk.
how do I get this to work??

File attached

[neiluk69]

Create a separate excel sheet xyz.xls which runs the code to send the updates.xlsm when you open xyz.xls.

[niggszo]

Hi,
How will it work if I dont open the file? but I need the file sent irrespective of me opening it i.e on my day off for instance.
hope you are getting what I am stating??

[neiluk69]

Does the file run on a server, if so you could set a timer event on the xyz.xls file to run every twenty four hours, you would have to leave the file open.

Alternatively if the file (updates.xlsm) is accessed at least once a day by any individual you could add a worksheet to act as the control.

the sheet would contain in A1 last date sent (format cell as short date)
range A2 = Set this value to the earliest time it cannot be sent before, (format cell as short time)


In the open workbook event compile a script that checks the date time

' if the date in range A1 is less than today and the time on opening is greater than the time set in A2

If A1 < DATE() and time()>A2 then

Insert your script to send the file

A1=DATE() 'Updates the date to today so script won't recur

save the updates.xlms file

Else

' do nothing

Exit SUb

End if

.........................

You might want to bear in mind when using the time field that some people might be late with their contribution, so I would, from experience, add some extra time, so if the file is due to be sent out at 10.00 am daily then advise your contributors that the cut off time is 9am, if you get what I mean.

Hope this helps