Email Hyperlinks to workbooks!

V

VTB

Hi all, Im new to this forum so be gentle with me.

I have set up a macro in Excel to send out HTML emails with hyperlinks to XLS files.

The string value for the link to a XLS file is in a cell on a worksheet.
myFile = Range("A1")
The string value in "A1" is as follows: "C:\CFSN\ISS Projects\ProGenII.xls"

I have no problem getting the hyperlink into the email. The problem is that where there is a spaces in the string value, the hyperlink that ends up on the email has a links that only represent the the string upto the first space.

In the above example, the hyperlink reference on the email would be
"C:\CFSN\ISS"
__________________________________________________________
Here is a snippet of the code for this:

Dim myFile as String
myFile = Range("A1")

myItem.HTMLBody = "<HTML><BODY><A href=" & myFile & ">Click Here to open the file</BODY></HTML>"
__________________________________________________________

I have tried putting a '%' symbol in place of the space, I have tried the '<>' brackets at the start and end of the cell contents, I have tried both together.

I seriously need some help with this one.

Any Idea's, Vincent...

PS. I have put together an example workbook of the problem that I can send through to anyone who is prepared to take a look at my problem.

Thanks in advance.
 
D

David McRitchie

Hi Vincent,
if you use the % for a space it would be the hex value
for a space so you would use %20

In VBA you can use Replace or prior to X2k you can use
application.substitute to change the values.

myItem.HTMLBody = "<HTML><BODY><A href=" _
& TRIM(Replace(myFile," ", "%20")) _
& ">Click Here to open the file</BODY></HTML>"

In working with HTML some substitutions you might encounter:
if you have embedded % use %25
if you have embedded @ use %40
if you have embedded & use %38
if you have embedded space use %20

For more information on email there is a small section in
http://www.mvps.org/dmcritchie/excel/mailmerg.htm#mailto
For more information on generating HTML from a spreadsheet
http://www.mvps.org/dmcritchie/excel/xl2html.htm

Do you really need to use HTML to be able to get a link
in your email.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

VTB said:
I have set up a macro in Excel to send out HTML emails with hyperlinks to XLS files.

The string value for the link to a XLS file is in a cell on a worksheet.
myFile = Range("A1")
The string value in "A1" is as follows: "C:\CFSN\ISS Projects\ProGenII.xls"

I have no problem getting the hyperlink into the email. The problem is that where there is a spaces in the string value, the
Click to expand...
hyperlink that ends up on the email has a links that only represent the the string upto the first space.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pasting a cell into bookmarked table from excel vba 1
EX2007 - Error 1004: method 'VBProject' of object '_Workbook' failed 15
Office 365 Outlook adding unwanted HTML to Email Body 0
Range.Find problem 3
Making Changes in a Group of Workbooks 2
Problem with hyperlink macro 0
Get data reference to last row 5
Consolidate Workbooks with Single header Row 7

Top