VBA email code for outlook / referencing a hyperlink

[willow1985]

Hello,

I have this code in Excel that creates an e-mail in outlook based on cell references. One of the cell references (A8 on Sheet: Lists) is a link.
To be more precise it is the text of a hyperlink using the formula: GetURL.

Either way it is the correct address in cell A8, for example: C:\users\your name\downloads

Now when the macro creates the e-mail and references A8 the text is put in the e-mail but it does not recognize it as a link. Only part of the address has the hyperlink (C:\users\your ) and when you click on it of course it tells you that it cannot be found because it is stopping at the space between "your" and "name"..

Is there a way to fix this? Here is the code and picture of an example spreadsheet and e-mail result:

Sub Owner()
'
' Owner Macro
'
       Msg = "An e-mail will be generated and sent to the owner selected." & vbCrLf & "" & vbCrLf & "Do you wish to proceed?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes

    ActiveCell.Select
Range(Cells(Selection.Row, 10), Cells(Selection.Row, 10)).Select
Selection.Copy
    Sheets("Lists").Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Database").Select
Range(Cells(Selection.Row, 1), Cells(Selection.Row, 1)).Select
Selection.Copy
    Sheets("Lists").Select
    Range("A6").Select
    ActiveSheet.Paste
  Dim OutlookApp As Object, MItem As Object
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  With MItem
    .to = Sheets("Lists").Range("A5")
    .Subject = Sheets("Lists").Range("A6")
    .Body = Sheets("Lists").Range("A7") & vbCrLf & vbCrLf & Sheets("Lists").Range("A8") & vbCrLf & vbCrLf & vbCrLf & "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"
    .Display
    .Send
  End With
  Sheets("Database").Select
  Case vbNo
        GoTo Quit:
    End Select

Quit:

End Sub

Thank you!

Carla

 

[Trevor_S]

I've often seen %20 in URLs instead of spaces. So try amending the line that sets the email body to replace spaces with %20:

    .Body = Sheets("Lists").Range("A7") & vbCrLf & vbCrLf & Replace(Sheets("Lists").Range("A8"), " ", "%20") & vbCrLf & vbCrLf & vbCrLf & "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"

 

[willow1985]

I did not know about the %20, this is perfect! Thank you so much ?