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.

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 Outlook and Developer discussions but not got much response.)

 

[Joel]

When scheduling the task make sure it is run with Alan's crudentials.

You can add debugging statments to help isolate the problem(s). You can add
a on Error statement ito the code that will produce an error log
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/e...-code-send-outlook-mail-excel.html#post556171

On Error goto 100




100 If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
Open "C:\Users\Alan\Documents\HSC\Fence Check\Batch.log" _
For Output As #1

Write #1, msg
Close #1

End If

 

[Joel]

When scheduling the task make sure it is run with Alan's crudentials.

You can add debugging statments to help isolate the problem(s). You can add
a on Error statement ito the code that will produce an error log
Original Source: The Code Cage Forums
Scheduled Task Fails running Code to send Outlook Mail from Excel.

On Error goto 100




100 If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
Open "C:\Users\Alan\Documents\HSC\Fence Check\Batch.log" _
For Output As #1

Write #1, msg
Close #1

End If

 

[Old Man River]

Thanks Joel

Have scheduled the task with all the right things such as run when I'm not
logged in and save my password.

Am going to try and set this up as a very simple Auto_Run macro add some
debug code as suggested and try to find where it's going wrong.

My feelings are that it is grabbing some resource and not letting go as the
first time the code is hit I think the task fails to complete.

Will post back if I get any clearer inication but if anyone has any ideas
don't be shy. A programmer with 40 years experience but who's been out of the
game for 6 years ain't to old to learn new tricks.

 

[Joel]

You beat me. I only have 31 years of experience but have always been in the
game. I think you need to get the namespace which associates the user login
to the mail pst file.

Sub ChangeCurrentFolder()
Dim myolApp As Outlook.Application
Dim myNamespace As Outlook.NameSpace
Set myolApp = CreateObject("Outlook.Application")
Set myNamespace = myolApp.GetNamespace("MAPI")
Set myolApp.ActiveExplorer.CurrentFolder = _
myNamespace.GetDefaultFolder(olFolderCalendar)
End Sub

 

[Old Man River]

Oh for the days of white coats, punched cards, paper tape, drum drives and
core!

Got a result but it doesn't hep me much Get the following error:
"Error # 70 was generated by VBAProjectPermission denied"
on this code line.
Set OutApp = CreateObject("Outlook.Application")
Will start a new thread and give you a ticK

 

[Old Man River]

Many Thanks Joel. I've posted in Outlook discussion and am getting messages
that this is simply not supported! I wish the documentation said so.

Trust centre in Outlook presents a "Programmatic Access Security" but all
options are grayed out. The only trusted publisher is Sun and I've no idea
how to add myself.

I think I'm going to have to try a workaround so that the task is always
running in the forground with a periodic recalc of the spreadsheet. Hints
would be appreciated! I could then always use a background task that didn't
do much but would wake my machine if I'm away!

 

[Old Man River]

I'm already thinking that way - Pick up on a task event in Outlook then use a
null scheduled task to wake the machine and trigger outlook when I'm away
from the machine for several days.

I'll sign off this thread with the thought expressed by my first wife that,
if you'll excuse the expression, "The pleasure you get from programming is
akin to mental masturbation!"