Automated formatted emails based on a range

[FlyingElk]

Hello all, FE here again... I wondered if someone with more coding knowledge than me can help solve this conundrum!

I would like to send a formatted email from outlook; the template for which is on the worksheet 'Email' (I was originally using an outlook template, but was advised against this). The email should contain elements of text referenced from certain cells on page 'Hotel Staffing'. The email should then be created with all addresses under the 'Hotel Staffing!email ad' cells copied into the recipients field, and with a copy of the entire spreadsheet also being attached. I'm sure this should be possible, and I've tried my best (see my blundered module) but just keep getting nothing at all, or errors which I cant rectify... I'm hopeful that one of you amazing VBA gurus will be able to help.

Grateful as always,
A humble FlyingElk

[Mumps1]

Are the email addresses in column C to the right of the cells that contain "email ad"? Do you want the one email to go to all the email addresses?

[FlyingElk]

Hi Mumps, thanks for your reply. The email addresses are in row 15, 18, 20, 23, 25, 27 & 29.

Yep just want one email to be created with all the addresses copied in automatically if that's possible. Thanks

[Mumps1]

Try this macro. Please note that there is currently no "Subject". You will have to manually enter the email subject between the quotes where indicated. Modify the data (in blue) to match your actual data. Also, the macro is set to display the email. This gives you an opportunity to review it before sending. If you want to send it automatically without reviewing it, change ".Display" to ".Send".

Please Login or Register  to view this content.

[FlyingElk]

Thanks so much Mumps1, that is a great start! I have a few questions if you will indulge me?

1) Is there any way to have the email formatted as per the 'Email' worksheet, as opposed to being in the outlook default HTML style?
2) When I create the email; it is addressed just to 'email ad'. I realise that when I clarified that before I wasn't entirely clear, but these are effectively headers for the rows. I have populated the spreadsheet with some sample data for clarity, and reupload it here. Is there any way to create the string by inputting the addresses from the populated cells?

I really appreciate your help Mumps1. Thanks a million!

[Mumps1]

I believe that there is code that can format the email, but unfortunately I don't have any experience in that area. You may want to do some research on it to see what you can find. The macro below will now insert the correct email addresses:

Please Login or Register  to view this content.

[FlyingElk]

That's amazing Mumps, thanks a million!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[FlyingElk]

All done, thanks Ali

[Mumps1]

You are very welcome.