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:
if Refno ne "" then delete;
libname xls clear;
/*email excel sheet*/
filename mymail email "Blah@blah.co.uk"
subject="Missing Ref No"
put 'Hi. ';
put 'Could you please provide me with the relevant Ref No for the attached cases. ';
put 'Regards ';
put 'Andy ';