To put a hyperlink from access in body e-mail outlook

[Tonaldo]

Hi,

I have a form with:
- e-mail adress
- subject (text)
- reference (hyperlink to a pdf document on the server)

Now I have a VBA code who allows me to open an e-mail in outlook, with the e-mail adress, the subject and in the body the hyperlink:

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.emailadress
.Subject = Me.subject
.Body = Me.referece
 

Problem: the hyperlink in the e-mail is displayed as a text format (like a hyperlink is built in access)

For Example:

2000#P:\Representation\document2000.pdf#

What I want is that in the body of the e-mail, just the display text (here: 2000) is and that the link there still behind is (here: P:\Representation\document2000.pdf ) --> like it is on the access form

So that I can e-mail the link to a person (who has also access to the server) instead of sending the pdf file.

Can anyone help me? Has a tip?

Greetings and thanks,
Tonaldo

[Mariostg]

.Body=Replace(Me.Reference, "#", "")

[Tonaldo]

Thanks for the reply mariostg.
When I change:

.Body = reference
into:
.Body = Replace (Me.Reference, "#", "")

The body of my e-mail looks like:

2000P:\Representation\document2000.pdf

But the link doesn't work, only the # has disappeared...

[Mariostg]

Ah sorry, I did not see about displaying the 2000.
I am not familiar with rtf syntax but if it might help, were you using an html formatted email instead, that would display your link as wanted

Dim link As Variant
link = (Split(Me.Reference, "#"))
mylink = "<a href='" & link(1) & "'>" & link(0) & "</a>"
 

[Tonaldo]

Thanks for the advice.

Now I got this code:

Dim link As Variant
link = (Split(Me.Reference, "#"))
mylink = "<a href='" & link(1) & "'>" & link(0) & "</a>"
 
Dim mess_body As String
        Dim appOutLook As Outlook.Application
        Dim MailOutLook As Outlook.MailItem
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
            .BodyFormat = olFormatHTML
            .To = Me.email
            .Subject = Me.subject
            .Body = mylink
 

The result in HTML code looks good (body e-mail):

<a href='P:\Representation\document2000.pdf'>2000</a>

Only problem is, using outlook2007 which doesn't allow using html code in their e-mail...

[Mariostg]

I did not know about that constraint in Office 2007, I am using Office 2003.
I suggest you open an rtf file that contains a hyperlink with something like TextPad so you can see the tags.

It would likely show something like this:

{\field{\*\fldinst{HYPERLINK "\Representation\document2000.pdf#"}}{\fldrslt{\cf1\ul\f1 2000\f1 }}
 

[Tonaldo]

Thanks again, Mariostg.

I understand what you want to say, but don't understand the code :-).
I don't really know where to paste that part in my VBA code... Can you help me? (sorry I just started learning working with VBA)

Any other suggestion to solve the problem?

[Mariostg]

I am guessing here as I am not familiar with rtf, but in my opinion, based on the provided sample code it would go like so

mylink = "{\field{\*\fldinst{HYPERLINK "" \ " & link(1) & "#""}}{\fldrslt{\cf1\ul\f1" & link(0) & "\f1 }}"
 

Watch for the double quotes, it gets confusing...

[Tonaldo]

I don't really understand the code, but I tried it, so I got this in my VBA...

Dim link As Variant
link = (Split(Me.Reference, "#"))
mylink = "{\field{\*\fldinst{HYPERLINK "" \ " & link(1) & "#""}}{\fldrslt{\cf1\ul\f1" & link(0) & "\f1 }}"
 
Dim mess_body As String
        Dim appOutLook As Outlook.Application
        Dim MailOutLook As Outlook.MailItem
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
            .BodyFormat = olFormatHTML
            .To = Me.email
            .Subject = Me.subject
            .Body = mylink
 

The result in my e-mail is this:

{\field{\*\fldinst{HYPERLINK " \ P:\Representation\document2000.pdf#"}}{\fldrslt{\c f1\ul\f12151\f1 }}

[Mariostg]

Maybe you want to check this it should give you some rtf clues.
http://www.microsoft.com/downloads/e...displaylang=en

I don't use Office 2007 so I can't really help further. I have a routine that creates html emails and it basically goes like so. It creates html formatted emails though.

Function CreateMail(strTo, strCC, strTitle, strBody)
Dim objMailItem As Outlook.MailItem
Set olkapp = New Outlook.Application
Set olknamespace = olkapp.GetNamespace("MAPI")
Set objMailItem = olkapp.CreateItem(olMailItem)
With objMailItem
    .To = strTo
    .cc = strCC
    '.Recipients.ResolveAll
    .Subject = strTitle
    '.Body = strBody
    .Display
    .HTMLBody = strBody
End With
 
Set objMailItem = Nothing
Set olknamespace = Nothing
Set olkapp = Nothing
End Function
 

[Tonaldo]

Ok, thanks for the help Mariostg.

Looked into the rtf clues but didn't find a solution.

Anyone else has a suggestion to solve my problem?

[Tonaldo]

okay, got an answer

just needed to add "file://"

But thanks Mariostg again, wouldn't have found it without your help! (to split the hyperlink first)!

[Mariostg]

Oh this is what you were looking for... :) Gee I use this all the time. You use <a href=... if you want to refer to a website. And file:// to refer to a file in a folder.
By the way, if you want to avoid problems with such links, avoid spaces when you name your files and folders.

[Tonaldo]

Yeah, thanks for the help and the tips!