This Excel VBA tutorial explains how to use Hyperlinks Function to send email and add hyperlink to Cell.
Excel VBA Hyperlinks Function
The purpose of the Hyperlinks Function is to remove or add hyperlink to a Cell, the hyperlink can be an email or a website.
There is another Worksheet Function Hyperlink (without s).
Hyperlinks.Add Method
Syntax
expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
| Name | Required/Optional | Data Type | Description |
| Anchor | Required | Object | The anchor for the hyperlink. Can be either a Range or Shape object. |
| Address | Required | String | The address of the hyperlink. |
| SubAddress | Optional | Variant | The subaddress of the hyperlink. |
| ScreenTip | Optional | Variant | The screen tip to be displayed when the mouse pointer is paused over the hyperlink. |
| TextToDisplay | Optional | Variant | The text to be displayed for the hyperlink. |
Example 1 – Add website hyperlink
The below example adds a text “Access-Excel.tips” in Cell A1, and add a hpyerlink to http://access-excel.tips. ScreenTip is the yellow pop up box when you hover the move over the hyperlink.
Public Sub add_hyperlink()
ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="http://access-excel.tips", ScreenTip:="This is my website", TextToDisplay:="Access-Excel.tips"
End Sub
Example 2 – Add file hyperlink
Simply replace the website with the file path to add file link.
Public Sub add_hyperlink()
ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="C:\example.docx", ScreenTip:="Open file", TextToDisplay:="example.docx"
End Sub
Alternatively, you can insert an object as below, click here for details.
Example 3 – Add Email hyperlink
The below example adds a text “Email me” in Cell A1, and add an email hpyerlink to my email address.
Public Sub add_hyperlink()
ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="mailto:scammera1@yahoo.com.hk", ScreenTip:="This is my email", TextToDisplay:="Email me"
End Sub
Click on the link to open the default email application.
There are other methods to send email via Excel VBA where you can do more customization, click on the below post.
Send Email in Excel VBA using CDO and OLMailItem
Remove Hyperlink using Hyperlinks.Delete Method
The below example removes all hyperlinks in active worksheet.
Public Sub del_hyperlink() ActiveSheet.Hyperlinks.Delete End Sub
The below example removes hyperlink in Cell A1. You can customize the If Condition to suit your needs such as removing hyperlink of specific range value.
Public Sub del_hyperlink() For Each hyper In ActiveSheet.Hyperlinks If hyper.Range.Address = "$A$1" Then hyper.Delete End If Next End Sub
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff822490.aspx