VBA/Lotus Notes Send to multiple recipients 1

[RP1America]

I have a sub that generates a Lotus Notes email via VBA. Everything works great when sending to only one recipient. How can I send to multiple recipients?

Sub EmailCashDetailReportLotusNotes()

    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("", "names.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
    [highlight #FCE94F]Call MailDoc.REPLACEITEMVALUE("SendTo", "Insert recipient here")[/highlight]
'Set subject of the mail
    Call MailDoc.REPLACEITEMVALUE("Subject", "Insert Subject here")
'Create and set the Body content of the mail
    Set Body = MailDoc.CREATERICHTEXTITEM("Body")
    Call Body.APPENDTEXT("Insert Body text here")
'Example to create an attachment (optional)
    Call Body.ADDNEWLINE(2)
    Call Body.EMBEDOBJECT(1454, "", "I:\RP\attachmentfile.xls", "Attachment")
'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

 

[PHV]

Did you try a semi-colon separated list of recipents ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RP1America]

Just tried. Only the first recipient received the email. The second email address cut off "@domain.com".

I tried both with and without a space between the semi-colon and second address. Same result.

 

[HughLerwill]

Have you tried a String array(0 and 1 based alternatives) or Variant array (of strings) as the 2nd argument?

 

[Hoaokapohaku]

It sucks, but when I dealt with this problem in the past I found only two ways around the problem. One is you must code to send an email separately for each recipient. There is no way to name multiple recipients with a single line of code. The other way is to create what Outlook calls a "Contact Group." I don't know what its called in Lotus Notes but I'm sure there must be something similar. Include all your intended recipients within that group. Then use that single group name within your VBA code and your email program will recognize the group name and send to the intended multiple recipients.

 

[Hoaokapohaku]

If Lotus Notes can't do what I said above, or if for other reasons you want to handle everything within VBA, you could create a Collection for the email addresses, populate the collection with the intended email addresses, and then write code to loop through the collection and send individually addressed emails to each address in the collection.

Decent instructions and illustration of creating a collection, adding items, and using the collection can be found at

http://www.functionx.com/vbaexcel/objects/Lesson6.htm.

 

[Hoaokapohaku]

You could do about the same thing using an array instead of a collection.

 

[HughLerwill]

... and/ or try a different syntax for for the sendto like in ...
MailDoc.Sendto Array("1stRecip@some.com", "2ndRecip@somother.com")

 

[HughLerwill]

Sorry I think that should be;
MailDoc.Sendto = Array("1stRecip@some.com", "2ndRecip@somother.com")

 

[strongm]

>There is no way to name multiple recipients with a single line of code

Of course you can. As HughLerwill describes, you just need to use a variant array.

 

[RP1America]

Tried HughLerwill's suggestion, but am receiving an error:

"Object doesn't support this property or method"

Sub EmailCashDetailReportLotusNotes()

    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("", "names.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
    [highlight #FCE94F]MailDoc.Sendto = Array("1stRecip@some.com", "2ndRecip@somother.com")[/highlight]
'Set subject of the mail
    Call MailDoc.REPLACEITEMVALUE("Subject", "Insert Subject here")
'Create and set the Body content of the mail
    Set Body = MailDoc.CREATERICHTEXTITEM("Body")
    Call Body.APPENDTEXT("Insert Body text here")
'Example to create an attachment (optional)
    Call Body.ADDNEWLINE(2)
    Call Body.EMBEDOBJECT(1454, "", "I:\RP\attachmentfile.xls", "Attachment")
'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

 

[HughLerwill]

How about;

Call MailDoc.REPLACEITEMVALUE("SendTo", Array("1stRecip@some.com", "2ndRecip@somother.com"))

 

[strongm]

Ah - can depend on version of Notes

Try commenting out

MailDoc.Sendto = Array("1stRecip@some.com", "2ndRecip@somother.com")

and change

Call MailDoc.SEND(False)

to

Call MailDoc.SEND(False, Array("1stRecip@some.com", "2ndRecip@somother.com"))

Another alternative:

Dim Recips() as Variant
Recips = Array("1stRecip@some.com", "2ndRecip@somother.com")

Call MailDoc.SEND(False, Recips)

 

[Hoaokapohaku]

Have any of the methods proposed here worked other than the one I mentioned? I'd like to know, because my conclusion that it can't be done with a single line of code was based on trying to solve the same problem trying the same ideas being suggested here until I wanted to pull out my hair. Seems like such a major oversight not to have this capability. If any of these methods are working, I'd really like to know because that means I did it wrong when I tried before and it would be very helpful to me to have something that works.

Hmmm...a thought just came to mind...the OP is using Lotus Notes, so this probably wouldn't help him. But for those using Outlook, I wonder if VBA can be used to create an Outlook contact group containing the recipients, then send the email to the group, then delete the Outlook group, or not delete the group if that is what is desired?

 

[Hoaokapohaku]

To answer my own question above, yes you can code an Outlook Contact List (distribution list) from within Excel VBA. Here is some code from MS to do it. I know this is not what the OP needs since he is using Lotus Notes, but it might be helpful for others using Outlook, or someone else familiar with Lotus Notes may be able to revise the code for use with that application.

Sub AddNewMembers()
Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myDistList As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myDistList = myOlApp.CreateItem(olDistributionListItem)
Set myTempItem = myOlApp.CreateItem(olMailItem)
Set myRecipients = myTempItem.Recipients
myDistList.DLName = _
InputBox("Enter the name of the new distribution list")
myRecipients.Add myNameSpace.CurrentUser.Name
myRecipients.Add "Dan Wilson"
myRecipients.ResolveAll
myDistList.AddMembers myRecipients
myDistList.Save
myDistList.Display
End Sub

 

[strongm]

>Have any of the methods proposed here worked other than the one I mentioned?

I happily used a variant of the suggestions provided here for some years.

 

[RP1America]

All, I have tried HughLerwill's suggestion of:

Call MailDoc.REPLACEITEMVALUE("SendTo", Array("1stRecip@some.com", "2ndRecip@somother.com"))

And it worked perfectly!

Thank you HughLerwill and to all who contributed!

 

[HughLerwill]

Good. Just for the record do you know which version of Notes you are talking to?

 

[RP1America]

8.5

 

[tbl]

Just as a matter of interest, a simple text string of recipients, separated by a COMMA works has always worked well for me.

Richard