automatically fill template and create Outlook mail

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

Hi Apo,

Thanks a lot for the above!! That would sincerely help me a lot.
Few questions though:
1. For e.g. a country as United Kingdom in the example file, I have multiple mail addresses in the TO and in the CC line. Is it possible to include all of these addresses in the Outlook mail?
2. If there are multiple lines for 1 country in the "HUB Outbound HTM daily report" sheet, will it create multiple mails at the same time when clicking the "Send Emails" button? Preferably the macro will have to look at that sheet, and create for each line a mail in Outlook with the data that the macro currently already does. I don't have Outlook available at the moment, so I can't test this right now (macro will obviously run into an application error as it calls for Outlook).

Many many thanks for your time and expertise in this matter!!

Kind regards,
Peter

Hi..

1. For e.g. a country as United Kingdom in the example file, I have multiple mail addresses in the TO and in the CC line. Is it possible to include all of these addresses in the Outlook mail?

I added it so 7 Recipients and 2 CC's will be included (as per your uploaded sample file)..

I added some more vlookups in the "SA" sheet to enable this..

Please Login or Register  to view this content.

2. If there are multiple lines for 1 country in the "HUB Outbound HTM daily report" sheet, will it create multiple mails at the same time when clicking the "Send Emails" button?

Yes .. it does that..

As you can't check on Outlook atm.. see the screenshot..

shot1.png

Many thanks for the fast feedback!!! Will test asap and let you know ;-)
But for now, if I had worn a hat, I would have taken it off for you!!

Kind regards,
Peter

Hi Apo,

Works perfectly!!!

If I could add 100 stars, I would have done it, but for now, a well deserved star added to you!!

Kind regards,
Peter

Hi Bax..

I am glad it works well for you..

If you need any more help ... just let me know..

Hi Apo,

May I call upon your expertise one more time in this matter?
A small additional (and quite easy) change is needed.
The macro you created in the above 'mail file.xlsm' works perfectly. However, can it be changed that the mail is created only when the cell in column D on the tab "HUB outbound HTM daily report" contains data?
So when cell D2 is empty, no mail is created, when D2 isn't empty, a mail is created for line 2? In the attached file, it should create 4 mails, for lines 2, 3, 4 and 6. Then it should put a 1 in column AQ (as per the current macro). When cell D5 is filled, it should then create a mail for that line, and put a 1 in column AQ for that line etc...

So basically it should be some kind of check that if column D is filled, it should run the macro for that line, if not, it should skip to the next line.

Would that be possible? I've no idea where in the code I should enter such check...

Thanks again!!!
Peter

Never mind ;-) Just figured it out myself... not sure if it's the correct way to write the code, but I've added below code to the macro:

Please Login or Register  to view this content.

I put this above this code in the macro and it seems to work fine..

Please Login or Register  to view this content.

Regards,
peter