Loop cell to create PDF and send as email in lotus notes

Hi All,

I'm currently developing a supplier report that needs to be sent each month.
I've created the report and the supplier codes can be changed in a drop down in cell C1 on sheet 1.
As I have over 400 suppliers I would like to automate this process with some sort of loop that would scroll through supplier codes in column A on sheet 2 pick up the contact email address in column B, convert sheet 1 as a PDF and then attach to an email and send.

I'm not really sure where to start, so any help is appreciated.

I'm currently using Office 2007 and Lotus notes.

Thanks

Mark.