Whenever Excel sees a '#' sign in a hyperlink, it tries to interpret it as sort of a relative reference or a 'magic hash'. See this: http://www.myonlinetraininghub.com/excel-factor-18-dynamic-hyperlinks-and-the-magic
But this is bad if your hyperlink has a '#' sign in it without you intending to use this fancy Excel functionality. What can you do?
It looks like Excel interprets the '#' as a 'magic #' whenever Excel creates hyperlinks. But if you create the hyperlink within Excel's object model, or modify the address of an existing hyperlink in Excel's object model, you can foist an un-magical # into Excel, or MS Word, or any other MS Office application that would normally have a problem.
I made some Excel code that does this automatically for a sheet containing column A full of pound-sign-containing addresses (the text only), which fills column B with new working hyperlinks. I was able to paste these hyperlinks successfully into Word and Outlook.
Sub Make_Column_A_into_hyperlinks_hashmarkworkaround()
'Column "A" contains the text of links to some files.
'This subroutine will turn those cells into Excel hyperlinks...
'...and then correct the hyperlinks in case Excel misinterpreted
'a # mark which had originally occurred in the filename.
'Because I'm using selection.End(xlDown).Select to find the complete list,
'this program does not tolerate any blanks in the "A" column list.
'Note that I don't add a hyperlink in Row 1.
Dim A As Object
Dim lngRow As Long
Dim Height As Long
With ActiveSheet
.Cells(1, 1).Select
Selection.End(xlDown).Select
Height = Selection.Row
For lngRow = 2 To Height
.Hyperlinks.Add Anchor:=ActiveSheet.Cells(lngRow, 2), Address:=Chr(34) & _
ActiveSheet.Cells(lngRow, 1).Value & Chr(34), TextToDisplay:="Open", _
ScreenTip:=Chr(34) & ActiveSheet.Cells(lngRow, 1).Value & Chr(34)
Next
For Each A In ActiveSheet.Hyperlinks
If A.ScreenTip <> "" Then
If InStr(1, A.ScreenTip, "#") <> 0 Then
A.Address = Mid(A.ScreenTip, 2, Len(A.ScreenTip) - 2)
End If
End If
Next
End With
End Sub
