Read/Retrieve/locate data between different excel sheet using macro/vba

I am using excel to send mail.
The example workbook below shows that whenever "day left" is less than 10, cell will turn orange.
By keying "x" it send mail with the email subject and msg already keyed in.
However i need to relocate the email address column to another sheet.
Is there anyway for the "Names" to locate/retrieve the correct email addresss from the other sheet?
Example2.xlsm is what i may want it to look like.
Pls look at the two example to get a better idea on what i am asking for.