using a variable inside of a hyperlink for email body

[treygon]

I know this is an old post that's been revived, but how would I apply this concept to using a variable inside of a hyperlink I am trying to add to the body of an email I am sending with Excel.

Basically, I want to insert a hyperlink to a pdf that is on our server into the body of an email, using a variable as part of the file name, and have the link show as "Click here" (rather than the whole file name).

Here is my code:

Dim myOutlook As Object
Dim myMailItem As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)

filename = myreport.pdf

With otlNewMail
.To = emailRecipient
.Subject = "new trade ticket for approval"
.htmlBody = "To view this file, please" & "<# href=""file://\\292.568.8.1\willow4\Documents\reports\quarter\" & filename"">Click here"
.Display

(I left out the A tags to keep the post from turning it into an actual link)

VB gives me a error that looks like this: "Compile Error - Expected: End of Statement"
Hopefully that makes sense. I have been trying to find a solutions for days! Please help!
Thanks.

 

[treygon]

I'm not sure but I think the nearest you could get would be:-

[FONT=Fixedsys].Body = "Please click " _ [/FONT]
[FONT=Fixedsys]      & Replace("[/FONT][URL="file://C:%5CUsers%5CTrey"][FONT=Fixedsys]file://C:\Users\Trey[/FONT][/URL][FONT=Fixedsys] Oler\Desktop\Delete\deletethis.pdf", Space(1), "%20") _[/FONT]
[FONT=Fixedsys]      & " to review trades."[/FONT]

The Replace is there to convert spaces into HTML space characters to stop the back end of the link being detached (as it appears in the post).

I appreciate your help on this project. The code works with a variable, too!

One last question...is there any way to get the link to show as "click here" rather than displaying the whole link string?

Thanks again.

 

[Ruddles]

Not as far as I can see - not in a task item. I can't seem to get the body of the task to accept HTML.

Perhaps, since we've strayed from the original topic of mail items onto the topic of task items, you could ask the question in a new thread. i think that would be okay, as the subject is now a slightly different one.