Hyperlink Not Working in Outlook via Excel Macro

A

alexaronson

Active Member
Joined
Sep 30, 2005
Messages
314
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.
Code: 
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
Code: 
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10", _
    TextToDisplay:="Alert 10"
To
Code: 
.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
Code: 
.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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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.
Code: 
"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?
 
Upvote 0
Andrew,

thanks for the link.

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

Code: 
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
 
Upvote 0
Andrew,

This code produces a link of S:/COLLABORATIVE
Code: 
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
Code: 
strBody = strBody & "<"A HREF="<"S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10">"Alert 10 Link</A>"
 
Upvote 0
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

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
 
Upvote 0
Try (in the original code):

Code: 
strbody = strbody & "<A href='S:\COLLABORATIVE SHARING FOLDER\TeamA\Sales\Reports\Alert 10'>Alert 10 Link</A>"
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
478
Domenic
D
A
  • Solved
Sending hyperlink filepath via email
Replies
8
Views
826
AndyEd
A
T
  • Solved
VBA- Copy & Paste Range into Outlook as a Picture
Replies
6
Views
4K
jolivanes
jolivanes
J
  • Question Question
VBA code to create an email not importing names in "To, CC, or BCC"
Replies
3
Views
347
Alex Blakenburg
A
J
  • Question Question
VBA to send multiple emails with multiple attachments
Replies
5
Views
731
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top