Email Hyperlink Question

UniMord

UniMord

Active Member
Joined
May 6, 2002
Messages
311
I have a spreadsheet residing on a network drive that is updated regularly.
Rather than emailing copies of it to co-workers, I email them (from Outlook) a link to it, so it can never accidentally get forwarded to an outside party. (This also saves space on the server and prevents people from consulting outdated data.)

So far, so good. However, I usually want the recipient to open the spreadsheet to a specific cell.

I'd like to enter something like:

X:\Company Secrets\High Voltage\Keep out\Data Our Competitors Would Die to Get Their Claws On.xlsx#'March 2011'!Z100

The spreadsheet will open, but my reference to 'March 2011!Z100 in the hyperlink just gets ignored.

If there is no simple way of doing this, is there perhaps a way for VBA to determine that the spreadsheet was opened via a hyperlink and extract the range information from the hyperlink opening it (in which case, it'd be a skip and a jump to open to the desired spot)?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Works for me when I tried, only difference between my URL and yours is the '

mine looks like this X:\Company Secrets\High Voltage\Keep out\Data Our Competitors Would Die to Get Their Claws On.xlsx#March 2011!Z100
 
Upvote 0
If it works for you and not for me, can it have something to do with the Excel version or Operating System? I'm running Excel 2010 and Windows 7 Pro.

It also seems that when I link to .xlsx files with a reference, I get an error, and with .xls files, the spreadsheet opens, but not to the reference inside the workbook.
 
Upvote 0
SOLVED! Email Hyperlink Question

Eureka!

I've been working on this, on and off, and I've finally gotten to the answer.

There are 2 issues involved. In order to select the range referenced in my hyperlink, I need to include it as a command line argument.

For example, "U:\Excel\Excel Testing\Link2SpecificCell.xlsm" /e/Sheet2!$A1:$G$1
(Those double quotes are important if there are spaces in your path.)

It is important to have the /Range follow immediately after the /e, with no spaces for this to work.

Now for the code. I started with the code shown in http://www.vbforums.com/showthread.php?p=2234990, but the Workbook_Open function needed modification to make it usable.

In a Standard Module, enter:
Code: 
Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
 
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

Then, in the Workbook module, enter:
Code: 
Private Sub Workbook_Open()
    Dim CmdRaw As Long
    Dim CmdLine As String
    Dim args
 
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    On Error GoTo noArgs
    args = Split(Split(CmdLine, "/e/")(1), "!")
    Sheets(args(0)).Activate
    Range(args(1)).Select
noArgs:
End Sub

This does the trick if you just want a Windows shortcut. However, you can't enter hyperlinks with arguments in Outlook. I found the solution to that at: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Q_23546832.html.

The trick is to make a batch file and instead of a link to the document, you enter a link to the batch file. So in my case, I included in my email the following link: U:\Excel\Excel Testing\Link2SpecificCell.cmd.

Then, in that file, I entered:
Code: 
@echo off
call excel "U:\Excel\Excel Testing\Link2SpecificCell.xlsm" /e/Sheet2!$A1:$G$1
This now allows me to email links to people in my company, and have it open up to the specified range.

This entailed a LOT of research and testing. I will be very gratified if this will be of help to anyone else who might need this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

spacecaptainsuperguy
  • Question
Advice for linked files on network drive
Replies
0
Views
348
spacecaptainsuperguy
spacecaptainsuperguy
O
  • Question
Hyperlinks with Pictures and Drop Down Lists
Replies
0
Views
428
Oztours
O
S
  • Question
Need to Embed Hyperlink in VBA Email (Lotus Notes)
Replies
3
Views
4K
John_w
J
T
  • Question
Hyperlink Issues
Replies
6
Views
380
tjdavis
T
L
  • Question
VBA Code to insert hyperlink into an email
Replies
0
Views
744
L_Corc
L

Forum statistics

Threads
1,215,358
Messages
6,124,487
Members
449,165
Latest member
ChipDude83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top