469,271 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. Dim mess_body As String
  2. Dim appOutLook As Outlook.Application
  3. Dim MailOutLook As Outlook.MailItem
  4. Set appOutLook = CreateObject("Outlook.Application")
  5. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  6.  
  7. Set appOutLook = CreateObject("Outlook.Application")
  8. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  9. With MailOutLook
  10. .BodyFormat = olFormatRichText
  11. .To = Me.emailadress
  12. .Subject = Me.subject
  13. .Body = Me.referece
  14.  
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
Sep 21 '10 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. Dim link As Variant
  2. link = (Split(Me.Reference, "#"))
  3. mylink = "<a href='" & link(1) & "'>" & link(0) & "</a>"
  4.  

13 14851
Mariostg
332 100+
.Body=Replace(Me.Reference, "#", "")
Sep 21 '10 #2
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...
Sep 21 '10 #3
Mariostg
332 100+
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
Expand|Select|Wrap|Line Numbers
  1. Dim link As Variant
  2. link = (Split(Me.Reference, "#"))
  3. mylink = "<a href='" & link(1) & "'>" & link(0) & "</a>"
  4.  
Sep 21 '10 #4
Thanks for the advice.

Now I got this code:
Expand|Select|Wrap|Line Numbers
  1. Dim link As Variant
  2. link = (Split(Me.Reference, "#"))
  3. mylink = "<a href='" & link(1) & "'>" & link(0) & "</a>"
  4.  
  5. Dim mess_body As String
  6.         Dim appOutLook As Outlook.Application
  7.         Dim MailOutLook As Outlook.MailItem
  8.         Set appOutLook = CreateObject("Outlook.Application")
  9.         Set MailOutLook = appOutLook.CreateItem(olMailItem)
  10.  
  11.             Set appOutLook = CreateObject("Outlook.Application")
  12.             Set MailOutLook = appOutLook.CreateItem(olMailItem)
  13.             With MailOutLook
  14.             .BodyFormat = olFormatHTML
  15.             .To = Me.email
  16.             .Subject = Me.subject
  17.             .Body = mylink
  18.  
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...
Sep 21 '10 #5
Mariostg
332 100+
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:
Expand|Select|Wrap|Line Numbers
  1. {\field{\*\fldinst{HYPERLINK "\Representation\document2000.pdf#"}}{\fldrslt{\cf1\ul\f1 2000\f1 }}
  2.  
Sep 21 '10 #6
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?
Sep 22 '10 #7
Mariostg
332 100+
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
Expand|Select|Wrap|Line Numbers
  1. mylink = "{\field{\*\fldinst{HYPERLINK "" \ " & link(1) & "#""}}{\fldrslt{\cf1\ul\f1" & link(0) & "\f1 }}"
  2.  
Watch for the double quotes, it gets confusing...
Sep 22 '10 #8
I don't really understand the code, but I tried it, so I got this in my VBA...

Expand|Select|Wrap|Line Numbers
  1. Dim link As Variant
  2. link = (Split(Me.Reference, "#"))
  3. mylink = "{\field{\*\fldinst{HYPERLINK "" \ " & link(1) & "#""}}{\fldrslt{\cf1\ul\f1" & link(0) & "\f1 }}"
  4.  
  5. Dim mess_body As String
  6.         Dim appOutLook As Outlook.Application
  7.         Dim MailOutLook As Outlook.MailItem
  8.         Set appOutLook = CreateObject("Outlook.Application")
  9.         Set MailOutLook = appOutLook.CreateItem(olMailItem)
  10.  
  11.             Set appOutLook = CreateObject("Outlook.Application")
  12.             Set MailOutLook = appOutLook.CreateItem(olMailItem)
  13.             With MailOutLook
  14.             .BodyFormat = olFormatHTML
  15.             .To = Me.email
  16.             .Subject = Me.subject
  17.             .Body = mylink
  18.  
The result in my e-mail is this:

{\field{\*\fldinst{HYPERLINK " \ P:\Representation\document2000.pdf#"}}{\fldrslt{\c f1\ul\f12151\f1 }}
Sep 22 '10 #9
Mariostg
332 100+
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.
Expand|Select|Wrap|Line Numbers
  1. Function CreateMail(strTo, strCC, strTitle, strBody)
  2. Dim objMailItem As Outlook.MailItem
  3. Set olkapp = New Outlook.Application
  4. Set olknamespace = olkapp.GetNamespace("MAPI")
  5. Set objMailItem = olkapp.CreateItem(olMailItem)
  6. With objMailItem
  7.     .To = strTo
  8.     .cc = strCC
  9.     '.Recipients.ResolveAll
  10.     .Subject = strTitle
  11.     '.Body = strBody
  12.     .Display
  13.     .HTMLBody = strBody
  14. End With
  15.  
  16. Set objMailItem = Nothing
  17. Set olknamespace = Nothing
  18. Set olkapp = Nothing
  19. End Function
  20.  
Sep 22 '10 #10
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?
Sep 23 '10 #11
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)!
Sep 23 '10 #12
Mariostg
332 100+
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.
Sep 23 '10 #13
Yeah, thanks for the help and the tips!
Sep 23 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by c676228 | last post: by
1 post views Thread by sweetpotatop | last post: by
1 post views Thread by Arthur Dent | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.