Hyperlinks in Excel VBA – Explained with Examples!

We can do many things using Hyperlinks in Excel VBA. The following examples will show you how to add or create and remove hyperlinks, how to open files using hyperlinks. And sending emails using hyperlinks in Excel VBA.

Important Methods and properties of Hyperlinks Object:

Add Method:

Add method of hyperlinks will add a hyperlink to a specific range or shape.

Delete Method:

Delete method of hyperlinks will delete the hyperlink.

Count Property:

Count property of hyperlinks will returns number of hyperlinks in object hyperlinks collection.

Important Methods of Hyperlink Object:

AddToFavorites:

AddToFavorites method of workbook will add a shortcut to the workbook or hyperlink to the Favorites folder.

CreateNewDocument:

CreateNewDocument method of hyperlink will creates a new document linked to the specified hyperlink.

Follow:

Follow method of a hyperlink will displays a cached document, if it’s exist. Otherwise will resolve the hyperlink to download the target document and displays the document in the appropriate application.

Important Properties of Hyperlink Object:

Address:

Address property of hyperlink object will returns the address of the hyperlink. It can be also used for setting an address value the hyperlink of the target document.

Creator:

Creator property of hyperlink object will the application in which this object was created.

EmailSubject:

EmailSubject property of hyperlink object will returns the subject line of the email. You can also use this property to set the subject line of hyperlink email.

Name:

Name property of hyperlink object will return name of the object in text format.

Parent:

Parent property of hyperlink object will return the parent object for the specified object.

Range:

Range property of hyperlink object will returns a range where hyperlink is attached.

ScreenTip:

ScreenTip property of hyperlink object will return the ScreenTip label of a hyperlink.

Shape:

Shape property of hyperlink object will return a Shape object that represents the shape attached to the hyperlink.

SubAddress:

SubAddress property of hyperlink object will return the location of the document associated with the hyperlink.

TextToDisplay:

TextToDisplay property of hyperlink object will return label of the hyperlink. You an also use this property to set the caption to be displayed for the specified hyperlink. Address of the hyperlink. Will be the default label.

Hyperlinks Examples using Excel VBA:

Following are the examples on using Hyperlinks in Excel with VBA.

Add Create Hyperlinks in Excel VBA:

The below example code will show you adding hyperlinks using Excel VBA.

Code:

Sub sbCreatingHyperLink()
ActiveSheet.Hyperlinks.Add Range("A5"), "https://www.analysistabs.com"

End Sub

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert New Module
4. Copy the above code and Paste in the code window
5. Press F5 to execute it
6. You can see a new hyperlink is added at A5

Removing Hyperlinks in Excel VBA:

The below example code will show you removing hyperlinks using Excel VBA.

Code:

Sub sbRemovingHyperLink()

Range("A5").Hyperlinks.Delete

End Sub

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert New Module
4. Copy the above code and Paste in the code window
5. Press F5 to execute it
6. It should remove the hyperlink from A5

VBA Open File Folder Website Using FollowHyperlink method in Excel:

The below example code will show you opening files, folders or a specific website using FollowHyperlink in Excel VBA.

Code:

Sub sbOpenAnything()
  
    Dim sXLFile As String
    Dim sFolder As String
    Dim sWebsite As String
   
    sFolder = "C:Temp" ' You can change as per your requirement
    sXLFile = "C:Temptest1.xls" ' You can change as per your requirement
    sWebsite = "https://www.analysistabs.com/" ' You can change as per your requirement

    ActiveWorkbook.FollowHyperlink Address:=sFolder, NewWindow:=True 'Open Folder
    ActiveWorkbook.FollowHyperlink Address:=sXLFile, NewWindow:=True 'Open excel workbook
    ActiveWorkbook.FollowHyperlink Address:=sWebsite, NewWindow:=True 'Open Website

End Sub

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert New Module
4. Copy the above code and Paste in the code window
5. Change the required file, folder and website to open
6. Press F5 to execute it
7. It should open the all file, folder and website mentioned in the code

VBA Create Send Emails Using FollowHyperlink Method – Send Keys in Excel:

The below example code will show you how to send an email using FollowHyperlink Method – Send Keys in Excel VBA.

Code:

Sub sbCreatingEmail()

    Dim sMsg As String
    Dim Recipient As String
    Dim RecipientCC As String
    Dim RecipientBCC As String
   
    Dim sSub As String
    Dim sHLink As String
  
    Recipient = "test@org.email.com"
    RecipientCC = "test@org.email.com"
    RecipientBCC = "test@org.email.com"

    sSub = "Test Mail"
    sMsg = "Hi, this is a auto generated mail from excel"
    sHLink = "mailto:" & Recipient & "?" & "cc=" & RecipientCC & "&" & "bcc=" & RecipientBCC & "&"
    sHLink = sHLink & "subject=" & sSub & "&"
    sHLink = sHLink & "body=" & sMsg

    ActiveWorkbook.FollowHyperlink (sHLink)
    Application.Wait (Now + TimeValue("0:00:03"))
    Application.SendKeys "%s" 'Send Keys

End Sub

VBA to loop through all hyperlinks in a worksheet:

We can use hyperlinks collection of an worksheet to loop through all hyperlinks in a spread sheet. The following example will show you to display all link address of the hyper links in the worksheet.

Code:

Sub sbLoopThroughAllLinksinSheet()
'For each link in the worksheet
For Each lnk In Sheets("Sheet1").Hyperlinks
    'display link address
    MsgBox lnk.Address
Next
End Sub

VBA to loop through all hyperlinks in a workbook :

We can use hyperlinks collection of an worksheet to loop through all hyperlinks in a workbook. The following example will show you to display all link address of the hyper links in active workbook by sheet.

Code:

Sub sbLoopThroughAllLinksinWorkbook()
'for each sheet in active workbook
For Each sh In ActiveWorkbook.Sheets
    'For each link in a shet
    For Each lnk In sh.Hyperlinks
        'display worksheet name and link address
        MsgBox sh.Name & ":" & lnk.Address
    Next
Next
End Sub