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

  • Thread starter Thread starter Old Man River
  • Start date Start date
O

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.
 
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().
 
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!
 
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.
 
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
 
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.
 
Sue gave you the answer. I made a mistake in not remembering that your
scenario is not supported. She's absolutely correct.
 
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.
 
Back
Top