Excel VBA Hyperlinks Function

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

Excel VBA Hyperlinks Function 01

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

Excel VBA Hyperlinks Function 001

Alternatively, you can insert an object as below, click here for details.

Excel insert file 07

 

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

Excel VBA Hyperlinks Function 02

 

Click on the link to open the default email application.

Excel VBA Hyperlinks Function 03

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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *