Sending an e-mail

[samndi67]

Good Evening All,

Earlier today I posted a question about a hyperlink and got the perfect answer. Now I need 1 more piece of advice. I am sending an e-mail from Excel that includes a link to a survey on my webserver. The link is very long so I shortened it to say 'click here'...that part works like a charm. The issue I am having is when I send the e-mail the shortened link shows up as text, not a url. If I send the long url it works great. Any ideas about how to make my shortened link (click here) show up as a link and not text?

Thanks,
Sam

 

[samndi67]

Anyone have any ideas on this?

Thanks in advance for your help.

Sam

 

[samndi67]

Maybe I should include the code that I'm using. Here is the code that sends the e-mail:

Sub SendEmail_Click()
'Create variables
Dim OutApp As Object
Dim OutMail As Object
Dim AW As Worksheet
Dim i As Integer
Dim Greeting As String
Set AW = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Do
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
'Create email
Greeting = "Please respond to our survey"
With OutMail
.To = AW.Range("C" & i).Value
.Subject = AW.Range("D" & i).Value
.Body = Greeting & vbNewLine & _
AW.Range("H" & i).Value & vbNewLine
 
'Send the e-mail
.Send
End With
i = i + 1
Loop Until IsEmpty(Cells(i, 1))
On Error GoTo 0
Set OutMail = Nothing
AW.AutoFilterMode = False
cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
 
Sub AddHyperlinkFormula()
Dim Part1 As String
Dim Part2 As String
Dim FriendlyName As String
Part1 = Range("g4").Value
'Part2 = Range("h2").Value
FriendlyName = "CLICK HERE TO TAKE SURVEY"
Range("h4").Activate
ActiveCell.Formula = "=Hyperlink(""" & Part1 & """, """ & FriendlyName & """)"
End Sub

When I use the value in cell h4 I get text only - no hyperlink in the e-mail, if I use cell g4 (which contains the link) I get the link.

Any idea how I can get the link in cell h4 to show in the e-mail?

Thanks,
Sam

 

[Ruddles]

Try this:-

.HTMLBody = Greeting & vbNewLine & _
    "< a href=""" & AW.Range("H" & i).Value & """>Click here< /a>" & _
    vbNewLine

(Not tested here.)

You'll need to remove the spaces I inserted after the < characters: they're only there to stop the forum software getting its knickers in a twist.

 

[samndi67]

Awesome!!!! You guys rock!