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

NatashaGillWH

New Member
Joined
Aug 12, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.

1597267860646.png


1597267891884.png

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"
1597268023203.png


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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,116
Messages
5,622,816
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top