Excel 2010: How to output a hyperlink from macro to email message


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
http://intranet/People/CorporateServices/IT/New to Us/Home.aspx

The link appears in the email body as
(breaks at the first white space).

This is the cheap & cheerful & butchered code;

Sub EmailWelcome()
    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"
End Sub

Open in new window

I know this code is very basic, but any guidance appreciated.
Who is Participating?
TazDevil1674Connect With a Mentor Commented:
You can do that...check this link - its where i got most of the source to do it myself

I use .HTMLbody instead of .body

    .HTMLBody = "<HTML>" & _
        "<p style='font-family:calibri;font-size:14'>" & _
        "Duty Manager Rota for this week is outlined below.<BR><BR>" & _
        "<B><a href=chr(34)mailto:email@domain.com?Subject=Email via DM Rotachr(34)>NT Team</a></B> Mailbox - " & _
        "The DM is the first point of contact for any issues; e-mail should be sent to the " & _
        "<B><a href=chr(34)mailto:email@domain.com?Subject=Email via DM Rotachr(34)>NT Team</a></B> (email@domain.com)" & _
        "</p><BR>" & _
        RangetoHTML(Rng) & _

Open in new window

I am also adding some cells converted to HTML to this...
concern_supportAuthor Commented:
How do I specify the .body as .HTMLbody when I don't use .body?

I am using

Dim Msg as String, URL as String

Msg = Msg & "....."
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 

Open in new window

Do I need to rewrite the complete approach to the macro? (I want the email to open on screen before sending, that the user has to manually click send on it).

concern_supportAuthor Commented:
Ok, thanks, looking at it now.
concern_supportAuthor Commented:
I had to re-write the complete macro, using the URL samples submitted
All Courses

From novice to tech pro — start learning today.