Lotus Notes Hyperlinks and VB script.

[DaveB]

I've got a quick and dirty script to read a list of email addresses and other info from a spread sheet and generate individual emails using lotus notes.

In the body of the email I need to include a hyperlink, but for the life of me I can't get it to appear in the mail as anything but a text string.

Code is below, any suggestions on how to get the URL to appear in the mail as a hyper link would be appreciated.

Sub SendNotesMail()

'Takes a list of email addresses in Column 1 and sends the email defined in MailDoc.Body to
'each address on the list. Emails must be in standard name@location format and start on line 2
'with a header row.  Additional data to be included can be added to cells to the right of each address
'and referenced in the body text.
'
'Note : You must have Lotus Notes open and running on your PC for this to work.  It will not open
'Notes for you.  If you intend to use this script you should check with the IT department first
'as it may cause problems with the email service if not properly managed.

Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim cl As Integer

'Setup the notes session for use by the script and get the username of the person logged into the PC.
'This is the person who will appear in the sent field of the email.
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName

'Open the users Mail Database for sending mail.
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)

If Not Maildb.IsOpen Then Maildb.OpenMail

'This is the loop that works down the spreadsheet and generates the mails.
'It's not elegant but will stop automatically when it reaches a blank row.
'It should really use a While Do loop but I didnt have time to work it out.
For cl = 2 To 10000
    
    Recipient = Cells(cl, "A")
    If Recipient = "" Then GoTo Audi
    
        Set MailDoc = Maildb.CREATEDOCUMENT
    
        MailDoc.Form = "MEMO"
        MailDoc.SendTo = Recipient
        'MailDoc.CopyTo = Whomever
        'MailDoc.BlindCopyTo = Whomever
        MailDoc.Subject = "E1 USER ID AND PASSWORD INFORMATION - PLEASE READ"
        MailDoc.Body = _
            Replace("This Email contains your username and password for E1." & _
            "  Your account will be enabled from 06:00 am on the 10th of August." & _
            "  Please do not try to login before this time." & _
            "@@If you have any problems logging in please refer to the user guide on brains " & _
            "or call the Service Desk.@@ " & _
            [B]"http://brains/documents/files5074/E1%20-%20Guide%201%20-%20Logging%20On%20(Go%20Live).pdf"[/B] & _
            "@@User Name : " & Cells(cl, "B") & _
            "@Password  : " & Cells(cl, "B"), "@", vbCrLf)

        MailDoc.SAVEMESSAGEONSEND = True
        MailDoc.PostedDate = Now
    
        MailDoc.SEND 0, Recipient
Next

Audi:
'Close the mail session nicely and clean up after ourselves.
Set Maildb = Nothing
Set MailDoc = Nothing
Set Session = Nothing
End Sub

 

[xoggoth]

Could you include the message as HTML and put <a href= in?

 

[DaveB]

Could you include the message as HTML and put <a href= in?

Probably, if I knew how. Back to google!

 

[xoggoth]

Just typed www.xoggoth.org into an email in notepad and found it comes up as a link without having to do anything. Maybe pdf and no www not so easily recognisable.

Why not send yourself an email with that link, open in notepad and see how it did it?

C:\Users\<user id>\AppData\Local\Microsoft\Windows live mail or similar

 

[DaveB]

Just typed xoggoth into an email in notepad and found it comes up as a link without having to do anything. Maybe pdf and no www not so easily recognisable.

Why not send yourself an email with that link, open in notepad and see how it did it?

C:\Users\<user id>\AppData\Local\Microsoft\Windows live mail or similar

If I send the mail to myself internally the Hyperlink comes out as plain text. If I then forward it to my gmail account and back it comes out as a Hyperlink. Notes normally recognises hyperlinks automagically so there is something odd happending somewhere.