using a variable inside of a hyperlink for email body

treygon · Mar 12, 2011

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:

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.

VoG · Mar 12, 2011

Not sure about the HTML but it should be

Rich (BB code):

Filename = "myreport.pdf"

Ruddles · Mar 12, 2011

And then:-

Code:

.htmlBody = "To view this file, please" & "< a href=""file://\\292.568.8.1\willow4\Documents\reports\quarter\" & filename [B][COLOR=red]& "[/COLOR][/B]"">Click here"< /a>

Scroll --> thataway -->

treygon · Mar 12, 2011

Thanks! It worked!

I would have never guessed to put the " & " " after the variable. Makes no sense to me. But, I'm not a vba expert either.

Ruddles · Mar 12, 2011

Did you make a slight change to the code I posted to make it work?

Here's an explanation:-

First part of string: "To view this file, please"
&
first part of hyperlink: "< a href="file://\\292.568.8.1\willow4\Documents\reports\quarter\"
&
the variable containing the filename: filename
& """>Click here< /a>"

where the first " starts the contents of the string, the "" generates a single quote inside the string variable (because a single quote would signal the end of the string variable to VBA) and the last " ends the string.

I hate explaining how to concatenate strings when they need to contain embedded quotes!

treygon · Mar 12, 2011

Ruddles said:

And then:-

Code:

.htmlBody = "To view this file, please" & "< a href=""file://\\292.568.8.1\willow4\Documents\reports\quarter\" & filename [B][COLOR=red]& "[/COLOR][/B]"">Click here"< /a>

Scroll --> thataway -->

I used this for the email I am sending out. Now I am trying to create the same link for a task I am creating, too. I tried using the same code, but I guess the task object doesn't accept a HTML body.

Here is what I have:

Code:

Set olApp = CreateObject("Outlook.Application")
Set olTask = olApp.CreateItem(olTaskItem)

filename = "myreport.pdf"

With olTask
.Subject = "report for review"
[COLOR=Red].Body =  "To view this file, please" & "< a href=""file://\\292.568.8.1\willow4\Documents\reports\quarter\" & filename [/COLOR][COLOR=Red]& """>Click here"< /a> [/COLOR]
.StartDate = Date
.DueDate = DateValue(EndDate)
.Status = olTaskInProgress
.Importance = olImportanceHigh
.ReminderPlaySound = True
.Companies = "WRCA"
.Owner = "Trey"
.Save
.Display ' delete this when launching final
.Recipients.Add ("lisa@mycompany.com")
.Assign
.Send
End With

This just inserts the words of the hyperlink without a link.

Thanks for your help!

Ruddles · Mar 13, 2011

Change this:-

Code:

[B]&[/B] [B]""">[/B][B]Click here[COLOR=red]"[/COLOR]< /a>[/B]

to:-

Code:

[B]&[/B] [B]""">[/B][B]Click here< /a>[COLOR=red]"[/COLOR][/B]

and remove the space after the < characters - they're only there to stop the board software getting its knickers in a knot.

treygon · Mar 13, 2011

Ruddles said:
Change this:-

Code:

[B]&[/B] [B]""">[/B][B]Click here[COLOR=red]"[/COLOR]< /a>[/B]

to:-

Code:

[B]&[/B] [B]""">[/B][B]Click here< /a>[COLOR=red]"[/COLOR][/B]

and remove the space after the < characters - they're only there to stop the board software getting its knickers in a knot.

This is actually how I had it. For some reason, though, it doesn't produce a link. It is just a text string that looks like this in the task body : 
Please < a href="C:\Users\Trey Oler\Desktop\Delete\deletethis.pdf">click here< /a> to review trades.
to review report.

Ruddles · Mar 13, 2011

Ruddles · Mar 13, 2011

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

Code:

[FONT=Fixedsys].Body = "Please click " _ [/FONT]
[FONT=Fixedsys]      & Replace("[/FONT][URL="file://C:\Users\Trey"][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).

using a variable inside of a hyperlink for email body

treygon

New Member

Excel Facts

VoG

Legend

Ruddles

Well-known Member

treygon

New Member

Ruddles

Well-known Member

treygon

New Member

Ruddles

Well-known Member

treygon

New Member

Ruddles

Well-known Member

Ruddles

Well-known Member

Similar threads

Forum statistics

Share this page

using a variable inside of a hyperlink for email body

New Member

Excel Facts

Legend

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock