Email used range on different sheet, use lookup to populate "to" email address

[carlito2002wgn]

Hi,

I have a workbook with several sheets, and a summary sheet. Each row in the summary sheet is a summary of some of the data on each sheet (duh! Obvious, I know).

Among others, some of the columns in my summary table are: Study Number, Sheet Link, Unit Tracker Owner

Study Number, as you might guess, has the number related to each study (project). Also, each sheet name is a study number.
Sheet Link has hyperlinks that look at the study number column, and when clicked, bring the user to the sheet with the same name as study number of that row.
Unit Tracker Owner has the name of the person running the project. This is the person who should get the email

I'm not sure what my options are, or what the best way to accomplish my goal is, but what I basically want to do is, add a column with a link (perhaps it will say "email") to the end of my summary table. When I click on that link, I want it to copy the used range from the related sheet into an Outlook email.

I have a lookups sheet in my workbook for various lookups to which I can add Unit Tracker Owner's names and email addresses to perform a lookup to populate the "to" link in an Outlook email.

Does this sound like something that can be done? Does it sound like the logic to my approach makes sense, or is there a better way? Is more information needed to even begin to answer this question?

Thank you,
Carlos

Edit: I have created a sample file to help make things a bit more clear. FYI, the lookups sheet that I mentioned above is currently called "Currency", and it does not currently have the Unit Tracker Owner names and email addresses, but they can be added.

[Logit]

.
Do I understand your goal correctly, as outlined below ?

	B	C	D	E	F	G	H	I	K	M	N	P
1	Study Number	Sheet Link	Unit Tracker Owner	Department	Group Lead	Sponsor	FX Rate	Current Month Forecast (USD)	Current Month Revenue (USD)	Overburn	Current Month Deviation (USD)	email address
2	05460093	Go to Sheet	Sonia Kim	ICO UTS RSH		Lundbeck	USD	130,443.39	108,573.21	Yes	(24,170.64)	[email protected]
3	05460143	Go to Sheet	Paul Aylott	ICO LA UTS RSH		Lundbeck	EUR	16,525.02	24,598.47	Yes	(1,205.78)	[email protected]
4	05460168	Go to Sheet	Paul Aylott	ICO UTS RWE		Lundbeck	EUR	7,605.52	10,782.65			[email protected]
5
6		Using P2 email address, send an email to D2, and have the contents of Sheet B2 included in the body of the email ?
7			Or would it be better to attach a new workbook with the contents of Sheet B2 ?

[carlito2002wgn]

Hi Logit, and thank you for the quick reply.

I think you nailed it on the head. I think your suggestion in C6 is exactly what I would like to do. The only thing I imagined differently is that the cells in column P would say "email", or "Send email", but displaying the email address is perfectly fine, too.

One more thing I should note, I would like for the Outlook email to NOT send automatically. Ideally, the email window would open with the contents of the relevant sheet copied to it, and the person's email address in the "To" line, but I would like to manually hit "Send" to send the email. The reason for this is that I will need to write some text in the body of the email before sending. Ultimately, I would like to have some standard text that would populate the body of the email along with the contents of the relevant sheet, but I am not ready for that at this time, and could not feel good asking for so much here in one shot! Maybe I can come back to you in the future with that request for additional functionality if that's ok

[Logit]

.
Here is one method :

Please Login or Register  to view this content.