How to send Lotus Notes e-mail using Excel VBA ?

There are times when as VBA programmers/Analyst we require to send e-mails after our analysis or dashboard development. This is very trivial that when a dashboard is completed, it is required to send to our seniors or the required recipients. So, there are times, when we've set the VBA program to automate the dashboard development and then we need to send that dashboard by attaching it to mail manually. Did this ever happened that you developed the dashboard and forgot to send to the requested/desired persons? Oops !

Now this article will help us in learning the automation of e-mailing the dashboard (or any file) using Lotus Notes and VBA. This is a referential program and we might need to change the appropriate variables in the program to make it run at our end.

With the introduction of Lotus Notes and Domino Release 5.0.2b, we now have the ability to manipulate the Domino object model via COM. As a result, we can use VBA to take advantage of Lotus/Domino services and databases.

To do so, however, we'll need Lotus Notes client version 5.02.b (or higher), Domino Designer client, or Domino Server. As with most Visual Basic object libraries, the programs need not be running to use them. Lotus has plans to make this runtime package distributable independent of its full software installation. Notice that before we can access any of the objects within the Notes Session, we must initialize a session first.

Anyway, let's directly do the practical which involves two simple steps:

1. Set a reference to "Lotus Notes Automation Classes". In the code window, go to Tool --> References --> Select 'Lotus Notes Automation Classes".

2. Use the following Visual Basic code to send a Notes e-mail message. The code includes examples of code to include an attachment and to save the sent message, which are both optional and can be removed if desired. Don't forgot to replace the underlined phrases with your ones.

Sub Send_Email_via_Lotus_Notes()
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim Body As Object
    Dim Session As Object
    'Start a session of Lotus Notes
        Set Session = CreateObject("Lotus.NotesSession")
    'This line prompts for password of current ID noted in Notes.INI
        Call Session.Initialize
    'or use below to provide password of the current ID (to avoid Password prompt)
        'Call Session.Initialize("<password>")
    'Open the Mail Database of your Lotus Notes
        Set Maildb = Session.GETDATABASE("", "D:\Notes\data\Mail\eXceLiTems.nsf")
        If Not Maildb.IsOpen = True Then Call Maildb.Open
    'Create the Mail Document
        Set MailDoc = Maildb.CREATEDOCUMENT
        Call MailDoc.REPLACEITEMVALUE("Form", "Memo")
    'Set the Recipient of the mail
        Call MailDoc.REPLACEITEMVALUE("SendTo", "Ashish Jain")
    'Set subject of the mail
        Call MailDoc.REPLACEITEMVALUE("Subject", "Subject Text")
    'Create and set the Body content of the mail
        Set Body = MailDoc.CREATERICHTEXTITEM("Body")
        Call Body.APPENDTEXT("Body text here")
    'Example to create an attachment (optional)
        Call Body.ADDNEWLINE(2)
        Call Body.EMBEDOBJECT(1454, "", "C:\dummy.txt", "Attachment")
    'Example to save the message (optional) in Sent items
        MailDoc.SAVEMESSAGEONSEND = True
    'Send the document
    'Gets the mail to appear in the Sent items folder
        Call MailDoc.REPLACEITEMVALUE("PostedDate", Now())
        Call MailDoc.SEND(False)
    'Clean Up the Object variables - Recover memory
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set Body = Nothing
        Set Session = Nothing
End Sub


Anonymous said…
HI I have tried using this code and it always brings back activex cannot create object ??
Anonymous said…
'Call Body.EMBEDOBJECT(1454, "", "C:\dummy.txt", "Attachment")

comment this line and it will work
Anonymous said…
'Call Body.EMBEDOBJECT(1454, "", "C:\dummy.txt", "Attachment")

comment this line and it will work
Anonymous said…
I am not able to see the message that I sent under Sent Folder on my Lotus Notes.