VBScript_Not able to open Hyperlinks to an excel SHEET(not whole workbook) from outlook's email body directly

Question

Background:

I am supposed to create a dashboard(in excel) using vbscript and send it over an email(outlook) to someone. I already have a template for the Dashboard(excel). My script opens up that template excel, updates the Excel with today's results and some hyperlinks to some new files, creates an excel mail envelope and sends it to someone.

ISSUE:

My script adds 2 hyperlinks in the dashboard excel. 1st link points to a mht File and 2nd link points to another excel File. I sent the mail to myself and opened it. After opening the email, when I click on the 1st link(to mht file), it works fine and opens up IE for displaying mht file contents. Issue is with the 2nd hyperlink which points to an excel file. It is not Opening the excel file(I have made sure that the link is correct).

Is there something I need to change in my code or Is there any outlook setting which will allow me to open up the link to an excel file directly?

CODE:

Option Explicit
Dim objXL, objXb, objXs, strXlPath, strMHTLink, strExcelLink

'Dashboard Excel template
strXlPath = "C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\test.xlsx"

'Link to MHT file
strMHTLink = "C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\MHTFile.mht"

'Link to another Excel File
strExcelLink = "C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\EXLFile.xlsx"

'Opening an excel(strXlPath) and updating it's Sheet1. In Sheet1, I will be adding 2 hyperlinks. 
'1st link for the MHT file(strMHTLink)
'2nd link for another Excel File(strExcelLink)

set objXl = CreateObject("excel.application")
objXl.visible = true
objXl.displayAlerts = false
set objXb = objXl.Workbooks.Open(strXlPath)
set objXs = objXb.Sheets("Sheet1")

objXs.Cells(2,1) = "=HYPERLINK("""&strMHTLink&""",""MHTLINK"")"                 'When clicked, it should open IE to display mht file contents
objXs.Cells(2,2) = "=HYPERLINK(""["&strExcelLink&"]Sheet1!A1"",""EXLLINK"")"    'When clicked, it should open Sheet1 of EXLFile.xlsx

'Saving the Workbook
objXb.save

'Mailing this excels contents using Mail Envelope
objXb.EnvelopeVisible = true
With objXs.MailEnvelope.Item
    .to = "xxxxxxxx@xxxxxx.xxx"         'contains an email address
    .subject = "Today's Hyperlinks"
    .attachments.add strXlPath          'attaches the saved dashboard in email
    .send
End With

objXb.Close
objXl.Quit
set objXs = Nothing
set objXb = Nothing
set objXl = Nothing

Screenshots:

This is the email which I get. You can see, it has 2 links and an excel file as attachment(This attached excel is my dashboard(test.xlsx). It's contents are same the contents of this email body)

In email body, when I click on mht link, it opens up the IE as expected:

But when I click on the Excel's link, nothing opens up. Even the link color doesn't change on clicking:

How I made sure that excel hyperlink is correct?

This email has an excel attached which has the same contents as email body. I opened up that excel and then clicked on the hyperlink. Both hyper links works as shown below. But I want to open both the links from the outlook's email body directly(not this way).

I have tried to provide as much information as I could. Please let me know if any other information is required.

UPDATE 1:

Here is the Email body source code as requested by @garbb

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 14">
<link rel=File-List href="cid:filelist.xml@01D36E74.B4504EE0">
<link rel=OLE-Object-Data href="cid:oledata.mso">
<style>
<!--table
  {mso-displayed-decimal-separator:"\.";
  mso-displayed-thousand-separator:"\,";}
.xl15
  {padding-top:1px;
  padding-right:1px;
  padding-left:1px;
  mso-ignore:padding;
  color:black;
  font-size:11.0pt;
  font-weight:400;
  font-style:normal;
  text-decoration:none;
  font-family:Calibri, sans-serif;
  mso-font-charset:0;
  mso-number-format:General;
  text-align:general;
  vertical-align:bottom;
  mso-background-source:auto;
  mso-pattern:auto;
  white-space:nowrap;}
.xl65
  {padding-top:1px;
  padding-right:1px;
  padding-left:1px;
  mso-ignore:padding;
  color:blue;
  font-size:11.0pt;
  font-weight:400;
  font-style:normal;
  text-decoration:underline;
  text-underline-style:single;
  font-family:Calibri, sans-serif;
  mso-font-charset:0;
  mso-number-format:General;
  text-align:general;
  vertical-align:bottom;
  mso-background-source:auto;
  mso-pattern:auto;
  white-space:nowrap;}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>300</x:DefaultRowHeight>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>8</x:ActiveRow>
       <x:ActiveCol>4</x:ActiveCol>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>8010</x:WindowHeight>
  <x:WindowWidth>14805</x:WindowWidth>
  <x:WindowTopX>240</x:WindowTopX>
  <x:WindowTopY>105</x:WindowTopY>
  <x:HasEnvelope/>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext="edit" spidmax="3073" />
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table border=0 cellpadding=0 cellspacing=0 width=183 style='border-collapse:
 collapse;table-layout:fixed;width:138pt'>
 <col width=89 style='mso-width-source:userset;mso-width-alt:3254;width:67pt'>
 <col width=94 style='mso-width-source:userset;mso-width-alt:3437;width:71pt'>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl15 width=89 style='height:15.0pt;width:67pt'>Link_To_MHT</td>
  <td class=xl15 width=94 style='width:71pt'>Link_To_Excel</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl65 style='height:15.0pt'><a
  href="file:///C:\Users\Gurman\Work\Misc\Excel%20Hyperlink%20in%20Outlook\MHTFile.mht">MHTLINK</a></td>
  <td class=xl65><a
  href="%5bC:/Users/Gurman/Work/Misc/Excel%20Hyperlink%20in%20Outlook/EXLFile.xlsx%5dSheet1!A1">EXLLINK</a></td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=89 style='width:67pt'></td>
  <td width=94 style='width:71pt'></td>
 </tr>
 <![endif]>
</table>

</body>

</html>

UPDATE 2

This is how both the HyperLink formulas are displayed in Dashboard Excel cells:

MHTLINK(working from within dashboard excel):
=HYPERLINK("C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\MHTFile.mht","MHTLINK")
EXLLINK(working from within the dashboard excel):
=HYPERLINK("[C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\EXLFile.xlsx]Sheet1!A1","EXLLINK")
In Outlook, as shown in the email source in Update 1, the links are:

MHTLINK(working from email body):
<a href="file:///C:\Users\Gurman\Work\Misc\Excel%20Hyperlink%20in%20Outlook\MHTFile.mht">MHTLINK</a>
EXLLINK(NOT Working from email body):
<a href="%5bC:/Users/Gurman/Work/Misc/Excel%20Hyperlink%20in%20Outlook/EXLFile.xlsx%5dSheet1!A1">EXLLINK</a>

UPDATE 3(ISSUE ROOT CAUSE)

In my vbscript code, I have the line:

objXs.Cells(2,2) = "=HYPERLINK(""["&strExcelLink&"]Sheet1!A1"",""EXLLINK"")"

This actually creates a hyperlink to Sheet1 of an Excel Book. The formula which gets written within this cell is also correct which is =HYPERLINK("[C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\EXLFile.xlsx]Sheet1!A1","EXLLINK"). It is important for me to add Sheet1!A1 in this formula as I want the hyperlink to point to a particular sheet(Sheet1 in this case) of the workbook

THE ISSUE COMES WHEN THIS EXCEL FORMULA IS TRANSFORMED TO A LINK IN THE OUTLOOK MAIL, its value becomes:

<a href="%5bC:/Users/Gurman/Work/Misc/Excel%20Hyperlink%20in%20Outlook/EXLFile.xlsx%5dSheet1!A1">EXLLINK</a>

The above link seems to be Incorrect as it has some HEX values like %5b, %5d etc. Hence, it is not working directly.

If I change my VBScript code to create a hyperlink to the Excel Workbook only rather than creating a hyperlink to its "Sheet1", it works fine and I can also open the excel workbook link directly from the email.

Updated line in VBscript:

objXs.Cells(2,2) = "=HYPERLINK("""&strExcelLink&""",""EXLLINK"")"

In Email source, it got transformed to:

<a href="file:///C:\Users\Gurman\Work\Misc\Excel%20Hyperlink%20in%20Outlook\EXLFile.xlsx">EXLLINK</a>

I am able to open the excel workbook using this link directly from the email. But the flaw with this approach is that it may or may not open up Sheet1 in this case. As soon as someone clicks on the hyperlink, may be, Sheet2 opens up instead of Sheet1. Hence, I wanted to handle this thing in my code itself.

So, my question/issue still remains unsolved:

Is it possible to have a hyperlink in Outlook's email body which opens up a particular sheet of an excel workbook?

Might be email security stopping excel links working. Can you right click on the excel link and copy out the URL? Does right click do anything? — Nick.Mc, Dec 06 '17 at 04:44
When I right click, it opens up a context-menu with options `Select HyperLink`, `Open HyperLink` and `Copy HyperLink`. When I click on `Open Hyperlink`, it still doesn't open up any thing. On copying the hyperlink, the full hyperlink saved in that excel cell, gets copied to clipboard as `%5bC:/Users/Gurman/Work/Misc/Excel Hyperlink in Outlook/EXLFile.xlsx%5dSheet1!A1`. But in my dashboard excel, the formula is seen as `=HYPERLINK("[C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\EXLFile.xlsx]Sheet1!A1","EXLLINK")` — Gurmanjot Singh, Dec 06 '17 at 04:47
So does your functioning MHT link look the same? What I don't understand is: I don't see any code in your VBScript that puts the words `Link_To_Excel` into a message body. Where does that come from? — Nick.Mc, Dec 06 '17 at 05:08
Please post the source for your email. (what you see what you see when you click the "view source" menu item.) — garbb, Dec 06 '17 at 05:56
Is it `Sheet1!A1` or `B1`? in `"=HYPERLINK(""["&strExcelLink&"]Sheet1!A1"",""EXLLINK"")"` — 0m3r, Dec 06 '17 at 06:07
@Nick.McDermaid As mentioned in the Background Info, the dashboard excel is actually a template which already contains the column headers. My script just updated the column values. So the texts `Link_to_MHT` and `Link_to_Excel` are already written in those templates at cells(1,1) and cells(1,2) respectively. — Gurmanjot Singh, Dec 06 '17 at 06:34
I suggest that you just do a standard HTML body in your script as you have more control. — Nick.Mc, Dec 06 '17 at 06:37
@Nick.McDermaid Actually this is just a very small part of my dashboard. My actual dashboard has a lot more info including graphs and images which I need to keep in excel file only. I just wanted to have all the excel dashboard contents getting displayed in the email body directly. Hence, I followed this approach. — Gurmanjot Singh, Dec 06 '17 at 06:42
@0m3r Either `A1` or `B1` doesn't make any difference. If I mention `A1`, the cell `A1` gets selected. For now, it is `A1`. — Gurmanjot Singh, Dec 06 '17 at 06:46
Ok. So does the functioning mht link look the same or different? — Nick.Mc, Dec 06 '17 at 06:46
@Nick.McDermaid Please see the update 2. I have mentioned all the link values and formulas — Gurmanjot Singh, Dec 06 '17 at 07:00
@Nick.McDermaid I have tried to remove all the confusion in Update 3. Could you please check it and tell me if it makes some sense? I really want to get this issue solved :( — Gurmanjot Singh, Dec 06 '17 at 08:07
Yes that's much clearer. The hex values are URL encoding. I'm not sure why it is double URL encoding your Excel link but not your MHT link. According to this: https://en.wikipedia.org/wiki/File_URI_scheme , a file URI can only point at a file. Not a piece in the file (like a worksheet). I'd be curious to see what the URI directly in the excel sheet looks like — Nick.Mc, Dec 06 '17 at 09:27
For example %5b = [, %5d = ]. I just think it doesn't know how to convert your link to a HTML link, because there is no way to do that kind of link in a webpage for example — Nick.Mc, Dec 06 '17 at 09:38
@Nick.McDermaid I guess you are correct. Will update this post if I get a solution. Thanks for your help. — Gurmanjot Singh, Dec 06 '17 at 12:53
I would be very surprised if outlook would allow a link to a file to function in an email. Isn't that potentially a security issue? — garbb, Dec 06 '17 at 18:42
@garbb I think, If we can have a link to an excel workbook in outlook, then I don't see any reason why we can't have a link to a Sheet of that workbook. May be someone can correct me, if I am wrong. — Gurmanjot Singh, Dec 07 '17 at 02:51
@Gurman, yes, you're wrong. `HYPERLINK` function applies only to Excel, hence things like `=HYPERLINK("[http://example.microsoft.com/report/budget report.xlsx]Annual!F10", D1)` can exist (and work) only in Excel. In Outlook your so-called "hyperlinks" are converted to the real URL links ([`file:\\` URI](https://tools.ietf.org/html/rfc8089)), which were created for a different purpose without a such option. Microsoft even came up with [their own URI schemes](https://msdn.microsoft.com/en-us/library/office/dn906146.aspx) for Office, which also do not cover (AFAIK) your tasks. — CommonSense, Dec 15 '17 at 15:56
are you sending this to people? If so, will they have access to the location where the document is stored? Or will the excel file also be sent with the e-mail? Would the file need to be saved to a specific location e.g. user's desktop first? — QHarr, Dec 16 '17 at 08:29
@CommonSense Thanks for your inputs. Even I have come to believe that it is not possible to have links to a specific sheet of an excel in Outlook email. — Gurmanjot Singh, Dec 16 '17 at 08:31
@QHarr Yes. The file is actually placed on network. The person who will receive the email has the access to that location. Just for making this issue less complex here, I provided the "local" paths. — Gurmanjot Singh, Dec 16 '17 at 08:34
@QHarr -- I'm just not sure how one would include spaces in a link like that. — ashleedawg, Dec 16 '17 at 08:42

ashleedawg · Answer 1 · 2017-12-20T11:25:49.340

Hyperlink to a specific Excel worksheet:

File:///c:\yourPath\yourFileName.xlsm#yourSheetName!A1

This can be used in an Outlook email. Clicking the link will open c:\yourPath\yourFileName.xlsm on worksheet yourSheetName and select cell A1.

_(Source)

Hyperlink to a UNC (Network) path:

(I'm not able to test these but one or both of these methods should work.)

file://///server/path/to/file.txt

^{(that's five slashes!)}

or

file://server/path/to/file.txt

_(Source)

Hyperlink in HTML Mail:

Another possibility, if the email is in HTML format, would be something like this (which could include fancier formatting like a ToolTip, etc):

  <style> a.tooltips {position: relative; display: inline;} a.tooltips span { position: absolute;  width:240px;  color: #FFFFFF;  background: #000000;  height: 30px;  line-height: 30px;  text-align: center;  visibility: hidden;  border-radius: 6px; } a.tooltips span:after {  content: '';  position: absolute;  top: 100%;  left: 50%;  margin-left: -8px;  width: 0; height: 0;  border-top: 8px solid #000000;  border-right: 8px solid transparent;  border-left: 8px solid transparent; } a:hover.tooltips span {  visibility: visible;  opacity: 0.8;  bottom: 30px;  left: 50%;  margin-left: -76px;  z-index: 999; } </style>
<br><br>
  <a class="tooltips" href="#">\\server\share\docs<span>This is a ToolTip!</span></a>

_(Source)

Edit:

Okay, you did a good job of gathering information so far, so I had hoped that with a couple days you'd do some more research and figure it out... :)

Honestly, I'm not sure of the exact correct method to do the steps that you need to do, but I know the steps that I would take to find out -- and based on my experience, it's tough to predict how much time the process will take, regardless of simple or complex the problem seems to be.

Thus, based on Stack Overflow's "help you with specific issues; not do the work for you" principal, I'll share the steps I would take next, give us updates on what you learn, ask specific questions on what you encounter, and definitely post the working solution once it's all figure out, to help others who encounter the same specific problem. :-)

A beautiful thing with coding (especially with Office) is that there's almost always multiple (completely different) ways of accomplishing the same end result. Some of the ways will be better suited to specific tasks than others, others could be completely interchangeable.

The decision of which method to use can be affected by many things (some obvious, some unforeseen) such as scale of the project, security requirements, knowledge level of both the developer & the end-user, deadlines, how often it will be used and/or will need fundamental changes, and so on. Therefore the best person to decide is you, the developer.

Any tips or code provided by others (including me) on a site like this are going to be based on assumptions that we might not even realize we are making, since nobody knows the "whole situation" better than you. (Askers & Answerers often won't realize the disconnect caused by the XY Problem.)

My point: A common step in developing a section of code to perform a task is to trash what you have and start over. (Surely I'm not the only one that does that, right?!)

Emailing a static Daily Report to multiple recipients as an attachment is 'sooo 1997'!

...and there are many reasons that it's a bad practice. For starters, it's a waste of bandwidth and storage space (a little like making separate VHS copies of Home Alone 3 for each TV in your house) but you can probably think of other reasons too (a big one being that it's not working.)

If I understand properly, you're opening a file, putting two links into cells in that file, closing it, putting the same links in the body of the email, and sending it to a bunch of people?

A major purpose of coding is to remove repetitive/redundant tasks or "things" completely, not just to automate your handling of them.

_{Oh man, tell me nobody's saving each one in a folder with the date as the filename, for historical records. And omg, I hope each user isn't doing that separately. And some people printing each day's report too? Surely not... (I'm having a flashback to a former government job!)}

Based on what I think your end goal is, you should forget about emailing altogether, post the file(s) to a shared location and share a link. This could be an shared location on the internal network, or if everyone's doesn't have access to the network, then a public file sharing location (OneDrive is one of many free options).

This way:

You only have to share a link (only once!) with whomever needs access.
You know for sure that everyone is looking at the same version.
You could update as often or little as needed, even erratically, or take down the file instantly if necessary. ("Oops there was a major mistake on the one we sent out this morning! Everybody stop looking at it now!")
You can add/remove permissions when needed. Perhaps some users could benefit from the ability to modify the file, while others shouldn't be allowed to. (Lean towards the 2nd option unless the 1st is beneficial.)
If anyone's printing the file, make them stop! There are reasons that society as a whole is close to being paperless. Besides saving trees, some of the same reasons apply as why not to have multiple copies of the same file, only worse since incorrect paper copies are even harder to remove.

There's lots of other benefits... but that's just one option. You gave your distribution method idea a good shot & tried to get it working (as did others for you), but sometimes the right answer to a different question.

Get some more ideas by spending some time Googling variations of:

How to share a chart with others
Security on shared files
How does similar company xyz distribute reports?
Sharing confidential Excel data on public networks
Why is it bad to email daily reports
Business email etiquette
How to avoid overloading your recipients
Create & distribute reports people will want to read

...etc...

.

_{Or, I could be on the totally wrong track with the why/when/how/where/who's, but that's all I've got with what I've understood of your issue.}

Thanks for your inputs. I will see how I can change my script to include your solution in that. Actually, the problem is that my script is not writing anything directly in outlook email. It writes everything in a excel file(dashboard) which is then transformed to an outlook email(using mail envelope). So the formula written in the excel `=HYPERLINK("[C:\Users\Gurman\Work\Misc\Excel Hyperlink in Outlook\EXLFile.xlsx]Sheet1!A1","EXLLINK")` gets automatically converted to `EXLLINK`. — Gurmanjot Singh, Dec 16 '17 at 08:48
The hyperlink format which you specified was correct. But I was just not sure how to make changes to my excel formula to get the hyperlink mentioned in your answer. When I implemented the answer provided by SBF in my script, the resulting hyperlink which I got in the final email was the same as mentioned by you. I, thank you, for your answer but I believe that I should accept SBF's answer. I just wish If it was possible to split the bounty. Thanks for your help, again. — Gurmanjot Singh, Dec 19 '17 at 18:01
Of course, _after_ I wrote my edit, I realized you found a solution, haha! But, I believe my edit (although not much of an answer) is relevant nonetheless. Good Luck :-) — ashleedawg, Dec 20 '17 at 11:28
I agree with most of the points made by you especially sharing the location once with the end users and adding/removing permissions. But, the things are the way they are because the end-users/client want it this way. Also, about the point you made on redundant things(excel reports, in this case). So, these are our daily regression/sanity testing reports which get generated daily. We can't just overwrite them everyday as we need to keep them for investigation purposes(in future, if required). Thanks again for your help :) — Gurmanjot Singh, Dec 20 '17 at 14:40

score 3 · Accepted Answer · answered Dec 19 '17 at 16:46

3

Instead of using HYPERLINK which is actually a formula, just add a real hyperlink to the cell:

objXs.Cells(2,2).Hyperlinks.Add objXs.cells(2,2), strExcelLink, "Sheet1!A1", "My Screentip", "EXLLINK"

answered Dec 19 '17 at 16:46

SBF

1,252
3
12
21

Thanks a ton, man. This actually worked. I have just tested this. The link which I get in the email is also fine and is opening the specific sheet, exactly what I wanted. :) – Gurmanjot Singh Dec 19 '17 at 17:55

VBScript_Not able to open Hyperlinks to an excel SHEET(not whole workbook) from outlook's email body directly

2 Answers2

Hyperlink to a specific Excel worksheet:

Hyperlink to a UNC (Network) path:

Hyperlink in HTML Mail:

Edit:

Emailing a static Daily Report to multiple recipients as an attachment is 'sooo 1997'!

This way: