Email address from Excel spreadsheet

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Email address from Excel spreadsheet

Greetings,

I have an Excel spreadsheet that I use to share an email distribution list.  This list is updated almost daily by multiple people.  There are 3 of us that rotate sending out a report created in SAS and emailed via SAS each day to the people on the spreadsheet.  Is there a way to read in all of the email addresses and put them in the TO: variable and send out this report ?  We currently use 7 distribution list for this task, but I would rather not update them each day and I don't want to create a new distribution list in Outlook and update it each day I have the duties.

I appreciate any help.

Thanks.

ODS PDF FILE = "W:\CrashMailBox\TransStatus\TRANSMISSION STATUS - &TODAY..PDF" NOBOOKMARKGEN;

PROC PRINT DATA=Trythis ;
VAR DOCTNMBR AGENCY OFFICER STATUS VERSION RPTABLE;
*WHERE STATUS <> ' ';
TITLE2 'INCOMING ELECTRONIC ACCIDENT FILES THAT SHOW A TRANSMISSION STATUS.';
TITLE3 'VALID STATUS VALUES ARE ACCEPTED, TRANSMITTED AND NON REPORTABLE.';
TITLE4 'REPORTS WITH A STATUS OF OPEN SHOULD NOT BE PROCESSED AND RETURNED TO THE AGENCY TO BE REDONE.';
*TITLE6 'A STATUS A VALIDATED MEANS THE AGENCY IS NOT TRANSMITTING THROUGH TRACS UTILITIES.';


FOOTNOTE 'S:\SOURCE\DOTBPN\TransStatus.SAS';
RUN;

ODS PDF CLOSE;

filename doemail email TO= ' '
        BCC=( 'WEEKLY REPORTS A - D','WEEKLY REPORTS E - K','WEEKLY REPORTS L - N','WEEKLY REPORTS O - R','WEEKLY REPORTS S - V','WEEKLY REPORTS W - Z','StPatrolElectronicWeekly' )
                       FROM="ME <me@DOT.WI.GOV>"
                       subject="ACCIDENT REPORTS RECIEVED AND PROCESSED &TODAY"
        TYPE='PLAIN\TEXT'
                       attach=("W:\CrashMailBox\TransStatus\TRANSMISSION STATUS - &TODAY..PDF");

data _null_;
  file DOEMAIL;
  put ' ';
  put 'Greetings,';
  put ' ';
  put ' ';
  put 'Attached is a listing of accident reports received and\or processed today.  Please check to see that all the reports you sent are listed here.';
  put ' ';
  put 'Amends are not listed on this report. ';
  put ' ';
  put "Note that any accidents you send after 7:00 a.m. may not be processed until the next day.  Be sure to check tommorrow's report for the files you sent.";
  put ' ';
  put ' ';
  put 'Thank you.';
run;


Accepted Solutions
Solution
‎03-14-2013 10:21 PM
Respected Advisor
Posts: 4,644

Re: Email address from Excel spreadsheet

You could try something like :

libname xl Excel "yourPath\yourFile.xlsx";

proc sql noprint;
select quote(trim(eMailAddess)) into :emails separated by ","
from xl.'Sheet1$'n;
quit;

libname xl clear;

filename doemail email
    TO= ' '
    BCC=( &emails )
    FROM="ME <me@DOT.WI.GOV>"
    subject="ACCIDENT REPORTS RECIEVED AND PROCESSED &TODAY"
    TYPE='PLAIN\TEXT'
    attach=("W:\CrashMailBox\TransStatus\TRANSMISSION STATUS - &TODAY..PDF");

but you might have to work harder if the list of adresses is too long for the BCC clause.

PG

PG

View solution in original post


All Replies
PROC Star
Posts: 1,090

Re: Email address from Excel spreadsheet

The most straightforward thing I can think of is to set up something in Outlook automation to gather the names, and write them out to a flat file. Then your SAS program can just read that and use the email addresses. The problem, of course, is that you're outside of SAS to do this.

Hopefully someone else will know a way that SAS can access Outlook internals.

Tom

Solution
‎03-14-2013 10:21 PM
Respected Advisor
Posts: 4,644

Re: Email address from Excel spreadsheet

You could try something like :

libname xl Excel "yourPath\yourFile.xlsx";

proc sql noprint;
select quote(trim(eMailAddess)) into :emails separated by ","
from xl.'Sheet1$'n;
quit;

libname xl clear;

filename doemail email
    TO= ' '
    BCC=( &emails )
    FROM="ME <me@DOT.WI.GOV>"
    subject="ACCIDENT REPORTS RECIEVED AND PROCESSED &TODAY"
    TYPE='PLAIN\TEXT'
    attach=("W:\CrashMailBox\TransStatus\TRANSMISSION STATUS - &TODAY..PDF");

but you might have to work harder if the list of adresses is too long for the BCC clause.

PG

PG
New Contributor
Posts: 2

Re: Email address from Excel spreadsheet

PG Stats

Thank you for the help.  It didn't dawn on me to use Proc SQL.  I have learned something new.

Brian

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 252 views
  • 0 likes
  • 3 in conversation