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

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.}

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"