Dynamic Hyperlink in Email

[High Plains Grifter]

Hello people,

I am having a bit of trouble inserting a dynamic hyperlink into an Email that will be generated from Excel, linking to a newly saved document. there is a macro, which seems to work fine, calling a function that seems to hold the error. here is the macro: (Please note that the forum interprets my html so it displays a link where there should be text to make a link...)

Option Explicit

Sub NewSmokeTest()
Dim boolSend As Boolean
Dim strText As String
Dim strEmail As String
Dim strDate As String
Dim strUserName As String
Dim strUserInitials As String
Dim strVersion As String
Dim strPath As String
Dim strLink As String
strPath = "P:\GoTrex\Test plans\"
strDate = WorksheetFunction.Text(Date, "YYMMdd")
strUserInitials = Range("Initials").Value
strUserName = Range("UserName").Value
'strText = ""
If Range("NewVersion").Value = True Then
        strVersion = InputBox("Enter the full code of the new version to be tested", "New Release")
Else
        strVersion = Range("CurrentVersion")
End If

Dim wbHidden As Workbook
Dim appExcel As New Excel.Application
appExcel.Visible = False
Set wbHidden = appExcel.Workbooks.Open(strPath & "Smoke Test Template.xlsm")

If Len(Dir(strPath & strVersion, vbDirectory)) = 0 Then
        'Folder not found - make a new one for this version
        MkDir (strPath & strVersion)
Else
        'Folder found - just carry on
End If

strLink = strPath & strVersion & "\" & strDate & Trim(strUserInitials) & ".xlsm"
wbHidden.SaveCopyAs Filename:=strLink

strEmail = Range("EmailAddress").Value
    'strText = InputBox("Add a line of explanatory text if you wish:", "Body Text")
    If SendEmail(strEmail, "Record Sheet for GoTrex Testing", strText, strUserName, strLink, Display, False) = 0 Then
            'nothing
    Else
            MsgBox ("Email Notification has failed")
    End If
End Sub

and this is the function - the problem comes (I think) in the line that starts with "strLink = "We have created a record sheet for you to complete...

Enum OptSendDisplay
    Send = 1
    Display = 2
End Enum
'THIS FUNCTION IS CALLED BY THE WORKBOOK SUB ON SAVE
Public Function SendEmail(strMailTo As String, strSubject As String, strText As String, strRecipientName As String, strLink As String, _
SendOrDisp As OptSendDisplay, boolReceipt As Boolean) As Long
'This will return 0 if successful else error number

    Dim ob***p As Object
    Dim objMail As Object
    'Dim strLink As String
    Dim strMyName As String
    Dim strGreeting As String
    strGreeting = "Hi " & strRecipientName & ","
    strMyName = Application.UserName
    
    MsgBox strLink
    
    strLink = "We have created a record sheet for you to complete while performing the tests on the new version of GoTrex. Please follow this link" + " to view it."
    
     DoEvent
    On Error GoTo ErrHandler
    'We are dealing with outlook here
    Set ob***p = CreateObject("Outlook.Application")
    'and a new email
    Set objMail = ob***p.CreateItem(0)
With objMail
        .to = strMailTo
        .Subject = strSubject
        .HTMLBody strGreeting + "
 
" + strLink + "
 
" + strText + "
 
" + strMyName
        .ReadReceiptRequested = boolReceipt
    If SendOrDisp = Display Then .Display
    If SendOrDisp = Send Then .Send
End With
 
    SendEmail = 0 'Success returned

ErrHandler:
    SendEmail = Err.Number 'Error returned
End Function

The strLink that appears in the MsgBox just before the addition of the link is:

"P:\GoTrex\Test plans\2.115.4475.39960\121025MJG.xlsm"

which is what I expect. the strLink just after the change also looks fine to my (probably wrong) eyes:
"We have created a record sheet for you to complete while performing the tests on the new version of GoTrex. Please follow this link to view it

When this link is put into the email, the address is changed to reference the shared server that contains the P drive, which is fine, but also loses the last part of the link - when I hover my mouse over the link, it displays this:

"file:///p:\gotrex\test"

This, of course, fails to open. what is it that Outlook doesn't like? I'm guessing it is the space, but I can't guarantee that the address won't contain spaces - what should I do?

Hmmm - The forum automatically interprets the html I have written... I do not know how to show my syntax in the important line - i have tried all formats and splitting the text up, but it tries to do whatever it can with it - if you need to see the actual VBA script, let me know and I can email it you as a .txt or something.

 

[High Plains Grifter]

I'm sorry and a fool. I should have realised the need to replace spaces in the link with "%20" . This is the solution.

As often happens, how ever much I look at something, it is only when I explain it to another that I see the solution. I think I need an inflatable doll next to me with a quizzical expression on its face!

Apologies to any whose time I have wasted.