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?

 

[Andrew Poulsom]

Here is an example that you may be able to adapt:

http://www.mrexcel.com/forum/showpost.php?p=2435236&postcount=13

 

[alexaronson]

Andrew,

I was able to adapt the example you gave me into my code.

However, I am running into a problem.

The directory path that the hyperlink needs has some spaces.

"S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10"

So when I execute the macro, the hyperlink path is being cut off after Collaborative.

The collaborative sharing folder is well known in my organization and I do not have the authority to chagne the file name to overcome.

Any suggestions?

 

[Andrew Poulsom]

See if this helps:

http://office.microsoft.com/en-us/o...-spaces-to-an-e-mail-message-HA001127242.aspx

 

[alexaronson]

Andrew,

thanks for the link.

However, I have the chevrons in place, but Outlook still truncates the link after collaborative.

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

***Note, I added "" around the chevron so the code line would show up on MrExcel.com

 

[Andrew Poulsom]

I don't see a chevron befor the S:.

 

[alexaronson]

Andrew,

This code produces a link of S:/COLLABORATIVE

strBody = strBody & ""<"A HREF=S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10">"Alert 10 Link</A>"

When I add a chevon infront of the S, I get a link of <S:/COLLABORATIVE

strBody = strBody & "<"A HREF="<"S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10">"Alert 10 Link</A>"

 

[alexaronson]

.

 

[alexaronson]

I found this code which creates the email, adderesses it, adds subject, adds body, and adds hyperlink correctly.
However, I can't figure out how to auto send like the original code or how to merge this code with my existing code.

Private Sub CreateEmail()

Dim rcp, hlink, msg, subj
rcp = "person@email.com"
subj = "Please review this file"
msg = "Please review file located at: " & _
"%0A%0A" & "<file://" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ">"
hlink = "mailto:" & rcp & "?"
hlink = hlink & "subject=" & subj & "&"
hlink = hlink & "body=" & msg
ActiveWorkbook.FollowHyperlink (hlink)
End Sub

Private Sub CreateEmail()

Dim rcp, hlink, msg, subj
    rcp = "person@email.com"
    subj = "Please review this file"
    msg = "Please review file located at: " & _
    "%0A%0A" & "<file://" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ">" 
    hlink = "mailto:" & rcp & "?"
        hlink = hlink & "subject=" & subj & "&"
        hlink = hlink & "body=" & msg
        ActiveWorkbook.FollowHyperlink (hlink)
End Sub

 

[Andrew Poulsom]

Try (in the original code):

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