Using a variable in HTML Link

[Kenicky]

Hello geniuses. I am trying to use a variable in an HTML Link in the body of an email. Clicking the link just try's to open a webpage by that name and doesn't grab the variable's value. This sheet is a template that will be saved many times in many locations so I won't be able to set the specific link in the HTML code itself. Any thoughts? The Red is obviously the problem. I just don't know the solution. Curly brackets were put in to keep the code from compiling.

Sub getpath()
    
    Dim olApp As Outlook.Application, olEmail As Outlook.MailItem
    Dim wbSummary As String
    
    Set olApp = New Outlook.Application
    Set olEmail = olApp.CreateItem(olMailItem)
    wbSummary = ThisWorkbook.FullName
       
    With olEmail
            .BodyFormat = olFormatHTML
            .Display
            .Subject = "Click This Link for Update"
            .HTMLBody = "Click {a href=[B][COLOR=#ff0000]wbSummary[/COLOR][/B]>here{/a} to go to: " & wbSummary<a_href=<font color="#ff0000"><a_ href="wbSummary">
    End With
End Sub

Many thanks in advance for any help.

Ken</a_></a_href=

 

[Gary McMaster]

Hi Ken,

Welcome aboard!

You can't use the variable inside the quotes as it is taken as a literal. You must concatenate the string / variable something like this:

.HTMLBody = "Click {a href=" & wbSummary & ">here{/a} to go to: " & wbSummary

Am I correct in assuming you deliberately substituted the braces for the correct HTML tags to get it posted correctly on this forum?

 

[Kenicky]

Hello Gary,

Thank you for that. It didn't occur to me that the HTML string could be broken up like that. That did work to get the HTML tag to look at the value of the variable. I do have one more problem though. The folder names were made before I arrived and there are spaces in the names of the folders. Example \\Engineering\All Share\Eng Dept\. When the HTML starts to look at the value, it stops at the first space. So in this example, it is looking for "\\Engineering\All". It doesn't continue on with the rest of the path. Is there a way to get it to include the entire thing including spaces?

And yes, you are right about the assumption on the curly brackets to get the code to post properly.

Ken

 

[Gary McMaster]

I remember there was some special code that was needed to get HTML to recognize spaces. I believe it was something like %%20.

You could use the VBA replace function to replace the spaces in the filename like this (assuming %%20 is the correct code):

wbSummary = ThisWorkbook.FullName
wbSummary = Replace(wbSummary, " ", "%%20")

 

[Gary McMaster]

This site says it is a single percent symbol %20, not %%20 as I posted

URL Encoding

 

[Kenicky]

You're the best Gary. A quick Google search shows that the correct code is "&hashtag32;". Replace hashtag with #. Did the replace and it works great. Thank you for that. I never would have figured it out without your help.

Ken

I used the HTML encoding for the space since I will most likely leave the file name in the email and I want it to show up properly there too.

 

[Gary McMaster]

You're welcome.

I've seen a number of file renaming procedures on this site. Maybe renaming all your files to use underscores instead of spaces would be another solution to look at.