Email On Click Cell

[YellowLabPro]

I have a fun one here.
I have a worksheet that looks up the email address:
=IF(G5="","",VLOOKUP($G5,CompInfo!$A$2:$M$199,10))
which resides in F9.

What I thought would be good fun is to be able to click on that cell or another button that would look at F9, close the workbook, attach it and send the workbook to that email address, possible?

Thanks,

YLP

ps. I will be gone for most of the day.... I will check back in tonight.

[mdmackillop]

Hi Yelp,
Been working on something similar here. To attach the active workbook, change the code as follows
[vba] 'Make temporary file
ActiveWorkbook.SaveCopyAs "G:\Test.xls"

HLink = "file://" & Path & Target.Offset(, -6) & ".xls"

With objMail
'Get email address from data sheet
.To = c.Offset(, 2)
.Subject = "FAO " & c.Offset(, 1).Value
.Attachments.Add "G:\Test.xls"
.Body = Target.Offset(, -5) & vbCr & "This issue has been closed by " & _
d.Offset(, 1) & vbCr & vbCr & HLink
.NoAging = True
.Display
End With
'Remove temporary file
Kill "G:\Test.xls"
[/vba]