Hyperlink breaks at space in folder name when using a variable that pulls from a cell value

L

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
I have created a spreadsheet that allows me to drop in a few bits of information and using a macro it sends out a form email through Outlook. I am using Office 2013. Within the document I have a cell that contains a formula that returns the file path of a hyperlink I want to include in the email. The only part of the Hyperlink that changes is the file name, which changes to match the current month. For instance, this month the file will be named "March CP.xls". I wanted the formula to create the link in the Excel file so that users not familiar with macros could see what was being created and possible correct it, rather than having the logic buried in the code. The macro will create the email with all text that I need and it even creates the hyperlink. The hyperlink displays the correct path, down to the file name, but the hyperlink itself breaks where there is a space in a folder name within the navigation. I know that if I surround my link with <> it is supposed to allow the spaces to work, but since I'm using a variable in that portion of the code, not the actual link text, it isn't working.

In the following code, H_Link.Value = \\accounting\investments\COMMERCIAL PAPER\MARCH 2016 CP.xls

The hyperlink displays the full address, but the link itself breaks after "COMMERCIAL".

How do I fix this portion of code so that the link doesn't break at the space?


Dim HLink As String
Dim strbody As String
HLink = Range("H_Link").Value
strbody = "<HTML><BODY>"
strbody = strbody & "" & Body_1 & AmtToPurch & Body_2 & Format(MedDate, "DDDD, m/yy") & "."
strbody = strbody & "<br><br>" _
& " <a href=" & HLink & " >" & HLink & " </A> " & "<br><br><br>" & Signature
strbody = strbody & "<BODY></HTML>"
Click to expand...
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Code didn't post correctly, trying again with HTML tags:

HTML: 
    strbody = "<HTML><BODY>"
    strbody = strbody & "" & Body_1 & AmtToPurch & Body_2 & Format(MedDate, "DDDD, m/yy") & "."
    strbody = strbody & "<br><br>" _
            & " <a href=" & HLink & "> " & HLink & " </A> " & "<br><br><br>" & Signature
    strbody = strbody & "<BODY></HTML>"
 
Upvote 0
Try using quotes to enclose href property value. For example I used single quotes here.

href='" & HLink & "' >

P.S. I see the issue you are facing in posting the html code here.
 
Last edited:
Upvote 0
Now why couldn't I find/think of that? Thanks for the help V_Malkoti. I found another fix, also on this forum that used the replace function to replace spaces with "%20", which also worked. That can be found at this link http://www.mrexcel.com/forum/excel-questions/535597-using-variable-inside-hyperlink-email-body.html

As it stands, the two fixes that have worked are, what V_Malkoti suggested:
HTML: 
& " <a href='" & HLink & "'> " & HLink & " </A> " & "<br><br><br>" & Signature

Or using the replace function:
HTML: 
& " <a href=" & Replace(HLink, Space(1), "%20") & "> " & HLink & " </A> " & "<br><br><br>" & Signature

V_Malkoti said:
Try using quotes to enclose href property value. For example I used single quotes here.

href='" & HLink & "' >

P.S. I see the issue you are facing in posting the html code here.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Adding a link in email macro using a cell value
Replies
3
Views
437
KOKOSEK
KOKOSEK
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
L
  • Question Question
How to fix VBA code to stop saving and emailing PDF of blank cell from data validation list?
Replies
2
Views
267
lharr28
L
G
  • Question Question
Email from Excel query
Replies
0
Views
376
gmazza76
G
T
  • Solved
HyperLinks to folders with the same name as excel cell value
Replies
5
Views
3K
timofey
T

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top