Hello,
I have the macro written below within a workbook. It's purpose is to send an email notification (in Lotus) when the button is hit, and it works great except the recipient of the email wants the location to be a hyperlink, and I can't figure out how to do that. Can anyone help? The part that requires the hyperlink is in the message body where it says "Checklist Location: " I basically want a link to where the checklist is saved, which is in a Sharepoint site.
Thank you for your help!
Code
[FONT=sans-serif][size=10][COLOR=#000000]Sub InitiateChecklist()[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000]Dim noSession As Object, noDatabase As Object, noDocument As Object[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim obAttachment As Object, EmbedObject As Object[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim stSubject As Variant, stAttachment As String[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim vaRecipient As Variant[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim vaMsg As Variant[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim valink As Variant[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Const EMBED_ATTACHMENT As Long = 1454[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Const stTitle As String = "Status Active workbook"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Const stMsg As String = "The active workbook must first be saved " & vbCrLf _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] & "before it can be sent as an attachment."[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'If the active workbook has not been saved at all.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] If Len(ActiveWorkbook.Path) = 0 Then[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] MsgBox stMsg, vbInformation, stTitle[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Exit Sub[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] End If[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim X As Integer[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] For X = 1 To 1[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Get the name of the recipient from the user.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] vaRecipient = "[EMAIL="[email protected]"][email protected][/EMAIL]"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Do 'Get the message from the user.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] vaMsg = "Hello," & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "Please refer to the link below for the following campaign checklist, which is now initated and stored in the repository location below:" & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "Campaign:" & " " & Worksheets("PMCM Checklist").Range("C2") & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "Program/Offer:" & " " & Worksheets("PMCM Checklist").Range("C3") & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "Checklist Location:" & " " & ActiveWorkbook.FullName & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "I have sent the checklist location to the appropriate contacts in Marketing Fulfillment, RIO, and PCM as applicable." & vbNewLine & vbNewLine & _[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] "Thank you!"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] [/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Loop While vaMsg = ""[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] If vaMsg = False Then Exit Sub 'If the user has canceled the operation.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Add the subject to the outgoing e-mail which also can be retrieved from the users[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'in a similar way as above.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] stSubject = Worksheets("PMCM Checklist").Range("C2").Value & "-Initiated Checklist"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Retrieve the path and filename of the active workbook.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] stAttachment = ActiveWorkbook.FullName[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Instantiate the Lotus Notes COM's Objects.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noSession = CreateObject("Notes.NotesSession")[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noDatabase = noSession.GETDATABASE("", "")[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'If Lotus Notes is not open then open the mail-part of it.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] On Error Resume Next[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] If noDatabase.IsOpen = False Then noDatabase.OPENMAIL[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Create the e-mail[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noDocument = noDatabase.CreateDocument[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Add values to the created e-mail main properties.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] With noDocument[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .Form = "Memo"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .sendto = vaRecipient[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .Subject = stSubject[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .Body = vaMsg[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .SaveMessageOnSend = True[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] End With[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Send the e-mail.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Dim myMessage As String[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] myMessage = MsgBox("Are you sure you want to send your initiated Checklist location?", vbYesNo, "Are you sure?")[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] If myMessage = vbYes Then[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] With noDocument[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .PostedDate = Now()[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] .SEND 0, vaRecipient[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] End With[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Release objects from the memory.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set EmbedObject = Nothing[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set obAttachment = Nothing[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noDocument = Nothing[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noDatabase = Nothing[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Set noSession = Nothing[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] 'Activate Excel for the user.[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] AppActivate "Microsoft Excel"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] MsgBox "The e-mail has successfully been created and distributed. Please send Checklist location to your supporting team members via email now.", vbInformation, "Done!"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Else[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] MsgBox "Unsent email!", vbInformation, "Unsent email"[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] End If[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] Next X[/COLOR][/SIZE][/FONT][COLOR=#000000][FONT=Helvetica] [/FONT][/COLOR]
[FONT=sans-serif][size=10][COLOR=#000000] End Sub[/COLOR][/SIZE][/FONT]