CODEWITHSUNDEEP
excelvbaoutlook
Mark harris
Mark harris

Reputation: 543

Add hyperlink to Outlook email which goes to row number when opening Excel spreadsheet

I have an Excel spreadsheet which sends email using VBA:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "This is email text, click the link <a href='C:\test.xlsm & Range("F" & ActiveCell.Row).Address'></a>"

On Error Resume Next

With OutMail
    .To = "####"
    .CC = ""
    .BCC = ""
    .Subject = "Sales Tracker: A New Task has been added to the tracker"
    .HTMLBody = strbody & strbody2 & strbody3 & strbody4
    .Send 'displays outlook email
    'Application.SendKeys "%s" 'presses send as a send key
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

The email is sent when a user clicks on a specific cell within the active row.

I include the Excel spreadsheet in the email with a hyperlink.

Now I want to add to the hyperlink to include the cell reference of the row the user clicks on.

The idea is the hyperlink when clicked will open the spreadsheet and will take the user to the row the link refers to and highlight it.

Upvotes: 1

Views: 2950

Answers (2)

R3uK
R3uK

Reputation: 14547

You are missing the reference of the sheet in your link (even though I'm not sure that this will be enough), so try something like this : 

href='[C:\test.xlsm]" & ActiveSheet.Name & "!" & Range("F" & ActiveCell.Row).Address & "'></a>"

to match this format :

href='[C:\test.xlsm]SheetName!A1'

And more importantly, you forgot to close the quotation marks properly, so here it goes :

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "This is email text, click the link <a href='[C:\test.xlsm]" & _
            ActiveSheet.Name & "!" & Range("F" & ActiveCell.Row).Address & "'></a>"

On Error Resume Next


With OutMail
    .To = "####"
    .CC = ""
    .BCC = ""
    .Subject = "Sales Tracker: A New Task has been added to the tracker"
    .HTMLBody = strbody & strbody2 & strbody3 & strbody4
    .Send 'displays outlook email
    'Application.SendKeys "%s" 'presses send as a send key
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Upvotes: 1

mauek unak
mauek unak

Reputation: 772

I'm not sure if it's possible to do it with hyperlink, most likely not. Only thing that comes to my mind is to add Worksheet_Activate() event to spreadsheet you are attaching, and there point to range you wish, but not with hyperlink.

Upvotes: 0

Related Questions