Create hyperlink in email sent via vba to execute a batch file with command line parameters

I am trying to build an Email that contains a hyperlink in visual basic to execute a batch file with command line parameters in Access 2010.

this Shell run in vbs works fine:

shell.Run """\\\Root\Custom_Reports\OPR00  Operations Custom Reports1.vbe"" """ & NCR_ID & """ ",,false

I would like to create a hyperlink in an email to open the above batch file from within Access 2010.

txtEmail = "NCR-Mgmt@mycompany.com"
subjMessage = "Non Conformance Report ID: " & Format(Me.ID, "00000")                

bodyMessage = "here is where i need to create the hyperlink to open \\Root\Custom_Reports\OPR00  Operations Custom Reports1.vbe"" """ & NCR_ID"              

 DoCmd.SendObject acSendReport, "Non Conformance Report", acFormatPDF, [txtEmail], , , [subjMessage], [bodyMessage], False

Open in new window

BFanguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Before you spend a lot of time trying to code that, you might want to just send it in email to yourself to see if Outlook and your anti-virus will even allow that link to work.  A lot of active links are blocked in email clients.
0
BFanguyAuthor Commented:
Thanks, will try.
0
Jeffrey CoachmanMIS LiasonCommented:
Then forget sendobject.
Sendobject will not accept HTML in any of its arguments

You will have to create an email object:
http://support.microsoft.com/kb/161088
...then use .HTMLBody, ...instead of .Body
Here is a sample of the .htmlBody to insert a hyperlink to an Access database:
    .HTMLBody = "Here is your Hyperlink: " & "<a href='c:\YourFolder\Yourdatabase.accdb'>Hyperlink</a>"

However, this may all be a moot point because you cannot specify an Access command line argument in a hyperlink "C:\YourDatabase /x YourMacro

You can try it with your batch file theory and let us know what happens...

Finally DaveBaldwin is correct, when you do this, you will be bombarded with "Outlook automation" and "unsafe Hyperlink" alert popups.

There are ways around these alerts, but remember, overriding the alerts open you up to malicious intent.

...keep us posted

;-)

JeffCoachman
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We see this question quite often in the Access zones, and the answer is pretty much all the same:

You can't do this. It's the very definition of a virus (running an executable program on the target machine by clicking a link in an email). Any decent (and most not-so-decent) antirvirus software will disallow this immediately.

I'm not sure what you're trying to do with the batch file. Could you elaborate on that more?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BFanguyAuthor Commented:
Just trying to send an email notification that a new record was entered in a database and a hyperlink to open that record from the email.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could send the notification with sufficient information in the email to allow the user to find the record. For example:

"Sales Order SO-123456 has been entered for Toro Mfg on 09-25-2013"

You would then provide the user with an easy-to-use search facility they could use to navigate directly to the sales order. So they'd open the database, open the search screen and enter the SO number, or select the Customer, or whatever makes most sense in your database.
0
BFanguyAuthor Commented:
that's what I currently have, but they want to be able to just click and open.  we have other web based software that they are used to getting emails with hyperlinks that opening the software to the form then need.   guess they are just spoiled.  ;)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's a bit different - opening a web-based app and filtering to a specific record. That's a fairly straight forward web programming matter.

Opening a desktop-based app from an email click ... that's an entirely different matter.

Ask your IT people what THEY think about that :)
0
BFanguyAuthor Commented:
Unfortunately I am the IT people... lol

I guess I'll just tell them it can't be done.

thanks
0
Jeffrey CoachmanMIS LiasonCommented:
I'm still confused too...
Don't they have a shortcut to the database?
You could do this:
Email them the "RecordID" (Primary key value) of the record. (not as a hyperlink, just as plain text)
Then on your main form (the form that opens when the database opens) create a textbox for them to paste in this the ID Number
Then add code to a button to open the form to this record

    DoCmd.OpenForm "YourForm", , , "YourIDField=" & Me.txtID


JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
...and also remember that the 'New' RecordID they get in the email, may not always be the "most current" RecordID.
If a person does not bother opening their email for a week, ...the "ID", in the email may have been superseded by other ID's already...

In this case why not set up a form to open up to the most recent record.
There are a couple of ways to do this (Sorting on a date field, sorting to see the highest ID first, ...etc)
Here is a simple way, ...just move to the last record...
    DoCmd.OpenForm "YourForm"
    DoCmd.GoToRecord , , acLast

You can set this form up as your startup form, or put a button on your startup form to run this code...

JeffCoachman
0
BFanguyAuthor Commented:
boag, yes they have a shortcut to the database and I am giving them the record id and they can search / paste the id and I do already open the form with the last record entered shown,  they are just lazy and wanted the email to contain a hyperlink that opened the form without them having to search.

thanks everyone
0
Jeffrey CoachmanMIS LiasonCommented:
Then as LSM mentioned, this IS a common question here, but there is no easy way to do this,
...because of the security concerns we all mentioned...

But again, there is no need to "search" for the record (per se) , they would simply paste in the value, then click the button and the form opens to that record (filters for that record).
0
BFanguyAuthor Commented:
thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.