automatically fill template and create Outlook mail

[Bax]

Hi all,

Just before the weekend starts, I have a challenge ;-)
Attached file has 4 sheets.
SA: contains the mail template (yellow highlighted cells contains the formulas where the data is taken from)
HUB Outbound HTM dailly report: rolling sheet which is daily filled with new lines
country codes: contains countries and their codes (new lines can be added when applicable)
Mail addresses: the TO and CC mail addresses linked to each country (addresses can be added / removed)

I'm in need of a macro which will do the following:
for each newly added line in the rolling sheet (2nd sheet), it has to fill the template (1st sheet). It will then have to create in Outlook a mail with this template (section cells A6:D26) in the body of the mail.
The mail addresses in the TO and CC need to be taken from the sheet "Mail adresses" for that corresponding country.

Subject of the mail is a fixed line with 3 changing values (below in underlined and bold):
Shipping advise for NL HUB outbound shipment 00Y8261306040ISSE01E to United Kingdom on 6/21/2013

Values are taken from:
00Y8261306040ISSE01E: cell A2 on sheet SA
United Kingdom: cell L2 on sheet SA
6/21/2013: cell B24 on sheet SA

It will then also somehow need to mark the lines in the 2nd sheet for which it has already created this mail, so the next time I run the macro, it will only create mails for the newly added lines which aren't marked yet.

Each line will convert into 1 mail. So 6 lines will need to create 6 mails, even if these are for the same country, mail addresses etc.

I hope that the above makes any sense to you. If not, please let me know so I can elaborate further.

It would sincerely make life so much easier if the above is possible!

Many thanks in advance and have a nice weekend!!
Kind regards,
Peter