Outlook Link passing Parameter to Excel spreadsheet

[Torc]

I have a process which produces an excel spreadsheet of results.

The process also sends an Email out to one or more users.

I have been asked to include a link on the Email for the recipients to
click, which will open up the results spreadsheet and kick off a macro.

I know that using the following format will allow me to include a link
to a file.

<file:c:\Documents\data.xls>

I have found the following thread which gives me the code to retrieve
the Command Line Parameters,

http://www.vbforums.com/showthread.php?t=366559

How can I pass a Command Line Parameter in the link?

Alternatively, is it possible to include a button on the Email which
would run a macro which populates the fields on the spreadsheet?

[Webtest]

<file:c:\Documents\data.xls>

Instead of trying to open the file directly, can you link to a shortcut or to a Batch (.bat) file instead? Probably not if the parameters are dynamic and you have to change them on the fly. Otherwise, you can certainly embed the parameters in the shortcut or batch file. My parameters are static and never have to be changed.

I have been using Declan's code for a long time now, and it has worked perfectly for me.

[RobDog888]

The answer is right there in the thread. Pass theh /e and then /whatever for your next argument.

excel \\AUSCSRR206\DECLAN_X_KENNY$\DK_Test.xls /e/dk/var2

[Torc]

Unfortunately it seems I cannot include the parameters in the link line.

When I put the parameters as part of the link in the Outlook
email I have with a link as follows

<file:c:\Documents\data.xls /e/MyParam>

It takes the whole of the link, including the Paramaters as part of the file name, which it naturally says that it cannot find.

One of the problems is that I would need to give a different parameter for
each person the email is sent to, whilst the spreadsheet they would all be working on would remain the same.

I have not used batch files for years, since MS-Dos.

I will try to build and run a batch file.

Will let you know how it turns out.

Thanks.

[RobDog888]

Ok, commandline parameters are different from arguments passed in to a Link.

[Webtest]

torc:

Once a person is identified, is that person's parameter constant from then on? If so, the batch file is easy. Just put the "run" command on a line in a text file and name it ".bat" instead of ".txt".

rem This is the batch file to start excel
rem I think that Windows can always find the system name "Excel"
rem You may need quotation marks around the file name if there are embedded spaces, just like in a shortcut
excel C:\++TEST-Regions.xls /e/myParameter

Now you can just link to the batch file and it will start Excel opening the file and passing the parameters.

[Torc]

Hi Webtest,

Yes, the parameter for a person will be constant.

Unfortunately I have tried using a batch file as follows

rem Batch file test
excel c:\documents\testbed.xls /e/MyParam

Unfortunately this did not work, it did not open Excel.

I will try again tomorrow with the full path name for excel.

The only problem with that is I do not know if I can guarantee that
all the users will have Excel in the same location that I do.

I have come across another weird problem which is that if I put code in
which will populate a module in my results spreadsheet the system will start
throuwing a wobbly every now and then about a sharing violation and crash,
losing any work I have done.

I have come up with a possible solution, which is to have the batch file calling
a second workbook which would open the results workbook and making any changes necessary.

By the way, do you know what would happen if the spreadsheet that was opened then deleted the batch file that called it?

[Webtest]

What happens if, on any machine's TASK bar, you click "Start" > "Run" and type:

Excel <Enter>

in the "Open" text box???

I had the same problem and I wrote a quick macro to find Excel's hiding place. I'll see if I can find it. Basically, it just wanders through "Program Files" looking for "Excel.exe".

[RobDog888]

From my post #3, you dont need to know its location nor from my new example below.

'From within Excel's VBA or from VB 6.
Option Explicit
 
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
                        ByVal hwnd As Long, _
                        ByVal lpOperation As String, _
                        ByVal lpFile As String, _
                        ByVal lpParameters As String, _
                        ByVal lpDirectory As String, _
                        ByVal nShowCmd As Long) As Long
 
Private Sub Command1_Click()
    ShellExecute Me.hwnd, "open", "excel", "\\COMPUTERNAME\SOMESHARE$\Test.xls /e/var1/var2", "C:\", 1
End Sub

Of course you still need the code to parse the commandline arguments.

[Webtest]

If you just want to know where Excel is hiding, open a DOS window (Start > Programs > Accessories > DOS Window) and do the following:

C: <Enter> (Make sure you are on drive with the Excel executable)
cd \ <Enter> (Make sure you are in the ROOT directory for starters)
dir excel.exe /s <Enter> (Find all instances of Excel.exe)

It will show you a directory listing of all instances of Excel on the drive.

[Torc]

Problem is that that will only give me where Excel is hding on a particualr machine.

Problems begin where Excel might be installed in a different location; rare in a
company environment I know, but not impossible.

I suspect that I will have to just set it up and hope for the best.

Meantime I would still be interested in knowing if it is possible to construct and send an Email message with a Command button in the message which kicks off a macro embedded in the Email Message.

[Webtest]

Torc:

Yes, having Excel in a different (uncontrolled) path on each machine is really a pain. I only had about 10 machines to worry about, all here in my office. I was thinking to suggest you try to set up something in the Registry, but again, you would have to do that on every machine.

Does anyone have any thoughts about why some machines (mine in particular - running Win 2K Pro) recoginzes the system handle "Excel" while Torc's doesn't? Is there something to keep in mind during installation? Is this registry entry something that can be done trivially "post installation" of Excel?

[Torc]

I have worked out why I could not start Excel without including the full path.

There is an environment variable PATH which is set with a list of directories
which are searched for a program name, if it could not be found in the
current directory.

My PATH environment variable does not include the directory where EXCEL.EXE is located.

I suspect that yours does, Webtest.

Unfortunately I will not be able to get it added to the PATH variable.

Going back to using a batch file to launch Excel I came across a stumbling block.

Due to certain constraints I cannot write a Macro into my results spreadsheet.
The results spreadsheet falls over when opened, has sharing violations
gets saved as an recoverable file which locks and does not open, etc.

So I decided to use another spreadsheet which would hold the code to
open my results spreadsheet, and then perform any necessary actions on it.

However when I used the following line in the batch file

"C:\Program Files\Microsoft Office\Office10\excel" "F:\TestBed.xls" /e"F:\Results Store\Results200702February01.xls"

It tried to open the spreadsheet TestBed.xls twice losing the parameter
/e"F:\Results Store\Results200702February01.xls", but when I removed
the path information from the results spreadsheet it worked as it was
supposed to.

"C:\Program Files\Microsoft Office\Office10\excel" "F:\TestBed.xls" /e"Results200702February01.xls"

Can you shed any light as to why this happened.

[Siddharth Rout]

I have a process which produces an excel spreadsheet of results.

The process also sends an Email out to one or more users.

I have been asked to include a link on the Email for the recipients to
click, which will open up the results spreadsheet and kick off a macro.

Does this helps?

Create a directory 'temp' in c:\
Create a test file and name it aaa.xls in that directory
Record a macro and place the code in the workbook open event
Save and close the file

and then
Click the commandbutton in the email attached as zip file...

The best part is you don't need to check where excel is installed, just make sure that in the References, select the Microsoft Excel Object Library as i have done in the email attached...

Hope this helps...

[Torc]

Hi koolsid,

Thanks, unfortunately we use Outlook 2002 here so I cannot open the
email message.

Is there any possibility you could save the message is Outlook 2002 format?

Thanks

[Siddharth Rout]

I am at the office now.

Will try and do that in the evening if that is okay...

[Torc]

Fine,

Thanks

[Webtest]

You dropped a required slash out of the parameter call ... the call template is:

"then /e and then /whatever for your next argument"

Your call Is:
/e"F:\Results Store\Results200702February01.xls"
Should Be:
/e/"F:\Results Store\Results200702February01.xls"

Notice the added slash right after the "e". The "/e" is a flag to look for an extended or 'extra' parameter, and the following "/" indicates the start of the extended parameter. Without the "/" the code can't find the start of the parameter string.

[Torc]

Tried it with the /e/

Still got the same results.

[Siddharth Rout]

Is there any possibility you could save the message is Outlook 2002 format?

Doesn't give me any such option... strange

[Torc]

Is it possible you could outline the steps you took to create the mail?