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.

Originally Posted by Winon

Hello EugeneE,

Maybe the attached WorkBook is what you want.

Also see the "Please consider" note at the bottom of this post.

Alright no problem.

Another method.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FoundEmail As Range
    If Target.Cells.Count > 1 Then Exit Sub
        If Target = "x" Then
            If Not Application.Intersect(Range("G2:G10"), Target) Is Nothing Then
                If Target.Offset(0, -2) <= 10 Then
                Set FoundEmail = Sheets("Email").Range("A:A").Find(What:=Cells(Target.Row, 3), _
                                                       LookAt:=xlWhole, _
                                                       MatchCase:=False)
                                                       
                If FoundEmail Is Nothing Then
                    MsgBox "Cannot match name on Email address sheet. ", vbExclamation, "No Match Found"
                Else
                    Call Testing(FoundEmail.Offset(, 1).Value2)
                End If
            End If
        End If
    End If
End Sub

Sub Testing(Email As String)
    
    Dim Subj As String
    Dim Msg As String, URL As String
        
    Subj = Cells(ActiveCell.Row, 1)
    
    Msg = ""
    Msg = Msg & "Dear " & Cells(ActiveCell.Row, 3) & "," & vbCrLf & vbCrLf & Cells(ActiveCell.Row, 2) & vbCrLf & vbCrLf & " Thank You. "
    
    'Replace spaces with %20 (hex)
    Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
    Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
    
    'Replace carriage returns with %0D%0A (hex)
    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
    
    'Create the URL
    URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
    
    'Execute the URL (start the email client)
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
    
    'Wait two seconds before sending keystrokes
    'Application.Wait (Now + TimeValue("0:00:02"))
    'Application.SendKeys "%s"
    
End Sub

AlphaFrog,

Sorry, my mistake.
Your code worked.
Thanks for your help.
Pardon me for asking you one more question,
is there a way to combine two different email body for the same name, into one email?

Hi Winon,

That was great. Thanks for your help.
However, if "David" has two task that is about to due, if there a way to merge two msg into an email?