Hyperlink Not Working in Outlook via Excel Macro

[alexaronson]

Hello and thank you for looking at my question.

I have a macro that creates several files and saves them. A task that I have to do every day is email out a hyperlink to the parties that need to review these files when the files are updated. I want to automate my message when the file is done.

So far I am able to create a new outlook message, enter a To, Subject, and Body text and send the email. However, I can't get the message to display the hyperlink.

Sub Test()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next

With OutMail
.To = "Test"
.CC = ""
.BCC = ""
.Subject = "Alert 10 for " & Date & " updated"
.Body = "Alert 10 for " & Date & " updated"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10", _
    TextToDisplay:="Alert 10"

.Display
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

when I try changing

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10", _
    TextToDisplay:="Alert 10"

To

.Body = ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10", _
    TextToDisplay:="Alert 10"

I get "Complie error: Expecdted: end of statement" at the word of Anchor.

When I Try changing the code to

.Body = "Alert 10 for " & Date & " updated"
.Body = "S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10"

The first .Body goes away and is replaced with the second .Body. Also the second .Body is not a hyperlink.

Any suggestions?

 

[alexaronson]

Andrew,

Can you comment the code line. It is coming up as Alert 10 and I can't see the code for it. The hyperlink works. It takes me to my directory path.

 

[Andrew Poulsom]

Oh I see. Let's try:

    strbody = strbody & "<A href='S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10'>Alert 10 Link</A>"

 

[CVDK]

Thanks Andrew for redirecting me. The ' ' did the trick and stopped the truncation. Everything now works in the macro, from saving a copy of the original file (or form) in a folder based on fields in the "form" over naming the copy according to some other information from the "form" to sending a mail with a link to that exact file (without attaching a file to the mail).

Best regards
Christian