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?
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?
Last edited:
