Hello,
I have the following code sending an email. I want the link following "Checklist location" in the body of the email to be a hyperlink, but right now it is pulling the link as text and cannot be clicked on. Can anyone suggest a way to change this code to create the hyperlink when the email is sent?
Thank you in advance for all help.
Laura
Code
Sub SendInitiationEmail()[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim noSession As Object, noDatabase As Object, noDocument As Object[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim obAttachment As Object, EmbedObject As Object[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim stSubject As Variant, stAttachment As String[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim vaRecipient As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim vaMsg As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim valink As Variant[/SIZE][/FONT] [FONT=sans-serif][size=10] Const EMBED_ATTACHMENT As Long = 1454[/SIZE][/FONT] [FONT=sans-serif][size=10] Const stTitle As String = "Status Active workbook"[/SIZE][/FONT] [FONT=sans-serif][size=10] Const stMsg As String = "The active workbook must first be saved " & vbCrLf _[/SIZE][/FONT] [FONT=sans-serif][size=10] & "before it can be sent as an attachment."[/SIZE][/FONT] [FONT=sans-serif][size=10] 'If the active workbook has not been saved at all.[/SIZE][/FONT] [FONT=sans-serif][size=10] If Len(ActiveWorkbook.Path) = 0 Then[/SIZE][/FONT] [FONT=sans-serif][size=10] MsgBox stMsg, vbInformation, stTitle[/SIZE][/FONT] [FONT=sans-serif][size=10] Exit Sub[/SIZE][/FONT] [FONT=sans-serif][size=10] End If[/SIZE][/FONT] [FONT=sans-serif][size=10] With Worksheets(1)[/SIZE][/FONT] [FONT=sans-serif][size=10] .Hyperlinks.Add .Range("CC1"), ActiveWorkbook.FullName[/SIZE][/FONT] [FONT=sans-serif][size=10] End With[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim x As Integer[/SIZE][/FONT] [FONT=sans-serif][size=10] For x = 1 To 1[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Get the name of the recipient from the user.[/SIZE][/FONT] [FONT=sans-serif][size=10] vaRecipient = "[EMAIL="[email protected]"][email protected][/EMAIL]bosmad.com"[/SIZE][/FONT] [FONT=sans-serif][size=10] Do 'Get the message from the user.[/SIZE][/FONT] [FONT=sans-serif][size=10] vaMsg = "Hello," & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Please refer to the link below for the following campaign checklist, which is now initated and stored in the repository location below:" & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Campaign:" & " " & Worksheets("Current Campaign - Final").Range("B4") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Program/Offer:" & " " & Worksheets("Current Campaign - Final").Range("B5") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Checklist Location:" & " " & Worksheets("Current Campaign - Final").Range("CC1") & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Please make sure that all tasks are signed off by placing the date and your initials on each line once completed." & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "Thank you!" & vbNewLine & vbNewLine & _[/SIZE][/FONT] [FONT=sans-serif][size=10] "PMCM Quality Manager"[/SIZE][/FONT] [FONT=sans-serif][size=10] [/SIZE][/FONT] [FONT=sans-serif][size=10] [/SIZE][/FONT] [FONT=sans-serif][size=10] [/SIZE][/FONT] [FONT=sans-serif][size=10] Loop While vaMsg = ""[/SIZE][/FONT] [FONT=sans-serif][size=10] If vaMsg = False Then Exit Sub 'If the user has canceled the operation.[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Add the subject to the outgoing e-mail which also can be retrieved from the users[/SIZE][/FONT] [FONT=sans-serif][size=10] 'in a similar way as above.[/SIZE][/FONT] [FONT=sans-serif][size=10] stSubject = Worksheets("Current Campaign - Final").Range("B2").Value & "- Usage Initiated Checklist"[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Retrieve the path and filename of the active workbook.[/SIZE][/FONT] [FONT=sans-serif][size=10] stAttachment = ActiveWorkbook.FullName[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Instantiate the Lotus Notes COM's Objects.[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noSession = CreateObject("Notes.NotesSession")[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noDatabase = noSession.GETDATABASE("", "")[/SIZE][/FONT] [FONT=sans-serif][size=10] 'If Lotus Notes is not open then open the mail-part of it.[/SIZE][/FONT] [FONT=sans-serif][size=10] On Error Resume Next[/SIZE][/FONT] [FONT=sans-serif][size=10] If noDatabase.IsOpen = False Then noDatabase.OPENMAIL[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Create the e-mail[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noDocument = noDatabase.CreateDocument[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Add values to the created e-mail main properties.[/SIZE][/FONT] [FONT=sans-serif][size=10] With noDocument[/SIZE][/FONT] [FONT=sans-serif][size=10] .Form = "Memo"[/SIZE][/FONT] [FONT=sans-serif][size=10] .sendto = vaRecipient[/SIZE][/FONT] [FONT=sans-serif][size=10] .Subject = stSubject[/SIZE][/FONT] [FONT=sans-serif][size=10] .Body = vaMsg[/SIZE][/FONT] [FONT=sans-serif][size=10] .SaveMessageOnSend = True[/SIZE][/FONT] [FONT=sans-serif][size=10] End With[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Send the e-mail.[/SIZE][/FONT] [FONT=sans-serif][size=10] Dim myMessage As String[/SIZE][/FONT] [FONT=sans-serif][size=10] myMessage = MsgBox("Are you sure you want to send your initiated Checklist location?", vbYesNo, "Are you sure?")[/SIZE][/FONT] [FONT=sans-serif][size=10] If myMessage = vbYes Then[/SIZE][/FONT] [FONT=sans-serif][size=10] With noDocument[/SIZE][/FONT] [FONT=sans-serif][size=10] .PostedDate = Now()[/SIZE][/FONT] [FONT=sans-serif][size=10] .SEND 0, vaRecipient[/SIZE][/FONT] [FONT=sans-serif][size=10] End With[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Release objects from the memory.[/SIZE][/FONT] [FONT=sans-serif][size=10] Set EmbedObject = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10] Set obAttachment = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noDocument = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noDatabase = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10] Set noSession = Nothing[/SIZE][/FONT] [FONT=sans-serif][size=10] 'Activate Excel for the user.[/SIZE][/FONT] [FONT=sans-serif][size=10] AppActivate "Microsoft Excel"[/SIZE][/FONT] [FONT=sans-serif][size=10] MsgBox "The e-mail has successfully been created and distributed.", vbInformation, "Done!"[/SIZE][/FONT] [FONT=sans-serif][size=10] Else[/SIZE][/FONT] [FONT=sans-serif][size=10] MsgBox "Unsent email!", vbInformation, "Unsent email"[/SIZE][/FONT] [FONT=sans-serif][size=10] End If[/SIZE][/FONT] [FONT=sans-serif][size=10] Next x[/SIZE][/FONT] [FONT=sans-serif][size=10]End Sub[/SIZE][/FONT][/FONT][/COLOR]