Using VBA to send hyperlink in email

[DavidG3276]

I have an Access 2010 database which has a table with several fields with the Hyperlink data type. Some of these hyperlinks reference files stored in a SharePoint database. I am relatively new to using hyperlinks. The task is to insert those hyperlinks into an email using VBA so that the recipient can just click on them to view the documents.

I have no problems with using VBA to create emails or to add attachments. My issue is with adding hyperlinks to the body of the email. Any help would be greatly appreciated. Thanks!

 

[MikeLeBen]

You should use .HTMLBody on your e-mail object and add HTML links (i.e.:<a href="url"></a>)

 

[DavidG3276]

Please forgive my ignorance but how do I figure out what the url is for a hyperlink particularly if it's pointing to a SharePoint database? Thanks for your help!

 

[MikeLeBen]

I was under the impression the url's were stored in your table.
If this is the case you could just use it as a string and feed it to your e-mail sub.

Maybe I misunderstood your question?
Basically you want to send hyperlinks to a SharePoint database by e-mail?

 

[DavidG3276]

My question is how do I convert the value for a hyperlink field in an Access 2010 table to text including the full path? Once I have the text, then I can get it into the email using VBA. I just don't know how to convert the hyperlink to provide me with the full link (URL and filename). Hope this helps!

 

[MikeLeBen]

As far as I know hyperlinks are stored in access tables as text fields, that are highlighted and clickable in datasheet view, but text fields nonetheless (for your intents and purposes).

Therefore depending on the purpose of your application you could use any of the several ways access offers you to obtain that text field and assign it to a string in your routine.

You could use SQL, recordsets, or a DLookup.

Look at the following pseudo-code

Dim myHyperlink As String

myHyperlink = DLookup("link", "tblLinks", "ID = x")

myemail.HTMLBody = "Hello! Visit: <a href=""" & myHyperlink & """>this site!</a>"

 

[DavidG3276]

I have tried this and wind up with #David%20P.%20Graf%20Hours%20for%202010-2012.xlsx# as the text. I've tried inserting this into the email and it just shows up as text. What am I doing wrong?

 

[MikeLeBen]

The %20 parts are there because spaces in URL's are encoded like that.

Also, it appears Access handles hyperlink field data by wrapping it in number signs ('#', I have never used this field type).

Furthermore, you have been returned what appears to be only a filename, and not a URL; unless the resource is in the same folder as your database application, I am going to assume it's not going to be found.

I found a resource on the web, it appears you should also set the mail.bodyformat property to olFormatHTML.

Let us know what happens.

 

[DavidG3276]

Thank you for your help! Until I can get the URL info somehow, I'm at a standstill. I've tried using hyperpart but that doesn't show the URL either.

 

[MikeLeBen]

I reckon the URL info is not stored in the table you stated would contain it. Does the location of these files change? Does it make sense to add this path to your hyperlink field?

If your hyperlink field only holds filenames, I would think it makes sense to do so.

 

[DavidG3276]

I will be finding out from the client if the url is absolute for these documents. If so, they can provide it to me and I should have enough info to build a complete string to insert a hyperlink in the email. Thanks so much for your help! It just seems strange to me that there isn't already some pre-built command or tool within Access to provide programmers with the complete URL.

 

[MikeLeBen]

I am not sure what you mean by that. With the potential number of URL's being infinite, what feature could possibly predict which URL you are going to need?

You may prompt the user to enter the URL, using a folder picker function, if that is what you mean.

 

[DavidG3276]

Access has to know the particular URL. Otherwise, the hyperlink within the field in the Access table couldn't open the file or webaddress.