Excel VB Macro need help incorporating vlookup

[mikelever]

Hi,

i have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,' Tenacity Jobs'!1:65536,5 ,FALSE)

How can I incorporate this function so it will insert the result of the lookup into the "To" box ?

_______________ _______________ _______________ _______________ ______

Sub SendEmail()

Dim OlApp As New Outlook.Applica tion
Dim myNameSp As Outlook.Namespa ce
Dim myInbox As Outlook.MAPIFol der
Dim myExplorer As Outlook.Explore r
Dim NewMail As Outlook.MailIte m
Dim OutOpen As Boolean

' Check to see if there's an explorer window open
' If not then open up a new one
OutOpen = True
Set myExplorer = OlApp.ActiveExp lorer
If TypeName(myExpl orer) = "Nothing" Then
OutOpen = False
Set myNameSp = OlApp.GetNamesp ace("MAPI")
Set myInbox = myNameSp.GetDef aultFolder(olFo lderInbox)
Set myExplorer = myInbox.GetExpl orer
End If
'myExplorer.Dis play ' You don't have to show Outlook to use it

' Create a new mail message item.
Set NewMail = OlApp.CreateIte m(olMailItem)
With NewMail
'.Display ' You don't have to show the e-mail to send it
.Subject = "Look at this!"
.To = "name@wherever. com"
.Body = "This is a demonstration"
End With

'NewMail.Send
If Not OutOpen Then OlApp.Quit

'Release memory.
Set OlApp = Nothing
Set myNameSp = Nothing
Set myInbox = Nothing
Set myExplorer = Nothing
Set NewMail = Nothing

End Sub