Send email with hyperlink

I am trying to have an email created from Access which has a hyperlink in the body of the message.  The code I have now is working fine and the hyperlink is fine.  What I want to do is change the hyperlink to a display name/word

I am currently using the docmd.sendobject with the following syntax...

DoCmd.SendObject , , acFormatHTML, [EmailAddress], , , "Request pricing for new product", "Dear consumer," & Chr(10) & Chr(10) & "The product you requested can be found on the website:" & Chr(10) & Chr(10) & "" & Me![ProductDatabaseList]

So... Instead of showing in the body of the email, I would like it to show something like "Click Here"

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.

Leigh PurvisDatabase DeveloperCommented:
Are you using Outlook? (Not express)
Could you perhaps create an email through automation - and use the BodyHTML to create the desired text?
CTSDevelopmentAuthor Commented:
I am using express and outlook.  

How would I do it through automation?
Leigh PurvisDatabase DeveloperCommented:
Try something like

On Error Resume Next

    Dim objOutlook As Object
    Dim objItem As Object
    Dim strBody as String

    strBody = "Dear consumer,<br><br>The product you requested can be found on the website:" & _
            "<br><a href=""" & _
            Me![ProductDatabaseList] & """>Click Here</a>"

    Set objOutlook = CreateObject("Outlook.Application")
    Set objItem = objOutlook.CreateItem(0)
    With objItem
        .To = Me![EmailAddress]
        .Subject = "Request pricing for new product"
        .HTMLBody = strBody
    End With

    Set objItem = Nothing
    Set objOutlook = Nothing

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
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

CTSDevelopmentAuthor Commented:
I tried putting your script into a command button but it doesn't seem to do anything when I click on it.  Do I have to declare or install something to get this to work?  
Leigh PurvisDatabase DeveloperCommented:
Try commenting out the
On Error Resume Next
line - and see what the error is it gives.
CTSDevelopmentAuthor Commented:
I get a runtime error '-2147024770 (8007007e)'

Automation error
The specifed module could not be found.

The error happens on the:

Set objOutlook = CreateObject("Outlook.Application")
Leigh PurvisDatabase DeveloperCommented:
So you perhaps don't have full blown Outlook installed?
(Or is it not set up?)

Just for testing, I created a blank database, pasted LPurvis' code in a new form, i added a few fields to match your email construct and it worked perfectly. Outlook popped up with the email constructed. I agree with him; I think it's an issue with how Outlook is loaded (or not completely loaded) on your workstation.

I did some more research and found a thread in an Outlook help group (sounds like an AA group for chronic emailers :-). This person solved his/her problem by reinstalling Outlook. It might be a risky proposition, but it worked in this case.


Here's the thread:
Yes one thought. Is this an upgrade from an earlier version of Outlook? I've
seen some similar problems when Outlook is upgraded, especially if it wasn't
uninstalled when the upgrade took place. Registrations are all fouled up and
CreateObject then fails. Most often I've seen it with Outlook 2002 -->
Outlook 2003.

Ken Slovak

"Thatch"  wrote in message

> The error happens right after the CreateObject("Outlook.Application") and
> also right after the GetObject("Outlook.Application"). I have run the
> Detect
> and Repair utility and I have disabled ALL Addin applications in the Addin
> Manager.
> In essence, when I create the Outlook object using CreateObject, I get
> the
> "Cannot create ActiveX component." error.
> The only thing I have not done is uninstall Office (with Outlook with it)
> and re-install Office.

> The problem here is that I have quite a few customers that have reported
> the
> same issue with Outlook 2003. Everything works great if  Outlook is open,
> but
> if Outlook is closed and the CreateObject is used, then the stuff does not
> work.
> In some cases, the customers re-install the whole operating system and
> Office and then the CreateObject works (which is something that I really
> do
> not want to do).

> So, any other thoughts???

 From:  Thatch - view profile
Date:  Tues, Feb 15 2005 6:29 pm  

I've uninstalled office 2003, deleted all and any remaining files
on my system of previous Office installations. Scary somewhat to do it.
However, upon re-install, the createobj now works fine. Also, I didn't lose
any information which is what I was fearing (I have 19 email accounts).

Thanx for the help.
CTSDevelopmentAuthor Commented:
Ok... I will try that but a major problem for me is that not all users use Outlook.  I have a bunch of account who use Outlook Express to send their email.  If anything, I think there are more users on the Express than the full version of Outlook.  Can the code be modified in any way to accomodate Express?  

Is there no simple way to have a hyperlink in Outlook Express have a display name?

I know that hyperlinks can be broken into 4 parts.  The following won't work as a hyperlink:

But if I remove the Click# all is well and Express recognizes it as a hyperlink... Is Express just retarted or do I have to code the entire thing in HTML?  I'd prefer not to since I have it working almost perfectly as it sits but I just want to clean it up so that I don't have to show the full hyperlink.
Leigh PurvisDatabase DeveloperCommented:
I can understand that desire - but not having to show the full hyperlink is achieved using HTML.
And the limited (albeit handy) functionality of SendObject can't do everything.
Leigh PurvisDatabase DeveloperCommented:
"It is assumed that any participant not responding to this request is no longer interested in its final disposition."

Hmm - I never really paid attention to that line before.
My lack of comment generally at moments like this isn't a lack of interest - more a case of laid back acceptance ;-)

Although, that said, in this case I don't have much to say still :-S

No is a valid answer, even if it's not one we want.
There are limitations to everything, each at its own level.  OE limitations are well below those of Outlook.  It's just a fact of life.
I don't blame MS for not making OE all singing and dancing - why should they?  They basically give it away.

Oh - see I did have something to say after all :-)
I'll post the following recommendation, which I believe effectively addresses the original question in this thread.

LPurvis {http:#16253528}

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.