Scheduled Task Fails running Code to send Outlook Mail from Excel.

[Old Man River]

I am trying to run this subroutine from an Excel Macro in a Scheduled job.

Private Sub SendMessage()
Dim OutApp As Object
Dim OutMail As Object
'ToStr, Subject and strBody are globals in the Excel VB Module.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ToStr
.CC = "xxx@yyy" 'Real address obscured here.
.BCC = ""
.Subject = Subject
.Body = strbody
.Send
End With
End Sub

It works perfectly when triggered by opening the spreadsheet which has an
auto run macro which invokes the code. Either manually from Excel or by
running the Command Line "C:\Program Files\Microsoft
Office\Office12\Excel.exe" /r "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Auto Run.xlsm"

Normally the scheduled task that runs the same code (there is a bit more to
it) runs perfectly but when it hits the above it is failing and as a result
screwing up the scheduled job which then fails to complete and subsequently
will not run until after a reboot and re-registering the task.

This is a follow on from a previous thread viz:
http://social.answers.microsoft.com...b77-d690-4403-8b51-4e1d0ae998e1?prof=required

Can anybody help or suggest a workaround. P.S. I always have Outlook Open on
my Desktop and I hate having to late bind the Objects in this code.

---Also posted in Office Developer Automation - but seems a pretty inactive
forum.

 

[Ken Slovak - [MVP - Outlook]]

I have no idea how that other link relates to this but just use GetObject()
to see if Outlook is running, if not use CreateObject().

 

[Old Man River]

Thanks Ken but as I understand it Outlook is a single instance programme so
both GetObject() and CreateObject() are equivalent. Thing is it works
perfectly when manually triggered but fails in a scheduled task!

 

[Ken Slovak - [MVP - Outlook]]

Did you try what I suggested? If not try it and see if it helps.

 

[Old Man River]

Thanks Ken

Put some debug code in and am getting the error
"Error # 70 was generated by VBAProjectPermission denied" if I use
CreateObject
and
"Error # -2147221020 was generated by VBAProjectAutomation error
Invalid syntax " if I use GetObject.

But thanks anyway. I'll start a new post to see if anyone can tell me what
this error means.

 

[Sue Mosher [MVP]]

PMFJI, but it may mean that what you're trying to do isn't possible, given
that automating Outlook from a scheduled task is not a supported scenario.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Old Man River]

Tried again after checking the GetObject syntax and adding in a comma and got:
Error # 429 was generated by VBAProjectActiveX component can't create object.
Different Error message but does confirm that Create and Get Object are
equivelent for OutLook.

 

[Ken Slovak - [MVP - Outlook]]

Sue gave you the answer. I made a mistake in not remembering that your
scenario is not supported. She's absolutely correct.

 

[Old Man River]

Feel free to jump whenever you want!

Thank's I was begining to think that it was a security issue. So I'll have
to skin this cat a different way (Can't accept impossible)!

I'm thinking (hoping) pick up on a reminder event in Outlook and trigger the
Excel from there. Just need to have a scheduled job that wakes the machine so
Outlook triggers the reminder if I'm away for a while.

 

[Old Man River]

Many Thanks Ken for your interest - see my post in response to Sue's.