PDA

View Full Version : Solved: Using Excel To Send Emails Through Lotus Notes



shrivallabha
01-31-2011, 05:04 AM
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
01-31-2011, 07:21 AM
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
02-01-2011, 08:39 AM
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.

shrivallabha
02-11-2011, 12:50 AM
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
05-08-2012, 06:26 AM
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 (http://www.redbooks.ibm.com/redbooks/pdfs/sg245670.pdf) 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:

nSession = CreateObject("Notes.Notesession")[/vCode]
COM:
[Code]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
06-05-2012, 12:56 PM
Hi Shrivallabha,

Wow, this is amazing :thumb. 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.

CatDaddy
06-05-2012, 01:59 PM
thank you! this is awesome

shrivallabha
06-06-2012, 06:11 AM
Hi Shrivallabha,

Wow, this is amazing :thumb. 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
06-07-2012, 06:55 AM
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.

canini
05-19-2013, 11:08 PM
tks for sharing

dayanrod
07-02-2013, 07:06 PM
.

Leti
08-21-2013, 10:53 AM
.

shrivallabha
08-22-2013, 08:20 AM
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
09-11-2013, 06:46 AM
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!

shrivallabha
09-11-2013, 07:39 AM
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!
Hi Ferdinando,

Welcome to VBAX. I'd try to change:

Call .Send(False, vToList)
to

Call .Send(True, vToList)

joee74
09-13-2013, 06:12 AM
Hi Guys

I am very new to VBA (just one week now) and I am working on a quoting system for work, which I have finished, but now need to have a button that opens an email, puts in a set statement, then adds the signature which I have set in Notes.

I have written the code below, (ok, so I copied it from the web and ammended it to suit me) and all is working apart from I dont know how to get this to insert my signature into the mail when it opens..

When I open a new mail in Notes normally, it always inserts my signature. but not when I run this module.

Additionally, there will be two users using this sheet from two separate computers, each with their own LotusNotes operating, so it will need to take the signature from their machine.

Here is the code I have so far

Sub SendQuoteToEmail()

Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim subject As String
Dim EmailAddress As String

subject = Worksheets("Internal").Range("BD1")
EmailAddress = Worksheets("Internal").Range("BD2")
Debug.Print subject

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

Set NDoc = NDatabase.CreateDocument

With NDoc
.SendTo = EmailAddress
.CopyTo = "digitalprint@zcard.com"
.subject = subject

.body = "Dear" & " " & Worksheets("internal").Range("j10") & vbLf & vbLf & _
"Many Thanks for your enquiry" & vbLf & vbLf & _
"Please find Attached your Quotation" & vbLf & vbLf & _
"If you would like to go ahead with this order, please let me know and I will send you a template, artwork guidelines and procedures for processing your order."

.Save True, False
End With

Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
With NUIdoc

Set WordApp = Nothing
End With

Set NSession = Nothing

End Sub

joee74
09-13-2013, 07:08 AM
Sub SendQuoteToEmail()

Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim subject As String
Dim EmailAddress As String

subject = Worksheets("Internal").Range("BD1")
EmailAddress = Worksheets("Internal").Range("BD2")
Debug.Print subject

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

Set NDoc = NDatabase.CreateDocument

With NDoc
.SendTo = EmailAddress
.CopyTo = "digitalprint@zcard.com"
.subject = subject

.body = "Dear" & " " & Worksheets("internal").Range("j10") & vbLf & vbLf & _
"Many Thanks for your enquiry" & vbLf & vbLf & _
"Please find Attached your Quotation" & vbLf & vbLf & _
"If you would like to go ahead with this order, please let me know and I will send you a template, artwork guidelines and procedures for processing your order."

.Save True, False
End With

Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
With NUIdoc

Set WordApp = Nothing
End With

Set NSession = Nothing

End Sub

Kenneth Hobs
09-13-2013, 07:42 AM
Welcome to the forum! Please start your own thread for future posts. If a thread like this one relates, post the link (after you get 5 posts) or reference the thread number, 35917, if it might help others help you.


signature = Maildb.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)

joee74
09-13-2013, 07:54 AM
Hi Kenneth

I will remember that for future posts thank you.

One question, where do I put this line of code into my existing code?

Kenneth Hobs
09-13-2013, 09:38 AM
I put my signatures at the end of the body myself.


Sub SendQuoteToEmail()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim subject As String
Dim EmailAddress As String
Dim s(1 To 5) As String

subject = Worksheets("Internal").Range("BD1")
EmailAddress = Worksheets("Internal").Range("BD2")
'Debug.Print subject

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GETDATABASE("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

Set NDoc = NDatabase.CREATEDOCUMENT

With NDoc
.SendTo = EmailAddress
.CopyTo = "digitalprint@zcard.com"
.subject = subject
s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
s(2) = "Many Thanks for your enquiry"
s(3) = "Please find Attached your Quotation"
s(4) = "If you would like to go ahead with this order, please let me know and I will send you a template, artwork guidelines and procedures for processing your order."
s(5) = " "
.body = Join(s, vbCrLf & vbCrLf) & _
NDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
.Save True, False
End With

NUIWorkSpace.EDITDOCUMENT True, NDoc

Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
End Sub

joee74
09-13-2013, 11:29 AM
Hi Kenneth.

I have done this and I can see its working looking for it, but instead of my signature appearing, the following is appearing:


C:\Documents and Settings\Joe\My Documents\joeellis-sig.html

I am using Windows 7, so there is no documents and settings.

the signature file is saved at the following destination.

C:\Users\joeellis\Documents\joeellis-sig.html

Obviously the user name and file name will change depending on who is using this spreadsheet
I really do appreciate your help here.

Kenneth Hobs
09-13-2013, 12:08 PM
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-email-including-html-signature-from-excel-with-vba.html#more-375

joee74
09-13-2013, 12:12 PM
Thanks Kenneth, excuse me for my ignorance, I am new to all of this.

I will start a new thread.

Appreciate your help

amauta
09-30-2013, 09:48 AM
Thanks!

Jacques.Grob
10-01-2013, 04:35 AM
Hi Shrivallabha

GREAT post! Thanks

peto04
10-09-2013, 03:46 AM
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
10-09-2013, 10:30 AM
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
10-15-2013, 12:42 AM
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

shrivallabha
10-17-2013, 07:49 AM
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
Bit strange. I've posted working code and some others have used successfully as well.

If you go to Visual Basic Editor | Tools | References, can you find the following reference:
"Lotus Domino Objects"

Kenneth Hobs
03-13-2014, 04:47 PM
There are several responses here so I don't know which you mean. In this, .Send should suffice.


Option Explicit

Sub test()
Dim lErr As ErrObject
SendNotesMail _
"PMP Handbook5", _
"c:\t.pdf", _
"khobson@work.org,khobson@aaahawk.com", _
"Click file: " & vbCrLf & _
"file://u:\Material\pmp\PMP%20Handbook.pdf" & vbCrLf & _
"or, open the attachement.", , lErr
If lErr.Number <> 0 Then MsgBox lErr.Number & vbCrLf & lErr.Description
End Sub

'Escape characters, %20=space, http://everything2.com/node/1350052
'Similar to: Brian Walters, http://www.ozgrid.com/forum/showthread.php?t=67089
Public Sub SendNotesMail(Subject As String, Attachment As String, _
ByVal Recipient As String, _
BodyText As String, _
Optional SaveIt As Boolean = True, _
Optional ByRef lErr As ErrObject)
'lErr is used when using the Sub in a batch process,
'to handle instances where an error appears

'Example of use:
'SendNotesMail "The Subject", "C:\My Documents\TestFile.txt", _
"john@doe.com, jane@doe.com", _
"This is the body text, can be longer", True, lErr

'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 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)
Dim ArRecipients() As String 'Array of recipients
Dim i As Long 'Counter
Dim oBody As Object 'Body of text in for rich text format

'Early Bind - Tools > Reference > Lotus Notes Automation Classes, notes32.tlb
'Dim ln As lotus.NOTESSESSION
'Set ln = CreateObject("Notes.NotesSession")
'Dim db As lotus.NOTESDATABASE
'Set db = ln.GETDATABASE("", "mail\username.nsf")
'Dim mDoc As lotus.NOTESDOCUMENT
'Set mDoc = db.CREATEDOCUMENT


'Create an array of recipients (Separated by commas)
ArRecipients() = Split(Recipient, ",")

'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
On Error GoTo err_h

'Open the mail database in notes
UserName = Session.UserName
'************** ADD YOUR username.
Set Maildb = Session.GETDATABASE("", "mail\khobson.nsf")
If Maildb.IsOpen = False Then
Maildb.OPENMAIL
End If

'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = ArRecipients
MailDoc.Subject = UCase(Subject)
'MailDoc.Body = UCase(BodyText)
Set oBody = MailDoc.CREATERICHTEXTITEM("Body")
oBody.APPENDTEXT BodyText

'This is supposed to be the property, but works
'on some systems only
'without an apparent reason of failure
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")
End If

'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.Send 1
MailDoc.Save True, True, False

'Clean Up
err_h:
Set lErr = Err
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

Lift
03-15-2014, 12:23 PM
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

Mirek1983
03-26-2014, 06:12 AM
Hello,

I have a issue with this code by one of my employees.

I have used this code to send e-mail via Excel.

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

After the employee enter his password to LotusNotes he is getting a Object error -2######.

11454

I don't know where the issue can be?

Thank you for any help,

bronzetch
07-31-2014, 08:39 AM
Thanks a lot Shrivallabah

majdimarmas
09-01-2014, 11:42 PM
Can anyone advice how to add one attachment to the code above ( by Mr. Kenneth Hobs 09-13-2013, 08:38 AM )
I'de like to always have the same file attached.

Thank you in advance

majdimarmas
09-01-2014, 11:45 PM
Can you please help me adding modifying the code to have attach a file to the created mails? Always the same file.




Sub SendQuoteToEmail()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim subject As String
Dim EmailAddress As String
Dim s(1 To 5) As String
Dim r As Integer
Dim x As Double



NumOfRows = Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For r = 2 To NumOfRows


subject = Worksheets("sheet1").Range("B2")
EmailAddress = Worksheets("sheet1").Range("c2")
'Debug.Print subject

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GETDATABASE("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

Set NDoc = NDatabase.CREATEDOCUMENT

With NDoc
.SendTo = EmailAddress
.CopyTo = "sami"
.subject = subject
s(1) = "Dear" & " " & Worksheets("sheet1").Range("D2")
s(2) = "Many Thanks for your enquiry"
s(3) = "Please find Attached your Quotation"
s(4) = "If you would like to go ahead with this order, please let me know and I will send you a template, artwork guidelines and procedures for processing your order."
s(5) = " "
.body = Join(s, vbCrLf & vbCrLf) & _
NDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
.Save True, False
End With

NUIWorkSpace.EDITDOCUMENT True, NDoc

Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
Next r
End Sub

dontlose
08-21-2015, 12:19 PM
thanks a lot!

angie
10-19-2015, 04:43 PM
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!

Liamhead
10-29-2015, 05:40 PM
Thanks Shrivallabha!

eurano
03-20-2016, 12:51 PM
This is awesome.

mdrr_73
06-28-2016, 10:03 AM
Thanks for posting

coarena
08-10-2016, 04:32 AM
Thank you

Kal-El
03-02-2017, 11:16 AM
This is great now how can I instead save 1 worksheet in the workbook and have it attach?

Vb@nger
08-23-2017, 11:13 PM
Thanks. This is great.