Date: Wed, 14 Mar 2012 13:59:26 -0500
Reply-To: "Data _null_;" <iebupdte@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Data _null_;" <iebupdte@GMAIL.COM>
Subject: Re: Using Outlook as an automated rich email service for SAS
Content-Type: text/plain; charset=ISO-8859-1
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
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.
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="email@example.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);
putlog 'NOTE: ' _n_ 3. +1 _infile_;
/* call the script */
command = catx(' ','cscript //nologo',quote(strip(script)));
infile dummy7 pipe filevar=command end=eof;
do while(not eof);
On 3/14/12, Ben Powell <firstname.lastname@example.org> 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
> 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
> 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?