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). Yo
can add a on Error statement ito the code that will produce an erro
log

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

Note: I used >> so the message adds to the log file and not overwrit
previous messages

 

[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 i
the game. I think you need to get the namespace which associates th
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 Su

 

[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

 

[joel]

I don't think there is an answer for this. It is a security issue. Yo
may want to post this question again to see if anybody has a solution.


When VBA code opens a mail folder a dialog box comes up asking for ho
long you want to have acxcess to the e-mail folder. This is to preven
a virus from attacking e-mail accounts. When you are running fro a tas
there is no way of disabling the dialog box. If there was then a viru
could simply perform the same bypass and attacked ths e-mail accounts o
a computer. I don't know if running the task as some sort o
administrator account will bypass the error message. I assume tha
there must be a method for administrator's tasks getting access t
e-mail accounts.

There is a trusted publish option that is in both excel and outlook.

From excel worksheet menu or outlook menu (not VBA)

Tools - Macro - Security - Trusted Publishers

There are two check boxes that you may try to eliminate the problem.
These need to be checked in outlook not in this case excel. You need t
check the boxes in excel if you where going to run a macro that woul
change antoher macro. On my PC at work I can check both boxes in exce
but only have access to one of the boxes in outlook. I don't have th
poriledge at work to add any trusted publisher accounts into the window

 

[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!

 

[joel]

Since you are an old programmer I like using a quote from the Uni
Manual:

If you can't bring Mohammed to the Mountain, bring the mountain t
Mahammed.

Thank-you K & R.


In this case have you considered writing the macro in Outlook? You ca
schedule an outlook task to trigger the macro. Outlook VBA can ope
workbooks very easily

 

[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!"