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.)

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

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 overwrite
previous messages.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

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/ex...tml#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


"Old Man River" wrote:

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.)

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" wrote:

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.)

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" wrote:

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" wrote:

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

I don't think there is an answer for this. It is a security issue. You
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 how
long you want to have acxcess to the e-mail folder. This is to prevent
a virus from attacking e-mail accounts. When you are running fro a task
there is no way of disabling the dialog box. If there was then a virus
could simply perform the same bypass and attacked ths e-mail accounts on
a computer. I don't know if running the task as some sort of
administrator account will bypass the error message. I assume that
there must be a method for administrator's tasks getting access to
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 to
check the boxes in excel if you where going to run a macro that would
change antoher macro. On my PC at work I can check both boxes in excel
but only have access to one of the boxes in outlook. I don't have the
poriledge at work to add any trusted publisher accounts into the window.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

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" wrote:


I don't think there is an answer for this. It is a security issue. You
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 how
long you want to have acxcess to the e-mail folder. This is to prevent
a virus from attacking e-mail accounts. When you are running fro a task
there is no way of disabling the dialog box. If there was then a virus
could simply perform the same bypass and attacked ths e-mail accounts on
a computer. I don't know if running the task as some sort of
administrator account will bypass the error message. I assume that
there must be a method for administrator's tasks getting access to
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 to
check the boxes in excel if you where going to run a macro that would
change antoher macro. On my PC at work I can check both boxes in excel
but only have access to one of the boxes in outlook. I don't have the
poriledge at work to add any trusted publisher accounts into the window.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

.

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

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

Thank-you K & R.


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

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

"joel" wrote:


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

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

Thank-you K & R.


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=153357

Microsoft Office Help

.