Menu
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 = "[email protected]" '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:[email protected]"> <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?
462
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)
(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)
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)
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?!)
...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).
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
