Adding a Link to another spreadsheet in email body using VBA

Good morning,

I have VBA coding that sends an email to a specific person depending on what name is entered into a form.
I want to be able to add a link within the email that will take the recipient to a specific spreadsheet, the link is different for each person,. The link to be placed in the text body comes from cell AM22.

I have the following code, it sends everything that i need it to apart from a workable link, currently it is just populating the text from AM22 (not showing as a link)

Sub email_from_excel_basic()

Dim emailapplication As Object
Dim emailitem As Object
Dim link As String

Set emailapplication = CreateObject("Outlook.Application")
Set emailitem = emailapplication.createitem(0)

' build the email

emailitem.to = Range("Handover!D9").Value
emailitem.Subject = "Handover Created."
emailitem.Body = "Handover created to carry out the following:" & vbNewLine & vbNewLine & Range("Handover!AL17").Value & "    " & Range("Handover!AM17").Value & vbNewLine & Range("Handover!AL18").Value & "  " & Range("Handover!AM18").Value & vbNewLine & Range("Handover!AL19").Value & "  " & Range("Handover!AM19").Value & vbNewLine & Range("Handover!AL20").Value & "  " & Range("Handover!AM20").Value & vbNewLine & Range("Handover!AL21").Value & "  " & Range("Handover!AM21").Value & vbNewLine & Range("Handover!AL22").Value & "  " & Range("Handover!AM22").Value

' send the email
emailitem.send

Set emailitem = Nothing
Set emailapplication = Nothing

End Sub

Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer

Very new to VBA and any guidance would be greatly appreciated.

Scott

To include a link in an email, you have to use HTMLbody instead of Body, and write the content in HTML. You did not give us enough information to know where the file is to be able to link to it.

An HTML link cannot link to a specific worksheet or cell in an Excel file. You can do this internal to an Excel file with a hyperlink in a cell, but not from a link in an email. The best you can do is link to the file and Excel will open it.

There is the other question of where the file is stored and whether an email can even link to it at all.

I am reading your post again and I may have misunderstood. You don't want to link to cell AM22; the content of cell AM22 gives you the link. Here is how I would write this, also splitting up the line for readability:

emailitem.htmlBody = "Handover created to carry out the following:<br><br>" & vbNewLine & _
                 Range("Handover!AL17").Value & "    " & Range("Handover!AM17").Value & "<br>" & vbNewLine & _
                 Range("Handover!AL18").Value & "  " & Range("Handover!AM18").Value & "<br>" & vbNewLine & _
                 Range("Handover!AL19").Value & "  " & Range("Handover!AM19").Value & "<br>" & vbNewLine & _
                 Range("Handover!AL20").Value & "  " & Range("Handover!AM20").Value & "<br>" & vbNewLine & _
                 Range("Handover!AL21").Value & "  " & Range("Handover!AM21").Value & "<br>" & vbNewLine & _
                 Range("Handover!AL22").Value & "  " & "<a href='" & Range("Handover!AM22").Value & "'>YOUR LINK TEXT GOES HERE</a>"

Impossible to test without your file.