Email Multiple Recipients from Lotus notes

Check out this link: http://www.fabalou.com/VBandVBA/lotusnotesmail.asp

NG,

see

https://www.experts-exchange.com/questions/22154974/Lotues-Notes-Group-Mail-VBA-Mail-Automation.html
and

https://www.experts-exchange.com/questions/22641409/Access-VBA-and-Lotus-Notes.html

Please notice that you may be limited to 32K of text in the To: field.

I hope this helps !

also see

https://www.experts-exchange.com/questions/22950699/Using-MAPI-to-send-Lotus-Notes-E-mail-from-Access-2003.html

It is bad practice to disclose recipients to other.
Simple suggestion is to use MAPI thru VBS to post multiple 100-recipient mails (rfc states that server is not required to accept more, and you should be able to split if you need more)

I can't magically guess what exitsing code you were using in order tof igure out why it has the limitation you describe.  You could be using a MAPI interface, and OLE interface to manipulate the memo directly on screen, and OLE interface to create the message directly in the mailbox, then only display the created message, or a COM version of the OLE interfaces. That's five possible basic ways, without getting into variations of the above.  I also don;t know whether you are sending one message per recipient or a message with multiple recipients.

Oh, and it isn't a bad practice to disclose recipients to each other.  It is a bad practice to expose recipient names when it violates privacy. In a corporate environment, it might never violate privacy, or only if the information were sensitive in nature ("you are [not] getting a bonus," "your position is being eliminated").

So far for 125 points and mass mailing support - can we agree on that?

I'm not really that great at programing i can enterpret code and change it.   I don't know how to use MAPI, is there a refference for it in Access 2003 ?

It is on msdn.microsoft.com

here is the code i'm using is there any way of making it work with 30 recipeints ?

Private Sub Command103_Click()
Dim rs As New ADODB.Recordset
Dim strEmail As String

rs.Open "YourEmailTable", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

strEmail = ""
Do While Not rs.EOF
strEmail = strEmail & rs!emails & ";"
rs.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "test", "Test", True

rs.Close
Set rs = Nothing

DoCmd.SendObject uses MAPI as the underlying interface. Best NOT to use MAPI if you are doing this with Notes.  The interface is limited, and SendObject has its own problems on top of that.  Here's something that just uses lower level Notes APIs to automate everything, yet is pretty simple to code:

BEFORE YOUR LOOP:

Set Lotus = CreateObject("Notes.NotesSession")
Set mailbox = Lotus.getDatabase("", "names.nsf")
Set msg = mailbox.CreateDocument
msg.replaceItemValue "Form", "Memo"
Set msgNoteText = msg.createRichTextItem("Body")

'The next two lines can also be above your loop if you have the same subject and body text for all messages
msg.replaceItemValue "Subject",                                                         "Greetings folks!"
body.appendText                                                                               "blah blah blah message text blah blah blah"

'The next line would also be above the loop if you have a single attachment that is included with all messages
body.embedObject 1454 , "" ,                                                                     "c:\testmail.doc"

'Next line is only one that MUST be inside loop, tho you may also loop 3 three previous lines (replaceItemValue, appendText, embedObject):
msg.Send 0,                                                                                 strEmail

This is an OLE interface. COM interface would be more efficient, but is a little more complicated to set up.
One problem with this.  It actually sends the message, it does not leave it on screen unsent, waiting for your action, as you suggest in your question. If that is a requirement, I'll explain how to get that to work; otherwise, this is good.

I'll try this tommorow and let you know if it worked, but i do need to view the email b4 i send it outl.   The reson is the file name changes.  I'll have to do the attachment manually.  Atleast for now.

Notes is the way to go just comment out this line and it won't be sent.

"MailDoc.SEND 0, Recipient"


NG,

Ok Here is my code,  I'm getting an  Expected variable or procedure, not project    Error ?  


Private Sub Email_Click()

Dim rs As New ADODB.Recordset
Dim strEmail As String


Set Lotus = CreateObject("Notes.NotesSession")
Set mailbox = Lotus.GetDatabase("", "names.nsf")
Set msg = mailbox.CreateDocument
msg.ReplaceItemValue "Form", "Memo"
Set msgNoteText = msg.CreateRichTextItem("Body")

msg.ReplaceItemValue "Subject", "Greetings folks!"
body.AppendText "blah blah blah message text blah blah "




On Error GoTo send_Err

rs.Open "tblAttendeeList", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

strEmail = ""


'The next line would also be above the loop if you have a single attachment that is included with all messages
'body.EmbedObject 1454, "", "c:\testmail.doc"



Do While Not rs.EOF
If Not IsNull(rs!Email) Then
strEmail = strEmail & rs!Email & ";"
End If
rs.MoveNext
Loop '


'DoCmd.SendObject , , , strEmail, , , "AM Consolidated DSR" & "   " & Date - 1, "Attached is the AM Consolidated DSR " & "   " & Date - 1, True

send_Err:
If Err.Number = 2501 Then
MsgBox "You have cancelled the e-mail - it will not be sent.", vbInformation, "Notice"
Else
Exit Sub
End If

rs.Close
Set rs = Nothing


End Sub

What line gives the error? It may be:
body.AppendText "blah blah blah message text blah blah "

That should really be:
msgNoteText.AppendText "blah blah blah message text blah blah "

...and you left out the line:
Set msgNoteText = msg.createRichTextItem("Body")
...which needs to be executed before those two lines

I made the same variable naming mistake in
body.EmbedObject 1454, "", "c:\testmail.doc"

Which should be
msgNoteText.EmbedObject 1454, "", "c:\testmail.doc"


(The field named BODY is supposed to contain the bulk of the message and any attachments. I had assigned that to a variable msgNoteText, an assignment that you had dropped. But I then mixed it up, and used a variable named body to interact with the body field, when I should have been using msgNoteText.)

To accommodate your I-need-to-edit-before-sending requirement, we need to SAVE instead of SEND. That will place all the messages in your DRAFTS folder in Notes, where you can open, attach, send. But since the SEND contained the recipient name(s), we have to apply that value before we save. Of course, if you had a way of letting the script know which file to attach, all that would not be necessary.

The error handling you had was for the SendObject procesisng, and is not relevant for this version. It would also not be difficult to "ask" Notes to actually bring the draft on-screen automatically.

Now, your complete code should be as follows:

Private Sub Email_Click()
 Dim rs As New ADODB.Recordset
 Dim strEmail As String

 Set Lotus = CreateObject("Notes.NotesSession")
 Set mailbox = Lotus.GetDatabase("", "names.nsf")
 Set msg = mailbox.CreateDocument
 msg.ReplaceItemValue "Form", "Memo"
 Set msgNoteText = msg.CreateRichTextItem("Body")

 msg.ReplaceItemValue "Subject", "Greetings folks!"
 msgNoteText.AppendText "blah blah blah message text blah blah "
 'msgNoteText.EmbedObject 1454, "", "c:\testmail.doc"

 On Error GoTo send_Err

 rs.Open "tblAttendeeList", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 strEmail = ""
 Do While Not rs.EOF
   If Not IsNull(rs!Email) Then
     strEmail = strEmail & rs!Email & ";"
   End If
   rs.MoveNext
 Loop

 rs.Close
 Set rs = Nothing

 msg.replaceItemValue "SendTo", strEmail
 msg.save True,True
End Sub

Set lotus = CreateObject("Notes.NotesSession")

This is the line that is causing it, and i'm still getting that error ? Even after i pasted in your code.

if you change the work set lotus to set lotuss or what ever it works, but nothing happens , it does not open a new email ?

Have you set a reference to LotusNotes in your references?

NG,

Yep both lotus

 notes domino objects

and Lotus notes Automation Classes

Your project obviously can't create the Lotus Notes object...  what does the error say exactly?

There is no error i click to command button and nothing happens.  And yes i'm sure that it's set up correctly.  

If anyone has a working example of a Lotus notes in Access sending an email to multiple recipients you can email it to me at ekomsky1@yahoo.com,  Maybe i can figure it out that way.   Thank you.

If it were setup correctly then it would be sending mail... The source code link that has been posted works I use it nearly once a week so it has something to do with implementation.

From your previous statement  "Ok Here is my code,  I'm getting an  Expected variable or procedure, not project    Error ?  "

Can you explain what you meant?

Have you tried stepping through your code?

NG,

You can try this... to send to multiple persons via Notes, comma separate the recipients.


Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
'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")
    '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
    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.copyto =
    'Maildoc.blindcopyto =
    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



Public Sub Send_Click()
Call SendNotesMail("test", "C:\Test.txt", "you@yourDomain.com, him@yourDomain.com", "This is a test", True)
End Sub

When i step through thecode nothing happens.   It just keeps going througt it step by step it's like the Notes is being open hidden or somehting.

Dim rs As New ADODB.Recordset
 Dim strEmail As String

 Set Lotus = CreateObject("Notes.NotesSession")
 Set mailbox = Lotus.getDatabase("", "")
 mailbox.OpenMail
 Set Msg = mailbox.CreateDocument
 msg.replaceItemValue "Form", "Memo"
 Set msgNoteText = msg.CreateRichTextItem("Body")

 msg.replaceItemValue "Subject", "Greetings folks!"
 msgNoteText.AppendText "blah blah blah message text blah blah "
 'msgNoteText.EmbedObject 1454, "", "c:\testmail.doc"

 rs.Open "tblAttendeeList", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 strEmail = ""
 Do While Not rs.EOF
   If Not IsNull(rs!Email) Then
     strEmail = strEmail & rs!Email & ";"
   End If
   rs.MoveNext
 Loop

 rs.Close
 Set rs = Nothing

 msg.replaceItemValue "SendTo", strEmail
 msg.Save True, True
 CreateObject("Notes.NotesUiWorkspace").EditDocument True, Msg
End Sub



PS An alternative object access method:
 Set Lotus = CreateObject("Notes.NotesSession")
 mailServer = Lotus.getEnvironmentString("MailServer", True)
 mailFile = Lotus.getEnvironmentString("MailFile", True)
 Set mailbox = Lotus.getDatabase(mailServer, mailFile)
 if not mailbox.isOpen Then Call mailbox.open("","")
 Set msg = mailbox.CreateDocument
And another, which is "cleaner," but has a possible password prompt and can't open the document on-screen when finished:
 Set Lotus = CreateObject("Lotus.NotesSession")
 Lotus.Initialize
 Set mailbox = Lotus.getDbDirectory("").openMailDatabase
 Set msg = mailbox.CreateDocument

(The code in the above post WAS ACTUALLY RUN THROUGH ACCESS and resulted in Notes having the requested memo displayed on screen.)

You code worked but only the firs 4 emails in the list appear the rest don't.   I had this issue before.  

I got around it by making a group in notes but, only i can use it, and only i can change the distribution list.   Is there some sort of space limit ?

From my first original post:

Also for multiple email addresses you just set MailDoc.sendto to an array of variants each of which will receive the message.

Dim recip(25) as variant
recip(0) = "emailaddress1"
recip(1) = "emailaddress2" e.t.c

maildoc.sendto = recip

Is it possible to have those addresses come from a table ? Could you possibly who me how that would work with the code you have above.  

If the additional names did not work, then you did not set them correctly.  Notes can easily accommodate far more names, and I have similar code that sends to dozens of addresses at once.

Try putting in MsgBox(strEmail) to see if the string actually contains what you think it does.  Also, look out for bad data in the database.  A NUL character can cause truncation.

Maybe i didn't explain well.   Is it possible to still use a table or a query to feed the adresses ? Also if not, then can you show me how to ad the code above to make it work for 2 or 3 addresses.  Sorry i'm not that good at putting it all together, I have not been coding VBA  for a long time.  

Umm, the code I gave you DOES pull the addresses fro a query. The rs.Open is basically a query, and the "while not rs.EOF / MoveNext" are retrieving the query results. Each row in turn has the Email column pulled and added to the strEmail variable, and I use the strEmail variable to assign the final recipient list.

rs.Open "tblAttendeeList", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 strEmail = ""
 Do While Not rs.EOF
   If Not IsNull(rs!Email) Then
     strEmail = strEmail & rs!Email & ";"
   End If
   rs.MoveNext
 Loop

And it does work, but only a few emails make it through ?   Is there a limit on how many you can pass ? Because it seems to me like it's an access issue.   Lotus notes can fit plenty of emails.   I seems like there is a limit on how many letters can be used ? All it is, is a list of emails in a table, they are all the same, and yet only a few make it through.  

Look at the saved message in your sent folder. What does it say in the To: field? Is everyone there?

No, like a said above, only the first couple of names appear.  That's my problem, i can't load the complete list of emails into the send to ?

Perhaps it isn't Notes, perhaps it is Access.  At the bottom of the script, add:

Msgbox strEmail

Let's see what Access as placed in the source list before deciding that it is a problem getting the list over to Notes.

Ok got an update.   Sorry took a while.  So basiclly the Msgbox strEmail returns the entire list.  But when it goes to Lotus notes only about half the list of 30 names makes it ?   So it has to be some sort of Lotus Notes  issue.  Access gets it right, but then only half the list transfers ?

It looks like only 255 charicters make it into Lotus notes.   Is this access or lotus notes causing the limit ?

Im getting an Expected: End of Statement error from this line  Set sendTo = msg.replaceItemValue "SendTo", "" 

I deleted the below
'start off with dummy entry


Set sendTo = msg.replaceItemValue "SendTo", ""

Never mind i figured it out it was missing ( )  thank you for your help,  i appreciate you taking your time to help me out.