Need to Embed Hyperlink in VBA Email (Lotus Notes)

  • 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.

    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]"[/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]
  • Re: Need to Embed Hyperlink in VBA Email (Lotus Notes)

    I have looked through a ton of answers, but none that I could just add to my existing code (all were entirely different codes that I'm not sure what they mean/how to use them). I was hoping for just a fix to my existing code.

  • Re: Need to Embed Hyperlink in VBA Email (Lotus Notes)

    Hi Stephen,
    Yes, I looked there. Those are solutions to send the workbook as an attachment. I want to send the hyperlink location, not an attachment.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!