Solved: Using Excel To Send Emails Through Lotus Notes

[shrivallabha]

Gist of all 4 posts:
If anyone wants to dig deeper to understand how things work with Lotus Notes then he / she should download IBM-Redbook and read.

I came across numerous codes on internet and some of them were really useful. Anyone who is interested in using OLE automation shall read very good code samples posted by XLDennis on Ron De Bruin's website. Following is the link:
http://www.rondebruin.nl/notes.htm

However, the problem with OLE automation is that you need Notes running to send emails [and you can do only late-binding]. That said, it otherwise gives you the flexibility that you may need. So if you want to avoid that then you will have to Domino COM which you can early bind as well. All this is available in Redbook.
OLE Automation:
[Code]nSession = CreateObject("Notes.Notesession")[/vCode]
COM:

nSession = CreateObject("Lotus.Notesession")

Using all these resources I have been able to put together a code which doesn't need notes running.
1. It will prompt you to provide password through InputBox
2. Ask you if you want to attach a file.
I have commented where I thought I might forget.
Note: The code is using late-binding but can be made early-binding using

Sub SendEmailUsingCOM()
' Unlike OLE automation, one can use Early Binding while using COM
' To do so, replace the generic "object" by "commented" UDT
' Set reference to: Lotus Domino Objects
Dim nSess       As Object 'NotesSession
Dim nDir        As Object 'NotesDbDirectory
Dim nDb         As Object 'NotesDatabase
Dim nDoc        As Object 'NotesDocument
Dim nAtt        As Object 'NotesRichTextItem
Dim vToList     As Variant, vCCList As Variant, vBody As Variant
Dim vbAtt       As VbMsgBoxResult
Dim sFilPath    As String
Dim sPwd        As String
'To create notesession using COM objects, you can do so by using.
'either ProgID  = Lotus.NotesSession
'or     ClsID   = {29131539-2EED-1069-BF5D-00DD011186B7}
'Replace ProgID by the commented string below.
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}
'This part initializes the session and creates a new mail document
sPwd = Application.InputBox("Type your Lotus Notes password!", Type:=2)
Call nSess.Initialize(sPwd)
Set nDir = nSess.GetDbDirectory("")
Set nDb = nDir.OpenMailDatabase
Set nDoc = nDb.CreateDocument
'If you want to send it to multiple recipients then use variant array to get the names from
'the specified range as below
'Add / Remove Comment mark from vCCList as per your needs.
vToList = Application.Transpose(Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row).Value)
vCCList = Application.Transpose(Range("B1").Resize(Range("B" & Rows.Count).End(xlUp).Row).Value)
'If you want to send it to multiple recipients then use variant array to get the names from
'the specified range as below
'Add / Remove Comment mark from vCCList as per your needs.
With nDoc
Set nAtt = .CreateRichTextItem("Body")
    Call .ReplaceItemValue("Form", "Memo")
    Call .ReplaceItemValue("Subject", "Test Lotus Notes Email using COM")
With nAtt
        .AppendText (Range("C2").Value)
'Decide if you want to attach a file.
        vbAtt = MsgBox("Do you want to attach document?", vbYesNo, "Attach Document")
Select Case vbAtt
            Case 6
                .AddNewLine
                .AppendText ("********************************************************************")
                .AddNewLine
                sFilPath = Application.GetOpenFilename
                Call .EmbedObject(1454, "", sFilPath) '1454 = Constant for EMBED_ATTACHMENT
            Case 7
                'Do Nothing
            End Select
End With
Call .ReplaceItemValue("CopyTo", vCCList)
    Call .ReplaceItemValue("PostedDate", Now())
    Call .Send(False, vToList)
End With
End Sub

I am also attaching the Excel Workbook used for this.
Usage:
Put addresses in separate cells (To addresses in Col A; Cc addresses in Col B)
In C2, type in message that you want to send.

And then click on "Send" and you are done!

BTW, this thread is now really "SOLVED" for me. Thanks for looking into it.