This is a VBA script that allows you to send a Lotus Notes email with VBA from excel. What is new here is that you can write the email in HTML and also attach your standard HTML signature. This seems to have been a major issue with VBA generated Lotus notes emails, as the signature is usually missing. The script also takes the email addresses from an excel spreadsheet.
Below you find the VBA source code and the xlsm file.
Sub SendLocalExtensionEmail()
''''''''''''''''''''''''''''''''''
' 2012-12-19
' V 0.2
''''''''''''''''''''''''''''''''''
Dim nMailBody As String
Dim nMailSubject As String
Dim nMailRecipient As Variant
Dim nMail As Object
Dim nSession As Object
Dim nDatabase As Object
Dim nMime As Object
Dim nMailStream As Object
Dim nChild As Object
Dim nSomeMailBodyText As String
Dim amountOfRecipients As Integer
nSomeMailBodyText = "<i> You are looking at some awesome text here!</i>"
nMailRecipient = ""
nMailSubject = "A great email"
Set nSession = CreateObject("Notes.NotesSession")
Set nDatabase = nSession.GetDatabase("", "")
Call nDatabase.OPENMAIL
Set nMail = nDatabase.CreateDocument
'Example on how to get email adresses from a spreadsheet
With ThisWorkbook.Sheets("EmailRecipients")
amountOfRecipients = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
nMailRecipient = ThisWorkbook.Sheets("EmailRecipients").Range("A1:A" & amountOfRecipients).Value
nMail.SendTo = nMailRecipient
nMail.Subject = nMailSubject
nSession.ConvertMIME = False
Set nMime = nMail.CreateMIMEEntity
Set nMailStream = nSession.CreateStream
'vBody contient le texte au format Html
Call nMailStream.WriteText(nSomeMailBodyText)
Call nMailStream.WriteText(" - and again - ")
Call nMailStream.WriteText(nSomeMailBodyText)
Call nMailStream.WriteText("<br>")
Call nMailStream.WriteText("<br>")
'----- READ AND PASTE SIGNATURE -------------------------------------
'Get the standard signature location
nSignatureLocation = nDatabase.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)
'nSignatureLocation = "C:\disclaimer.htm" 'In case you would like to specify a path to an html file instead
'Required Reference: Windows Script Host Object Model
Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.OpenTextFile(nSignatureLocation, ForReading)
Dim ThisLine As String
Dim i As Integer
i = 0
Do Until ts.AtEndOfStream
ThisLine = ts.ReadLine
'i = i + 1
'MsgBox ThisLine
Call nMailStream.WriteText(ThisLine)
Loop
ts.Close
'-------------------------------------------------------------------
Set nChild = nMime.CreateChildEntity
Call nChild.SetContentFromText(nMailStream, "text/html;charset=iso-8859-1", ENC_NONE)
Call nMailStream.Close
nSession.ConvertMIME = True
Call nMail.Save(True, True)
'Make mail editable by user
CreateObject("Notes.NotesUIWorkspace").EDITDOCUMENT True, nMail
'Could send it here
End Sub
I created an HTML signature for my email client to use when I send emails (lotus notes). Is there any HTML code that I can use to manipulate the space between two specific lines?
There is no html code as it can not actively manipulate the lines. However, I assume you mean vba? Using vba, you can easily have the loop do something at line x. Just use an “If $line = x Then” within the loop that reads the signature to do what you wish.
Hi, this example works great,
can you help me enhancing it a little bit?
I have a database, supporting reorganizations. Th db contains tasks, to be executed during the reorgs. Some of the tasks need to send an email to a group of users.
I have a memo-field im my MS-Access 2010-database, containing the mailtext. When trying to transfer this to notes, the text is truncated after 255 bytes. Here ist the code doing the transfer:
There is one procedure, where I retrieve the data from the database (filling the recordset rst_Aufgaben_unerledigt_nahe_Zukunft_Mailversand)
Dim MailSubject as string, MailText as string
MailSubject = rst_Aufgaben_unerledigt_nahe_Zukunft_Mailversand!MailSubject
MailText = rst_Aufgaben_unerledigt_nahe_Zukunft_Mailversand!MailText
If I debug, I can see, that the contents of field rst_Aufgaben_unerledigt_nahe_Zukunft_Mailversand!MailText has the length of 510.
After assigning the value to Mailtext, the contents is truncated to 255.
Do you know why? If the content of the memo-field is short enought (<255) the code is working fine.
Best regards
Bernd
I didn’t know there was a limit. Just split the string every 250 chars looping it into the mail text, that should do the trick.
Cheers
the script works great! however, if the signature contains images, they’re not displayed. you know what can be causing this? thanks!
Hi david
The pictures from my html signature are displayed just fine. To debug I’d output the html code into a text file and see if it differs in any way from the signature file. If you look at the output in a browser, will it display the image? Maybe a path issue?
Cheers
I did the output and all images are displayed in my browser. however, when inserted in the Lotus Notes message, they’re not displayed (the text yes, but the images not). do you think there’s something in the Notes setup I need to change?
also, this text is added automatically before the signature:

thanks.
@ Adrian