Using "mailto" hyperlink to email the workbook as an attachment

[Gilthoniel]

I can't seem to figure out how to do this. I want to add a hyperlink to my
Excel Spreadsheet (2003 version) that will allow someone to click on it and
it will automatically construct an email to the specified "mailto" address,
attaching the spreadsheet as an attachment.

Any ideas?

 

[Daniel.C]

You may remove the hyperlink, ans format the cell in underlined blue
like a hyperlink and paste the following macro in the sheet module (it
is assumed that the cell containing the mail address is A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Count <> 1 Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Cells.Copy
Workbooks.Add 1
ActiveSheet.Paste ' or maybe do a paste special
With ActiveWorkbook
On Error Resume Next
Kill "c:\temp\attachment.xls"
On Error GoTo 0
.SaveAs "c:\temp\attachment.xls"
.Close
End With
Set OlApp = CreateObject("Outlook.application")
Set m = OlApp.CreateItem(olMailItem)
With m
.Subject = "Subject"
.Body = "Body"
.Recipients.Add Target.Value
.attachments.Add "c:\temp\attachment.xls"
.Send
End With
Target.Offset(, 1).Select
Application.EnableEvents = True
End Sub

HTH
Daniel