MS Access VBA / RTF/ Email link

I'm using CDO to handle outbound mail from access.

I've set objMessage.HTMLBody = strbodyMsg

Everything works just how I'd like (IE i receive email, with formatting), but I don't know how to get a link embedded in my body. The body content srtbodyMsg is coming from a lookup table (memo field with rich text format).

Ideally I'd like a nice link like:

<A HREF="http://www.somewhere.com">Somewhere</A> but obviously putting that in with the rest of my body just gives me the literal text as is, with punctuation as is etc.

I made slight progress using:
strbodyMsg = strbodyMsg + ("file://O:\MOBILE~1\SERVIC~3\IcMT\ACTION~1\ICMTDA~2.ACC" + " /cmd" + " ActionID=2")

Open in new window


but then everything after (and including) the /cmd isn't part of the link - I'm guessing because of the space (I got around the space issues using 8.3 format for the main link to the DB).

Anyone help?

Thanks!
LVL 2
RossAsked:
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.

fridomCEO/ProgrammerCommented:
Check function slike URLDecode and URLEncode
http://msdn.microsoft.com/en-us/library/zttxte6w.aspx

There are implemenation for VBA at:
http://p2p.wrox.com/excel-vba/79712-encoding-url-utf-8-vba.html

or maybe have a look at:
http://www.freevbcode.com/ShowCode.asp?ID=5137

Regards
Friedrich
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're using the HTMLBody, which it seems you are, then the link should be represented as a Link. You can include standard HTML tags in the HTML body, and they'll be represented as such.

However, it doesn't look like you're after a link, but rather you want a "link" in your email's body to fire off a program on the desktop, or something of that nature? If so, that's the very definition of spam/malware and wouldn't be allowed (even though I'm sure you're not intending this to be spam/malware).
0
RossAuthor Commented:
Thanks LSM - I'm trying to have an email (generated by the DB) send to the user, including a link in the same email to allow the user to open the DB to a specific record.

IE, if I have the user click on a shortcut on their desktop with it's target set as:

"O:\Mobile Services\Servicecenter\IcMT\ActionLog\ICMT Daily Action Log.accdb" /cmd ActionID=2

it works - opening the correct (ActionID 2) record I want.

However, I can't seem to embed this in an email link allowing the user (who may not have access open) to go directly to the record referenced in the email FROM the email itself.

IE, imagine you place an order with an online retailer. They send you an email saying "click here to check the shipping status of your order" - you click it, and it takes you directly to your exact order, rather than just to their website, where you have to sign in, click orders, click history etc etc.

Hope that makes sense!

Thanks for taking the time to think about it

Ross

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
This syntax works fine for me:

       .HTMLBody = "This is a clickable hyperlink: " & "<a href=" & "www.CNN.com" & ">" & "Link To CNN.COM" & "</a>"


See this link as well:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26606114.html

JeffCoachman
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Right, I understand what you're trying to do - it's just not really possible to do this directly, and there are many problems that can cause this to fail.

YOu might review this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1639722

The post about using the autoexec macro might be something you can adapt. Basically, the autoexec macro looks for a commandline that's being passed in, and can process the arguments in that comment line argument.

0
RossAuthor Commented:
This works (well, it opens the database, just not passing the command line string obviously)

strbodyMsg = strbodyMsg + ("<A HREF='file:///O:\MOBILE~1\SERVIC~3\IcMT\ACTION~1\ICMTDA~2.ACC'>CLICK HERE!</A>")

But this doesn't:

strbodyMsg = strbodyMsg + ("<A HREF='file:///O:\MOBILE~1\SERVIC~3\IcMT\ACTION~1\ICMTDA~2.ACC /cmd ActionID=2 '>CLICK HERE!</A>")

Replacing spaces with %20 doesn't seem to help either.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by "this doesn't"? Does it do nothing, or does it open Access but not the form, etc etc. Using the /cmd action requires more work on your end ...
0
RossAuthor Commented:
Thanks LSM - I'm already doing just that (the command line checking in autoexec). The problem I'm having is embedding that command line switch in the link the DB emails OUT - ie, if I send a link in the email to open the DB it works (but sans command line switch). If I edit the link it sends out to have /cmd whatever on the end, it fails to open - outlook doesn't like it.
0
Jeffrey CoachmanMIS LiasonCommented:
...produces this in the email body
(fully "clickable" and functional Hyperlink)
untitled.JPG
0
RossAuthor Commented:
boaq2000 - thanks for the input but getting links in html email is fine - I need to add a commandline switch to the link - that seems to fail.

   Untitled.png
0
Jeffrey CoachmanMIS LiasonCommented:
Sorry folks, I misunderstood the Question...

FWIW: I have never had good luck opening a databases from a hyperlink and have it "do" anything other than simply open the db...

RossDagley1, what is your ultimate goal here?

...perhaps there is a more efficient approach than using a Hyperlink?
For example, collecting Access data via email:
http://www.youtube.com/watch?v=RXeQeBOPOh8
http://office.microsoft.com/en-us/access-help/collect-data-by-using-e-mail-messages-HA010015427.aspx
0
RossAuthor Commented:
I'm developing a DB to handle incidents that are raised. The incident is created, and when an update is made to the specific incident by someone, the person the incident is assigned to receives an email (as above screenshot) showing the update. If they want to respond, they need to open the DB, find the record, then update it.

Of course, this works fine. However, I've been asked to add a link (of whatever type works!) in the body of the email so the user can just click on the link and be taken to that specific record.

Using the /cmd switch and some work in a function I've created I can monitor the command line given to access as it opens (IE, as I posted in one of the first posts in this thread - a desktop shortcut with "database.mdb /cmd ActionID=2" works just dandy). However, I can't seem to figure out how to get this working as a link the user can click on from the email itself.

I hope thats clear - thank you all for your assistance thus far :)

Ross
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<FWIW: I have never had good luck opening a databases from a hyperlink and have it "do" anything other than simply open the db...>

Me either, and I've tried many different ways. I don't think there is a reliable way to open Access (or any other program) on a remote machine using an emailed message.

That said:

Your trouble is with the /cmd switch? Can you post an example of the email HTML body that is sent out (i.e. the EXACT text that is in your strHTMLbody string) as well as the Source of that HTML body AFTER it's sent out?


0
RossAuthor Commented:
here is the debug.print output of the variable strbodyMsg:
<div>Team, </div>

<div>&nbsp;</div>

<div>An incident has been updated by <font color=red><strong>rdagleyc</strong></font> on the Central Ops Awareness Log.</div>

<div>&nbsp;</div>

<div>Please can you go onto the log and review/respond/close as appropriate.</div>

<div>&nbsp;</div>

<div>The details of the update are:</div>

<div>&nbsp;</div>

<div><font face=Arial size=3>???????????????????</font> </div>

<div><strong>Updater</strong>: <font color="#2F3699">rdagleyc</font></div>

<div><strong>Original issue description</strong>: <font color="#2F3699">Testing Rich text in body of update emails</font> </div>

<div><strong>User's update</strong>: <font color="#2F3699">435345 </font></div>

<div><font face=Arial size=3>???????????????????</font></div>

<div>&nbsp;</div>

<div>Many Thanks</div>

<div><strong>The ICMT Team</strong></div>

<div>&nbsp;</div>

<div>Click here to go to the database:</div><A HREF='file:///O:\Mobile Services\Servicecenter\IcMT\ActionLog\ICMT Daily Action Log.accdb /cmd ActionID=2'>Click</A>

Open in new window


And here is the source code of the received email:
<div>Team, </div>

<div>&nbsp;</div>

<div>An incident has been updated by <font color=red><strong>rdagleyc</strong></font> on the Central Ops Awareness Log.</div>

<div>&nbsp;</div>

<div>Please can you go onto the log and review/respond/close as appropriate.</div>

<div>&nbsp;</div>

<div>The details of the update are:</div>

<div>&nbsp;</div>

<div><font face=Arial size=3>???????????????????</font> </div>

<div><strong>Updater</strong>: <font color="#2F3699">rdagleyc</font></div>

<div><strong>Original issue description</strong>: <font color="#2F3699">Testing Rich text in body of update emails</font> </div>

<div><strong>User's update</strong>: <font color="#2F3699">435345 </font></div>

<div><font face=Arial size=3>???????????????????</font></div>

<div>&nbsp;</div>

<div>Many Thanks</div>

<div><strong>The ICMT Team</strong></div>

<div>&nbsp;</div>

<div>Click here to go to the database:</div><A HREF='file:///O:\Mobile Services\Servicecenter\IcMT\ActionLog\ICMT Daily Action Log.accdb /cmd ActionID=2'>Click</A>

Open in new window


Interestingly, the popup with the error (see image above) has the slash going the other way - don't know if thats important. It's sent to outlook correctly as you can see.

Again - removing the /cmd ActionID=2 part of the string allows the link to become clickable and it opens the database, but without any way of knowing which record to open.
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
RossAuthor Commented:
What I'm asking for cannot be done apparently.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.