Looping Macro to create and email Excel attachment

[deleted_user]

Apologies in advance for long winded question but any help would be greatly appreciated
I need some help with a macro that will loop through data and write data to spreadsheet depending on 2 variables (the Branch and the Ref No)

One data set contains sales data. Columns include ‘Branch name’, ‘Policy number’, and ‘Ref No’. This will be a daily programme and the ranges are dynamic.

My aim is to create an excel spreadsheet by branch for any instance, if any, where the ‘Ref No’ field is blank and then email the output as attachment to the relevant branch
If the Ref No field is not blank, no email is required.
I also need some form of summary function so that if there are 3 entries for the London branch with blank Ref No field then 1 email as opposed to 3 is generated .
Code I have so far can only generate output by a predefined excel template and send to one email address so is very limited:



data test1;
set dublindata;
if Refno ne "" then delete;
run;

/*write reults to Excel sheet*/

libname xls pcfiles server=&_clientmachine PORT=8621
path="\\saspcomp01\C\Andy\Testsheet.xls";

proc sql;
drop table xls.Testarea;
quit;

data xls.Testarea(dblabel=yes);
set test1;
run;
libname xls clear;

/*email excel sheet*/

filename mymail email "Blah@blah.co.uk"
subject="Missing Ref No"
attach=("/sas/C/Andy/Testsheet.xls"
content_type="application/excel");
data _null_;
file mymail;
put 'Hi. ';
put 'Could you please provide me with the relevant Ref No for the attached cases. ';
put 'Regards ';
put 'Andy ';

run;

[Peter_C]

Need some lookup from "branch" to a suitable email address.
Need to decide how to operate over multiple branches : via macros or by-groups.
What are the volumes?

[deleted_user]

Volumes are relativley small

10 Branches and the daily data set will contain anything between 5 and 50 policies