I have an excel sheet with a macro button to send a welcome email to staff. When run, the maco takes data filled in the spreadsheet (name, email address, line manager, position etc) and opens an email to be sent to the user. Part of the email body is a hyperlink. I cannot change this hyperlink as it is another department but it has white spaces in the link. I cannot seem to get the link into the email body as a fully formed link - the white spaces break the link so it is malformed and does not link to the page.
Does this make sense? I have tried chr(34) & ... and I have tried %20% and also tried """" but I cannot get these to work.
The link is
The link appears in the email body as
(breaks at the first white space).
This is the cheap & cheerful & butchered code;
Dim FirstName As String, Subj As String, LineManager As String
Dim GPAccess As String, POlevel As String, Email As String
Dim Msg As String, URL As String
Dim myHelpLink As String
Dim r As Integer, x As Double
' Get the email address
Email = Sheets(1).Range("C14")
FirstName = Sheets(1).Range("C5")
LineManager = Sheets(1).Range("C10")
GPAccess = Sheets(1).Range("C37")
POlevel = Sheets(1).Range("C38")
' Message subject
Subj = "Welcome - ICT Information "
' Compose the message
Msg = ""
Msg = Msg & "Dear " & FirstName & "," & vbCrLf & vbCrLf
Msg = Msg & "Hello and welcome to the team!" & vbCrLf & vbCrLf
Msg = Msg & "We have put some important information about our IT systems on the Intranet for you" & "." & vbCrLf & vbCrLf
'Msg = Msg & "" & "http://intranet/People/CorporateServices/IT/New to Us/Home.aspx" & "" & vbCrLf & vbCrLf
Msg = Msg & "Here you will find important information about passwords (which password for which system), working from home and backing up your data correctly. For example, it is your responsibility to back up files to the server/Intranet, and we will look after them from there. " & vbCrLf & vbCrLf
Msg = Msg & "It will only take about 10 minutes to read all the pages on the link above, and it will clear up some of the questions you have." & vbCrLf & vbCrLf
Msg = Msg & "Just email us if there is anything that we can help you with (related to ICT or Facilities) after checking the links above." & vbCrLf & vbCrLf
Msg = Msg & "We hope that this is the start of a long and positive time working together. " & vbCrLf & vbCrLf
'Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "Kind Regards" & vbCrLf
Msg = Msg & "The IT and Facilities Team"
' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s"
I know this code is very basic, but any guidance appreciated.