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.
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.
Code:
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.@@ " & _
"http://brains/documents/files5074/E1%20-%20Guide%201%20-%20Logging%20On%20(Go%20Live).pdf" & _
"@@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
Comment