Hi
I have a workbook that I want to send an email from every hour. I have managed this but it randomly opens another workbook (not linked to this one and not always the same one).
Any ideas why this occurs?
email upload.xls
Thanks
George
Hi
I have a workbook that I want to send an email from every hour. I have managed this but it randomly opens another workbook (not linked to this one and not always the same one).
Any ideas why this occurs?
email upload.xls
Thanks
George
Well, the only link I see is to Plu excel.xlsm in g:\public\planning\hellinic
Capture.jpg
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
First, you need to clean up your workbook, there are too many un-used subs and even modules, that it just isn't worth someone else's time to dig thru them all.
As a bonus, that cleanup might just fix your problem.
@ everybody:
I couldn't even close Excel normally with thatbook open. Don't enable Macros if you open it.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Ok thanks. I did notice there were a lot of unused subs/ modules but I left them in if that might be the problem. I never had an issue with it closing though. This particular workbook changed many times over the years hence the surplus subs. I will tidy up.
Thanks for taking the time to reply.
Last edited by dode; 06-30-2015 at 10:37 PM.
Comment out some key line(s) in the sub, then test the book before deleting that sub.
Add the words "Option Private Module" at the top of the Module, then test the Workbook. IF it works, Export the module, only then delete the module and retest the book.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
email upload.xls
With modules/subs deleted.
I will run it all day and let you know what happens!
CU L8R
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
It's always necessary to disable application.ontime instructions before closing a workbook.
All those separate modules for every day of the week are redundant.
See more: http://www.snb-vba.eu/VBA_Application.OnTime_en.html
Seems to have worked better today, so tidying up seems to have helped.
Thanks for your help
Started doing it today again. I am really at a loss.
Any ideas?
ThanksPrivate Sub Workbook_Open() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("run macro") Application.OnTime Now + TimeValue("00:05:00"), "update_2" Application.OnTime TimeValue(ws.Range("e3").Text), ws.Range("g3"), Text Application.OnTime Now + TimeValue(ws.Range("h3").Text), ws.Range("g3"), Text Application.OnTime TimeValue(ws.Range("f3").Text), "Closebook" End Sub Public Sub CancelOnTime() Application.OnTime TimeValue(ws.Range("f3").Text), "update_2", , False Application.OnTime TimeValue(ws.Range("f3").Text), ws.Range("g3"), Text, , False End Sub
George
Last edited by SamT; 07-02-2015 at 10:26 AM. Reason: changed HTML Code Tags to VBA Code tags.
If you do nothing with the suggestions we make why would we offer any ?
In the Workbook_BeforeClose sub, cancel all the OnTimes.
Example
This example runs my_Procedure 15 seconds from now.
This example runs my_Procedure at 5 P.M.Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
This example cancels the OnTime setting from the previous example.Application.OnTime TimeValue("17:00:00"), "my_Procedure"
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _ Procedure:="my_Procedure", Schedule:=False
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
From the code you posted in Post #1`3, it looks like you need 4 Ontime Cancels in the BeforeClose sub.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ