View Full Version : VBA code to use a hyperlink to email file

05-05-2009, 10:19 PM
hey guys,

im working on a spreadsheet where we hyperlink the files into this one spreadsheet.

the idea behind the vba code is as follows

one column is the =hyperlink("directory","Click Here")
next column is the activex checkbox

the vba code right now checks column with checkbox for instances of ticked checkbox (which is linked to the cell in which is positioned to hold the value when its ticked)

then it checks if the column with the hyperlink "hasformula"
if it does then it removes the characters up to the first "
then it removes everything from the second "
then it sends out the file in an email


there are a lot of people using this document who would rather hyperlink it by right clicking on the name of the document and putting in a hyperlink, because its easier than typing out a formula and searching for location.

it ruins my method because now there is no formula in the cell, so i cant do what i was doing before. when i hover my mouse over the hyperlink it shows me the location. is there anyway to get that location from that way of hyperlinking. if so would you be so kind to tell me how

thank you

05-06-2009, 12:21 AM
The following will return the address of a hyperlink entered in cell A1:


05-07-2009, 12:08 AM
got it working. thanks

now a trickier bit...

i have a range of few cells in sheet 2, i want to select that range and paste it in outlook in the body of the email by using a macro.

itd be good if it could be pasted as a picture instead of text so that the recipient cannot be edited

thanks guy

05-07-2009, 01:05 AM

Have a look here: http://www.rondebruin.nl/sendmail.htm

My preferred method is the Object Outlook Model. I know an image can be taken from a graph but I really don't know how to take an image of a range.

There are plenty of examples in the above link.