Hyperlink in Email Body

D

Darrell Lankford

I have a spreadsheet that names and saves the file in a folder and the
below code creates an email to let an admin know that the file is
ready to be entered in a separate data base. Everything is working
except the hyperlink in the email. It puts the text path and name of
the file in the body of the email, but as text and not as a hyperlink.
If anyone can help with how I can turn the text into a hyperlink, it
would be greatly appreciated.


Sub email_Alert_Input_Rdy()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)


msg = "All," & Chr(13) & Chr(13)
msg = msg & "The Estimate is ready to be entered." & Chr(13)
msg = msg & "If you have any questions, let me know." & Chr(13) & Chr
(13)
msg = msg & "Thanks," & Chr(13) & Chr(13) & Chr(13)

‘THE BELOW LINE SHOULD BE THE HYPERLINK…
msg = msg & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & Chr(13)


addee = "(e-mail address removed)"

With objMail
.To = addee
.CC = CC
.Subject = “Estimate Ready”
.Body = msg
.Display
'.Send

'ActiveWorkbook.Close


End With
Set objMail = Nothing
Set objOL = Nothing




End Sub
 
R

Ron de Bruin

hi Darrell

.Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
.Body = "file://Yourcomputer/YourFolder/Week%202.xls"

So try to add file://

Ot create a html body

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



I have a spreadsheet that names and saves the file in a folder and the
below code creates an email to let an admin know that the file is
ready to be entered in a separate data base. Everything is working
except the hyperlink in the email. It puts the text path and name of
the file in the body of the email, but as text and not as a hyperlink.
If anyone can help with how I can turn the text into a hyperlink, it
would be greatly appreciated.


Sub email_Alert_Input_Rdy()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)


msg = "All," & Chr(13) & Chr(13)
msg = msg & "The Estimate is ready to be entered." & Chr(13)
msg = msg & "If you have any questions, let me know." & Chr(13) & Chr
(13)
msg = msg & "Thanks," & Chr(13) & Chr(13) & Chr(13)

‘THE BELOW LINE SHOULD BE THE HYPERLINK…
msg = msg & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & Chr(13)


addee = "(e-mail address removed)"

With objMail
.To = addee
.CC = CC
.Subject = “Estimate Ready”
.Body = msg
.Display
'.Send

'ActiveWorkbook.Close


End With
Set objMail = Nothing
Set objOL = Nothing




End Sub
 
D

Darrell Lankford

hi Darrell

 .Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
    .Body = "file://Yourcomputer/YourFolder/Week%202.xls"

So try to add file://

Ot create a html body

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



I have a spreadsheet that names and saves the file in a folder and the
below code creates anemailto let an admin know that the file is
ready to be entered in a separate data base. Everything is working
except thehyperlinkin theemail. It puts the text path and name of
the file in the body of theemail, but as text and not as ahyperlink.
If anyone can help with how I can turn the text into ahyperlink, it
would be greatly appreciated.

Sub email_Alert_Input_Rdy()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

msg = "All," & Chr(13) & Chr(13)
msg = msg & "The Estimate is ready to be entered." & Chr(13)
msg = msg & "If you have any questions, let me know." & Chr(13) & Chr
(13)
msg = msg & "Thanks," & Chr(13) & Chr(13) & Chr(13)

‘THE BELOW LINE SHOULD BE THEHYPERLINK…
msg = msg & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & Chr(13)

addee = "(e-mail address removed)"

With objMail
    .To = addee
    .CC = CC
    .Subject = “Estimate Ready”
    .Body = msg
    .Display
    '.Send

'ActiveWorkbook.Close

End With
Set objMail = Nothing
Set objOL = Nothing

End Sub
Click to expand...


I tried changing the .body line several different ways, and I get the
link in the body of the email, but still as text only.

I tried putting the hyperlink on the excel sheet in the range that
puts the email in the HTML format, and the link goes with it, looks
like a hyperlink, but I get a box titled "Locate Link Browser". Not
sure what to try next.
 
R

Ron de Bruin

strbody = "<H3><B>Dear Customer</B></H3>" & _
"Please visit this website to download the new version.<br>" & _
"Let me know if you have problems.<br>" & _
"<A HREF=""http://www.rondebruin.nl/"">Ron's Excel Page</A>" & _
"<br><br><B>Thank you</B>"

Use .HTMLBody = strbody then instead of .Body = strbody



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


hi Darrell

.Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
.Body = "file://Yourcomputer/YourFolder/Week%202.xls"

So try to add file://

Ot create a html body

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



I have a spreadsheet that names and saves the file in a folder and the
below code creates anemailto let an admin know that the file is
ready to be entered in a separate data base. Everything is working
except thehyperlinkin theemail. It puts the text path and name of
the file in the body of theemail, but as text and not as ahyperlink.
If anyone can help with how I can turn the text into ahyperlink, it
would be greatly appreciated.

Sub email_Alert_Input_Rdy()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

msg = "All," & Chr(13) & Chr(13)
msg = msg & "The Estimate is ready to be entered." & Chr(13)
msg = msg & "If you have any questions, let me know." & Chr(13) & Chr
(13)
msg = msg & "Thanks," & Chr(13) & Chr(13) & Chr(13)

‘THE BELOW LINE SHOULD BE THEHYPERLINK…
msg = msg & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & Chr(13)

addee = "(e-mail address removed)"

With objMail
.To = addee
.CC = CC
.Subject = “Estimate Ready”
.Body = msg
.Display
'.Send

'ActiveWorkbook.Close

End With
Set objMail = Nothing
Set objOL = Nothing

End Sub
Click to expand...


I tried changing the .body line several different ways, and I get the
link in the body of the email, but still as text only.

I tried putting the hyperlink on the excel sheet in the range that
puts the email in the HTML format, and the link goes with it, looks
like a hyperlink, but I get a box titled "Locate Link Browser". Not
sure what to try next.
 
D

Darrell Lankford

strbody = "<H3><B>Dear Customer</B></H3>" & _
              "Please visit this website to download the new version.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<A HREF=""http://www.rondebruin.nl/"">Ron's Excel Page</A>" & _
              "<br><br><B>Thank you</B>"

Use .HTMLBody = strbody then instead of .Body = strbody

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
















I tried changing the .body line several different ways, and I get the
link in the body of theemail, but still as text only.

I tried putting thehyperlinkon the excel sheet in the range that
puts theemailin the HTML format, and the link goes with it, looks
like ahyperlink, but I get a box titled "Locate Link Browser". Not
sure what to try next.- Hide quoted text -

- Show quoted text -
Click to expand...

Ron,

I really appreciate the help on this and I almost have it. It works
fine, but I have a changing link to the file I need to put into where
you have
the HREF "http://www.rondebruin.nl/". I'm trying to put the
following:

Link = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

If I put the actual link "K:\Pricing\Estimates\Item 123-11-001.xls",
it works fine, but I'm not sure how to get the changing link in?
 
R

Ron de Bruin

Try this

strbody = "<H3><B>Dear Customer</B></H3>" & _
"Please visit this website to download the new version.<br>" & _
"Let me know if you have problems.<br>" & _
"<A HREF=" & ThisWorkbook.FullName & ">Ron's Excel File</A>" & _
"<br><br><B>Thank you</B>"

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


strbody = "<H3><B>Dear Customer</B></H3>" & _
"Please visit this website to download the new version.<br>" & _
"Let me know if you have problems.<br>" & _
"<A HREF=""http://www.rondebruin.nl/"">Ron's Excel Page</A>" & _
"<br><br><B>Thank you</B>"

Use .HTMLBody = strbody then instead of .Body = strbody

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
















I tried changing the .body line several different ways, and I get the
link in the body of theemail, but still as text only.

I tried putting thehyperlinkon the excel sheet in the range that
puts theemailin the HTML format, and the link goes with it, looks
like ahyperlink, but I get a box titled "Locate Link Browser". Not
sure what to try next.- Hide quoted text -

- Show quoted text -
Click to expand...

Ron,

I really appreciate the help on this and I almost have it. It works
fine, but I have a changing link to the file I need to put into where
you have
the HREF "http://www.rondebruin.nl/". I'm trying to put the
following:

Link = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

If I put the actual link "K:\Pricing\Estimates\Item 123-11-001.xls",
it works fine, but I'm not sure how to get the changing link in?
 
D

Darrell Lankford

Try this

strbody = "<H3><B>Dear Customer</B></H3>" & _
              "Please visit this website to download the new version.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<A HREF=" & ThisWorkbook.FullName & ">Ron's Excel File</A>" & _
              "<br><br><B>Thank you</B>"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm










Ron,

I really appreciate the help on this and I almost have it. It works
fine, but I have a changing link to the file I need to put into where
you have
 the HREF "http://www.rondebruin.nl/". I'm trying to put the
following:

Link = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

If I put the actual link "K:\Pricing\Estimates\Item 123-11-001.xls",
it works fine, but I'm not sure how to get the changing link in?- Hide quoted text -

- Show quoted text -
Click to expand...

I tried several times & couldn't get the hyperlink to work. It was
cutting off the end of the hyperlink at the spcaes. I went back to
your HTML function I got from you a year or so ago...
Public Function RangetoHTML(sh As Worksheet, rng As Range)

I put the following in the code after I named a cell "Path"...
Range("Path").Value = ThisWorkbook.FullName

In the range that goes into the email, I put a line with a formula...
=HYPERLINK(Path)

This puts the hyperlink in the email with the other text in the range.

Ron, thanks for taking time to help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

emailing from Excel 2
Email Body Text & Signature 1
Sending a mail via outlook excel 2003 0
Outlook email url in body 2
Sending e-mails from specific email address 2
Outlook - Object Model Guardian 7
Get info from first cell in row. 5
How to Hyperlink in an email? 2

Top