PDA

View Full Version : scheduled task & can't create outlook object



andreaD
06-18-2014, 01:24 AM
Hi to everyone, I noticed a very strange behaviour with my VBA macro in Excel, never discussed in any of the other posts. I have windows 7, Outlook and Excel 2010.
I wrote some lines of code for sending automatic emails to a distribution list, using:


Dim Mail_Object As Object, Mail_Single As Variant
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)


No problem if i run the macro in the classic way (from the worksheet -> run macro -> select the name of the macro).
If I try to shedule a task on my pc for opening the xlx, then running the same macro when the file opens (code written in sub workbook_open()), it fails.
It gives me back the so famous run time error 429 (active x can't create the object). Therefore, the error is not in the code for automatically run the sending-mail macro, but in the function that previously worked fine, in particular at the line:



Set Mail_Object = CreateObject("Outlook.Application")


Why it fails only when the scheduled task run the macro??
That's very strange, isn't it?
Help me please

Thanks
Andrea

westconn1
06-18-2014, 04:32 AM
try
use application.ontime in workbook_open to run a macro after the workbook has fully opened

andreaD
06-18-2014, 05:44 AM
try
use application.ontime in workbook_open to run a macro after the workbook has fully opened

I have never used such a function, can you help me writing the code I must insert, please?
My code is


Private Sub Workbook_Open()
'...
' Some lines for filter data into the sheet
'...

If Hour(Now) = 10 And Minute(Now) = 00 And Weekday(Now, vbMonday) < 7 Then
Call automaticEmail
End If


End Sub

Thanks a lot
Andrea

westconn1
06-18-2014, 02:41 PM
try like

Private Sub Workbook_Open()
'...
' Some lines for filter data into the sheet
'...

If Hour(Now) = 10 And Minute(Now) = 00 And Weekday(Now, vbMonday) < 7 Then
Application.OnTime Now + TimeSerial(0, 0, 4), "automaticemail" ' 4 second delay
End If
End Sub some proviso, automaticemail procedure should be a public procedure in a standard module

andreaD
06-19-2014, 12:36 AM
try like

Private Sub Workbook_Open()
'...
' Some lines for filter data into the sheet
'...

If Hour(Now) = 10 And Minute(Now) = 00 And Weekday(Now, vbMonday) < 7 Then
Application.OnTime Now + TimeSerial(0, 0, 4), "automaticemail" ' 4 second delay
End If
End Sub some proviso, automaticemail procedure should be a public procedure in a standard module

Hi, I tried but still the same error, whether excel is already open or not (outlook of course is open).
The automaticEmail sub is a public routine in a standard module...
Any other suggestions?
Thanks
Andrea

andreaD
06-20-2014, 03:09 AM
In addition, when the scheduled task opens the xlx it notifies my PERSONAL.XLX is already in use, therefore asking if I want to open it in read only mode or notify.
Could this be useful? Thanks

Andrea

westconn1
06-20-2014, 03:35 AM
i added code to create instance of outlook, to an existing workbook, added a scheduled task, worked correctly without error

try in new workbook
if i does not work, post a copy of the new workbook

westconn1
06-20-2014, 03:37 AM
PERSONAL.XLX is already in useindicates the the scheduled task create a new (additional) instance of excel, which i also found when testing above

andreaD
06-23-2014, 05:32 AM
I tried to create another wb and add only the essential structure for sending mails..and it works! so there should be something "wrong" in other lines of code. I spent two days looking for the bug, unfortunately without any results.
Can you help me? I might attach the xlx..
Thanks
Andrea

westconn1
06-23-2014, 02:35 PM
I might attach the xlx..
if you can do that, i will look through it

andreaD
06-24-2014, 02:08 AM
if you can do that, i will look through it
This is the file I created.
There is a data input sheet, another one for a gantt chart and other minor sheets.
Try on your pc if it runs correctly, thanks a lot!
Andrea

westconn1
06-24-2014, 02:30 PM
sorry, i get too many errors, at startup, from version incompatibilities (i use old version of excel), to be able to test your problem

andreaD
06-25-2014, 11:09 PM
sorry, i get too many errors, at startup, from version incompatibilities (i use old version of excel), to be able to test your problem

It is a pity but thanks anyway..there is anyone that can help me quickly analyzing my code? Hope yes...thanks

snb
06-26-2014, 02:17 AM
Since you are not using a scheduled task (it only fires at the opening of the file, under the conditions it is exactly 10 o'clock in the morning on a working day) it's no use to use application.ontime.
Since you are not changing anything in the workbook it's no use to unprotect it. You can read anything you like from a 'protected' workbook/worksheet.

The only code you need in the Workbook_open event:


Private Sub Workbook_open()
Sheets("Gantt chart").ComboBox1.Object.List = Array(1, 5, 15, 30)
If Hour(Now) = 10 And Minute(Now) < 5 And Weekday(Date, 2) < 7 Then automaticEmail
End Sub

andreaD
06-26-2014, 08:16 AM
Since you are not using a scheduled task (it only fires at the opening of the file, under the conditions it is exactly 10 o'clock in the morning on a working day) it's no use to use application.ontime.
Since you are not changing anything in the workbook it's no use to unprotect it. You can read anything you like from a 'protected' workbook/worksheet.

The only code you need in the Workbook_open event:


Private Sub Workbook_open()
Sheets("Gantt chart").ComboBox1.Object.List = Array(1, 5, 15, 30)
If Hour(Now) = 10 And Minute(Now) < 5 And Weekday(Date, 2) < 7 Then automaticEmail
End Sub

Hi snb, thanks for replying.
The unprotection of the ws is because I order the first column of the ws before sending mails (it was not shown in the wb attached, sorry). Anyway, it does not interfer with the rest of code. I used your suggestion but the problem still persists.
The point is, if I launch the macro whilst the wb is open, it works. On the contrary, when running a scheduled task the file opens, but the "run-time Active X object error" appears. Then, if I close the error window, close the wb and re-open it, it works!!!! I mean, the security window warnings me about an external program trying sending mail appears! (Obviously only if the filter I set on time in the workbook_open event is still valid!!) Very strange..

westconn1
06-26-2014, 02:35 PM
can you open the test workbook, you created, from the main one?
does that then work, or does it too fail when the main workbook is open?

andreaD
06-26-2014, 02:45 PM
can you open the test workbook, you created, from the main one?
does that then work, or does it too fail when the main workbook is open?
sorry what do u mean with ma in and test workbook?

westconn1
06-27-2014, 01:15 AM
the workbook you mention in post #9 as the test workbook
the workbook that fails to create outlook object as main workbook

another thing you can try, is to change the order of your code, create the object of outlook before any other code runs, if that works you can use the outlook object later, after the other code has run