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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

C
  • Question Question
Send Email from Excel - Launch User's Default Email Client
Replies
3
Views
476
Micron
M
spacecaptainsuperguy
  • Question Question
Advice for linked files on network drive
Replies
0
Views
755
spacecaptainsuperguy
spacecaptainsuperguy
O
  • Question Question
Hyperlinks with Pictures and Drop Down Lists
Replies
0
Views
489
Oztours
O
S
  • Question Question
Need to Embed Hyperlink in VBA Email (Lotus Notes)
Replies
3
Views
4K
John_w
J
T
  • Question Question
Hyperlink Issues
Replies
6
Views
441
tjdavis
T

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

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