Date:         Wed, 14 Mar 2012 16:07:57 -0400
Reply-To:     Ben Powell <bpowell555@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ben Powell <bpowell555@GMAIL.COM>
Subject:      Re: Using Outlook as an automated rich email service for SAS

Thanks Data _Null_, I'll have a look at that in
the morning. Does your method work as a
scheduled task?

Regards

On Wed, 14 Mar 2012 13:59:26 -0500, Data
_null_; <iebupdte@GMAIL.COM> wrote:

>This is how I do it.  The email does not look
exactly like it would if
>I had written it with Outlook editor but I
expect that problem could
>be overcome.
>
>a) Outlook will send the email as requested,
no clicks required
>That is accomplished.
>b) any formatting or content you require can
be translated to VBA
>I think this can be done.  I have done
attachments but I have worried
>to much about formatting.
>c) absolutely no smtp considerations
>Not sure what you mean but as you can see
below there are SMPT related
>bits.  Perhaps these parameters could be
queried at runtime.
>
>If you change DOMAIN below to your
domain you should be able to send
>an e-mail to yourself.
>
>data _null_;
>   length to script filevar command $256;
>   to =
catx('@',sysget('USERNAME'),'domain.com');
>
>   script    =
catx('\',pathname('WORK'),'email.vbs');
>   filevar   = script;
>   /* write the script */
>   file dummy1 filevar=filevar;
>   put 'Const sch="http://
schemas.microsoft.com/cdo/configuration/"';
>   put 'Set
myMail=CreateObject("CDO.Message")';
>   put 'With myMail';
>   put +3 '.Subject="Sending email with CDO"';
>   put +3 '.From="user@domain.com"';
>   put +3 '.' To=:$quote128.;
>   put +3 '.TextBody="This is a message." &
vblf & "This is line two"';
>   put +3 '.Configuration.Fields.Item (sch &
"sendusing")=2';
>   put +3 '.Configuration.Fields.Item (sch &
"smtpserver")="smtp.domain.com"';
>   put +3 '.Configuration.Fields.Item (sch &
"smtpserverport")= 25';
>   put +3 '.Configuration.Fields.Update';
>   put +3 '.Send';
>   put 'End With';
>   put 'set myMail=nothing';
>
>   /* close the script file by opening another
file, not used */
>   /* without this VBSCRIPT remains open and
cannot be used below in
>the same data set */
>   filevar =
catx('\',pathname('WORK'),'DUMMY2.vbs');
>   file dummy1 filevar=filevar;
>
>   /* look at the script, not necessary but may
be useful */
>   infile dummy6 filevar=script end=eof;
>   do _n_ = 1 by 1 while(not eof);
>      input;
>      putlog 'NOTE: ' _n_ 3. +1 _infile_;
>      end;
>
>   /* call the script */
>   command = catx(' ','cscript //
nologo',quote(strip(script)));
>   infile dummy7 pipe filevar=command
end=eof;
>   do while(not eof);
>      input;
>      put _infile_;
>      end;
>   stop;
>   run;
>
>On 3/14/12, Ben Powell
<bpowell555@gmail.com> wrote:
>> Dear List,
>>
>> I've come across this problem in one form
or another many times over
>> several years and never found a
satisfactory solution. The problems
>> encountered are variously:
>>
>> 1) SAS smtp config does not cover aes
encryption
>> 2) IT won't give you the exchange server or
your own local smtp server
>> 3) Outlook in order to prevent spam
doesn't like being used as an smtp
>> server and prompts on send for
confirmation
>> 4) clickyes software is kludgy in the
extreme
>> 5) Outlook is buggy in when it decides to
release auto emails from the
>> outbox
>> 6) It's nigh on impossible to get Outlook
2007 to render nice html email,
>> so all your auto emails look like they were
sent by a robot from the 70s.
>> I hear he posts on the list.
>> 7) Outlook in any event will only send on a
remote box when you log on
>> so you may as well have run it locally
>>
>> Etc etc. You'll probably get acceptable
results if you're there when the
>> email is sent and don't try anything fancy.
>>
>> But I think I have the answer. It's not just
SAS that faces these issues.
>> You get the same smtp config in EG and
task scheduler actions on newer
>> versions of Windows.
>>
>> None of these are suitable in the wild
except with ideal configs from
>> scratch.
>>
>> The answer is to not use SAS for the email
but Outlook, specifically
>> Outlook VBA.
>>
>> Still you face a couple of hurdles. There is
no schedule or sleep function
>> in the Outlook object model, so you have
to borrow from the kernel32
>> API and iterate between that and the
DoEvents function to release
>> enough CPU cycles to allow Outlook to
continue to function, but not so
>> much that you burn a CPU core.
>>
>> Then you need a basic seconds based
timer to fire off the email at
>> required intervals.
>>
>> Finally, use VBA to write the email, perhaps
using a txt input file written
>> by a regular scheduled SAS batch job, plus
an attachment from the same
>> process and:-
>>
>> a) Outlook will send the email as
requested, no clicks required
>> b) any formatting or content you require
can be translated to VBA
>> c) absolutely no smtp considerations
>>
>> Any thoughts?
>>
>> Regards
>>