Convert String To Hyperlink

[DMT Dave]

Hi Guy's is there a method to convert a string to hyperlink ? the aim is to use a string (address) but on the mail body is just says click here

Something like:

Dim strAddress as String


strAddress = "http://www.etc

Dim MailLink as Hyperlink

MailLink = "Click Here"

Now Click Here Needs to be the strAddress

Hope this makes sense

[ranman256]

const Q = """"
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = GetApplication("Outlook.Application")  'it may be open already so use this
Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = "wiley@acme.com"
    .Subject = "my Subject"
    
    .HTMLBody = "<a href=" & Q & "https://www.acme.com/?/r/mylink" & Q & ">Click here</a>"

    .send
end with

[DMT Dave]

Hi ranman, thank you, all works apart from line separations, i will snip the 2 outcomes of my current version and your version, which is great but i can't get it to read with the correct line feeds etc..

I may well be trying to mix

.htmlbody & vbnewline & vbnewline & _
.mailbody

[Minty]

PMFJI but yes if it's HTML you will need to add <br> or <p> instead of the vb equivalents.

[DMT Dave]

Hi ranman code adjusted but can't get the line feeds to work correctly! can i make or you help me make your Click Here version look like mine with the line feeds etc ??

my code including adjustment

Const Q = """"Dim CurrDay As Date, myDay As String, DayNo As Long, OrgDate As Date, DateChange As String, MailBody As String, TimingBody As String, TimeSlot As String, myUser As String, MailBody3 As String
Dim myClient As String, FullName() As String, dte As Date, TOD As String, FriDate As Date, ConDate As Date, myGreet As String, myETA1 As String, myETA2 As String, myEmail As String, MailBody2 As String
Dim rs As DAO.Recordset
Dim MyItem As Outlook.MailItem
Dim OutAccount As Outlook.Account
Dim myApp As New Outlook.Application
Dim BookingLink As String, RecNo As Integer


RecNo = Me.txtRef
myUser = Me.txtLogin
CurrDay = DateAdd("d", -(Weekday(Me.txtDateSource) - DayNo), Me.txtDateSource)
myDay = Format(CurrDay, "dddd-dd-mmm-yyyy")
DateChange = Format(CurrDay, "dd-mm-yyyy")
FriDate = DateAdd("d", -(Weekday(Me.txtDateSource) - 6), Me.txtDateSource)


FullName = Split(Me.txtName, " ")
Select Case UBound(FullName)
Case 0
myClient = FullName(0)
Case 1
myClient = FullName(0)
Case 2
myClient = FullName(0) & " " & FullName(2)
End Select


dte = Format(Now(), "hh:nn")
Select Case TOD
Case Is < TimeValue("12:00")
TOD = "Good morning"
Case Is < TimeValue("17:00")
TOD = "Good afternoon"
Case Else
TOD = "Good evening"
End Select


myGreet = TOD & " " & myClient & ","


BookingLink = "https://www.ourwebsiteremoved.com"




myETA1 = Me.txtETA
myETA2 = Me.txtETA2


TimeSlot = "We have now allocated a buffer time slot to remove your Item Removed Here." & vbNewLine & vbNewLine & _
"Here is the allocation we are offering:" & vbNewLine & vbNewLine & _
"......................................................................................................................." & vbNewLine & _
vbTab & "Removal Date: " & Me.txtDate & vbNewLine & vbNewLine & _
vbTab & "Expected between: " & myETA1 & " and " & Replace(myETA2, ":00", "") & " " & "(Subject To Your Approval)" & vbNewLine & vbNewLine & _
vbTab & "You can view your times online which will be updated on " & Format(Me.txtFriConDate, "dddd-dd-mmm-yyyy") & " " & "mid afternoon onwards" & vbNewLine & vbNewLine & _
"......................................................................................................................." & vbNewLine & _
Chr(149) & " " & "IMPORTANT NOTE" & " " & Chr(149) & vbNewLine & vbNewLine & _
"Due to a very congested phone line, to prevent us missing you, your timings are now offered online also." & vbNewLine & vbNewLine & _
"Due to our privacy policy, there are no names and address on our website that corresponds with your booking, just your unique booking number."


MailBody2 = "Please follow this link "
MailBody3 = "your 4 digit booking number " & "( " & RecNo & " ) will be listed" & vbNewLine & vbNewLine & _
"If you can accommodate the timings offered, please type your reference number " & "(" & RecNo & ")" & " " & "Click 'Confirm Time Button'" & vbNewLine & vbNewLine & _
"We much appreciate this as it helps us whilst our telephone lines are very busy." & vbNewLine & vbNewLine & _
"We thank you for your understanding and we look forward to assisting you" & vbNewLine & vbNewLine & _
"With our kindest regards" & vbNewLine & vbNewLine & _
myUser


MailBody = myGreet & vbNewLine & vbNewLine & _
TimeSlot




Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RecordNo = " & RecNo)
With rs
.Edit
!Date = Me.txtDateAdd
!ETA = Me.txtETA
!ETA2 = Me.txtETA2
.Update
.Close
End With
Set rs = Nothing
Me.lstDates.Requery


If Me.cboList = "Email All" Then
MsgBox ("System Updated Ready For Emailing " & myClient), vbInformation + vbOKOnly, "TIMING UPDATED"
End If


If Me.cboList <> "Email All" Then
If Me.txtEmailAddress = "" Then
MsgBox ("There is No Email Address For: " & myClient), vbOKOnly, "NO EMAIL ADDRESS"
myEmail = ""
Else
myEmail = Me.txtEmailAddress
Set MyItem = myApp.CreateItem(olMailItem)
    With MyItem
     Set OutAccount = myApp.Session.Accounts.Item(2)
       .To = myEmail
       .HTMLBody = MailBody & vbNewLine & vbNewLine & _
       MailBody2 & "<a href=" & Q & BookingLink & Q & ">Click here</a>" & vbNewLine & vbNewLine & _
       MailBody3
        '.Body = MailBody
       .Subject = "Item Removal Date"
       .SendUsingAccount = OutAccount
       .Display
    End With
End If
End If

ranman version



my version with line feeds in

[Minty]

Hi ranman code adjusted but can't get the line feeds to work correctly! can i make or you help me make your Click Here version look like mine with the line feeds etc ??

Dave - did you see my post?

[DMT Dave]

Hi minty and raman so is the line something like this to get the line feeds in ??

.HTMLBody = MailBody & "<b>" & "<b>" & MailBody2 & "<a href=" & Q & BookingLink & Q & ">Click here</a>" & MailBody3 & "<b> & <b> & MailBody4"

[Minty]

Yes - that should do it.

[DMT Dave]

Hi minty, yes i did, thank you, when i tried the <b> as below, it puts in bold

.HTMLBody = MailBody & "<b>" & "<b>" & MailBody2 & "<a href=" & Q & BookingLink & Q & ">Click here</a>" & MailBody3 & "<b> & <b> & MailBody4"

[DMT Dave]

Hi minty, doing some testing now as i didn't read your suggestion correctly, i have used <b> instead of <br> !!!

Thank you, will test

[Minty]

Hi minty, doing some testing now as i didn't read your suggestion correctly, i have used <b> instead of <br> !!!

Thank you, will test

I didn't read your first reply correctly either - we're both hopeless

[DMT Dave]

yippee thank you guy's you are all great, got it all looking good, last question on this, where it says click here, can i convert that to click direct to the BookingLink URL

Whereas, you have to use > Press and hold Ctrl and click, can i rule out pressing and holding Ctrl ?

Kindest

[Minty]

I'm sure that is an Outlook setting to prevent accidental opening of dodgy links, nothing much you can do to control it.

I think Once, A long Time ago in a Galaxy far far away, I made a button appear in an Outlook email which opened a web page somewhere...

[Welshgasman]

Must be a setting within Outlook as I can open links without any Ctrl key?

I do only have 2007 though?