Solved: Using Excel To Send Emails Through Lotus Notes

[shrivallabha]

I never thought that this was possible. But I found following code at
http://www.fabalou.com/VBandVBA/lotusnotesmail.asp

This code works fine except at one line:

'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.
Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
'Thanks to http://www.fabalou.com/VBandVBA/lotusnotesmail.asp
'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    
    'Next line only works with 5.x and above. Replace password with your password
    Session.Initialize ("password")
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.
    
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    'Open the mail database in notes
    
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
        'Already open for mail
    Else
        Maildb.OPENMAIL
    End If
    
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Subject = Subject
    MailDoc.Body = BodyText
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    
    'Set up the embedded object and attachment and attach it
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    
    MailDoc.SEND 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

The program however fails at the Red Marked line. The error is application does not support this property or method (438).

So I opened Notes Session. Then commented out the red marked line and tested the code. The code runs correctly.

I'd like the code to start Notes - session and login with the specified ID and then execute further. This is for the first time I'm working on official project. And as usual I've stumbled. You guys are as usual the best help I can think of. Thank you.

[shrivallabha]

I think, I am being little impatient here. I found a hint at ozgrid. So I specifically searched for XLDennis. I found some really terrific codes on Ron De Bruin's website which are developed by XLDennis.

Looking at them perhaps its not necessary to access inbox after all. Following is the link:
http://www.rondebruin.nl/notes.htm

I will test some more and get back with results tomorrow.

[shrivallabha]

The correct syntax for providing password is:

session.hashpassword ("Password")

However, using it also turned futile.

The problem now comes at:

Set Maildb = Session.GETDATABASE("", MailDbName) 
If Maildb.IsOpen = True Then 
'Already open for mail
Else 
Maildb.OPENMAIL 
End If

Since the database is protected with password, it doesn't get opened straightaway (even after providing password at beginning). The provide password screen pops up again. Here VBA transfers control to inputbox. I tried to use SendKeys. But that wouldn't send keys. I used below syntax:

Else 
Maildb.OPENMAIL 
application.sendkeys "Password~", True
End If

But the code remained hung at Maildb.OPENMAIL and inputbox remained waiting. So I pressed ALT + TAB to get to the lotus inputbox and inserted password + ENTER. The control returned back to VBA to execute sendkeys which was meaningless (as password was already provided). So the revised question now:=

Can I mimic the operation:
1. To get the lotus-inputbox
2. Punch in password + ENTER
reliably? I've searched many forums to find (MrExcel / Ozgrid / ExpertExchange) and the site above but could not find what I was looking for. Kindly note, of all the trolling, I do not have cross-posted this thread anywhere.

[Kenneth Hobs]

That is getting pretty far off this topic. Please start a new thread as I explained.

Sounds like you would use some of this. http://www.alcs.ch/html-lotus-notes-....html#more-375

[shrivallabha]

A few things before saying closed on this thread.

1. The password part is real tricky one. I could not come across a code which did that. In fact, a few posts recommended against it.

2. Initialize is not method but it is subroutine where you pass on the password string (Found through object browser).

3. It is member of NotesSession alright but the hitch is it is owned by:
Domino.NotesSession and Not by Notes.Notesession. I tried creating ActiveX object using the same method but it fails.

For all my money, I do not know what difference between 'Notes and Domino' is and it is not within my current capabilities either.

For now I have adopted and changed XLDennis' code and have provided an option button of sending mails so the user can always turn on his lotus notes and send. Thank you.

[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.

[chuckster101]

Hi Shrivallabha,

Wow, this is amazing . I tried using the code and it worked perfectly for me when sending out emails.

I also tried adding some format however its sending out plain text. How can I send it as an email if there is a table (ex D9:J13) which contains some formatted text with stats and a graph (ex called Graph 1) ?

Thanks in advance.

[shrivallabha]

Hi Shrivallabha,

Wow, this is amazing . I tried using the code and it worked perfectly for me when sending out emails.

I also tried adding some format however its sending out plain text. How can I send it as an email if there is a table (ex D9:J13) which contains some formatted text with stats and a graph (ex called Graph 1) ?

Thanks in advance.

Hello chuckster,

Welcome to VBAX. I will see if something can be worked out.

[shrivallabha]

Hello chuckster,

Welcome to VBAX. I will see if something can be worked out.

Check this link:
http://www.mrexcel.com/forum/showthread.php?t=518746
Your issue is addressed.

Only thing is it uses OLE automation so your notes shall be up and running before you run the code supplied there.

[peto04]

Hello,
i need to send emails from excel via lotus. I tried your code but i have always error in row:
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}

What i exactly need to change or add to this vba code to correct working? I am new in vba.

Thanks for help.

Peter

[shrivallabha]

Hello,
i need to send emails from excel via lotus. I tried your code but i have always error in row:
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}

What i exactly need to change or add to this vba code to correct working? I am new in vba.

Thanks for help.

Peter

Welcome to VBAX. I don't use lotus notes any more but I can definitely try to answer. What error do you get when you run the code?

I have attached a sample workbook which you can download and see if it works Check specifically post #5 for all explanation and useful links.

[peto04]

Hello,
i just copied all vba code from post 5 and I don´t know what i need to change or fill in to this code.
I download your document from post 5 and when i click on SEND; i have error:

Run-time error '429':
ActiveX component can't create object

I don´t know what does it means. Can you help me with this?

Thanks a lot.
Peter

I

[Lift]

I had been searching for a function like this but everything seemed to require Outlook. I'm using Notes and just wanted to say thanks for sharing this.
Lift

[Vb@nger]

Thanks. This is great.

[angie]

I'm trying something similar, but want to send it to different email addresses based on content in the spreadsheet. i'll use this as a start. THANKS!

[CatDaddy]

thank you! this is awesome

[dayanrod]

.

[Leti]

.

[shrivallabha]

Welcome to VBAX.

I may not be able to answer anything 'new' in Lotus Notes regard as I do not use it any more.

[Ferdinando]

Shirivallabha
I hope that even without using Lotus Notes you can answer to my question:
What should I change in the file attached by you to save messages in sent folder?

Thanks in advance!