VBA Excel vlookup and then copy the result to clipboard

Hi Guys and thanks in advance for any info shared,

I have a person's name in cell D50 in Sheet 1 in excel and I want to reference this with a table in Sheet 2 to receive the mail of the person, in column D. I have about 100 names. I know this can be done with vlookup but then I also want to have the corresponding e-mail copied to the clipboard so I can paste it manually in outlook. I am trying to run this code but it gives error 13.

Dim obj As New DataObject
Dim txt As String

'Put some text inside a string variable
txt = Evaluate("=VLOOKUP(D50,Sheet3!A1:B100,2,0)")

'Make object's text equal above string variable
obj.SetText txt

'Place DataObject's text into the Clipboard
obj.PutInClipboard

End Sub


Any help will be much appreciated!

Hi, the code is actually works, I just had to type manually the vlookup formula! Cheers!