Excel VBA Formula for new Email To's & CC's

[NatashaGillWH]

Hiya!

I am wanting to set up a object that will be called "Email RFI" with macros linked that will draft a new email based on various information in cells. I want the macro to pick up the name in cell B/C12 in worksheet "RFI", and then make the To: part of the email the email address in cell C18 in Worksheet "Job Details" Screen shots below.

However, i need this to be flexible, as the template already has a macro working on it so it copies everything from the template and when i click "new", it creates RFI1, then RFI2 and so on. And also the name of who it's addressed to could change depending on who it needs to go to, so sometimes it may go to Heather and othertimes it may go to Rachel which i need the VBA to pick up the name on the RFI worksheets in B/C12 and automatically know to address the email to the email next to that name on the Job Details spreadsheet. If that makes sense?

I will also want to include a CC function, where it again picks up the names associated in RFI worksheets, cells B/C 41:48 and will include them in this same email as a CC and grab their emails from cells C18:25 from worksheet "Job Details"

Any ideas? It's a lot, but would save a lot of our processing time making new emails and typing each To and CC in.

I feel it would beneficial to create the macro on the "Template" worksheet, so that when i click the "new rfi" macro on the template worksheet, it copies over all the information including the "Email RFI" macro and still picks up the correct details.

 

[ManiacB]

So do you want to create an email tab to then create an email? It is not clear what you have in this total spreadsheet. Any chance you can upload a sample file?