Multiple Recepients - Lotus Notes

[Phendrena]

I have the following code that sends an email from an excel userform.
I would like to include multiple recepients.

As you can see from the code the SendTo, CC & BCC fields are completed based on what subject line the user chooses. In several cases the SendTo, CC & BCC fields are all used.

How can add more recepients?

Private Sub cmdSendEmail_Click()
 
' Check to see if Who Called has been completed
If Trim(Me.cboWhoCalled.Value) = "" Then
Me.cboWhoCalled.SetFocus
MsgBox "Please complete the Who Called field!"
Exit Sub
End If
 
' Check to see if Nature Of Call has been completed
If Trim(Me.cboReason.Value) = "" Then
Me.cboReason.SetFocus
MsgBox "Please complete the Nature Of Call field!"
Exit Sub
End If
 
' Check to see if Subject has been completed
If Trim(Me.cboSubject.Value) = "" Then
Me.cboSubject.SetFocus
MsgBox "Please choose a Subject for the email!"
Exit Sub
End If
 
Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object
Dim SendTo As String
Dim cc As String
Dim bcc As String
 
If Me.cboSubject.Value = "7+1 - Amendment Required" Then
SendTo = "Louise Cragg"
cc = "Cheryl Lumsden"
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "7+1 - Unable To Locate Customer" Then
SendTo = "Jody Lee McGee"
cc = "Louise Cragg"
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Agency Amendment" Then
SendTo = "Agency Mailbox"
cc = ""
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Contingency Email Address Change" Then
SendTo = "Agency Mailbox"
cc = ""
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Test" Then
SendTo = "David Bandey"
cc = ""
bcc = ""
End If
EMailSendTo = SendTo '' Required - Send to address
EMailCCTo = cc '' Optional
EMailBCCTo = bcc '' Optional
EMailSubject = Me.cboSubject.Value & " : " & Me.txtTwo.Value
 
''Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")
 
''Establish Connection to Mail File
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
 
''Open Mail
objNotesMailFile.OPENMAIL
 
''Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT
 
''Create 'Subject Field'
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EMailSubject)
 
''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)
 
''Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)
 
''Create 'Blind Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo", EMailBCCTo)
 
''Create 'Body' of memo
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")
With objNotesField
.AppendText "Date : " & Me.txtDate.Value
.ADDNEWLINE 1
.AppendText "Customer Manager : " & Me.txtCM.Value
.ADDNEWLINE 1
.AppendText "Dealer Number : " & Me.txtDealerNo.Value
.ADDNEWLINE 1
.AppendText "Dealer Name : " & Me.txtDealerName.Value
.ADDNEWLINE 1
.AppendText "Scheme : " & Me.cboScheme.Value
.ADDNEWLINE 1
.AppendText Me.lblOne.Caption & " : " & Me.txtOne.Value
.ADDNEWLINE 1
.AppendText Me.lblTwo.Caption & " : " & Me.txtTwo.Value
.ADDNEWLINE 1
.AppendText Me.lblThree.Caption & " : " & Me.txtThree.Value
.ADDNEWLINE 1
.AppendText Me.lblFour.Caption & " : " & Me.txtFour.Value
.ADDNEWLINE 1
.AppendText Me.lblFive.Caption & " : " & Me.txtFive.Value
.ADDNEWLINE 1
.AppendText Me.lblSix.Caption & " : " & Me.txtSix.Value
.ADDNEWLINE 1
.AppendText "Additional Comments : " & Me.txtComments.Value
.ADDNEWLINE 1
End With
 
''Send the e-mail
objNotesDocument.send (0)
 
''Release storage
Set objNotesSession = Nothing
Set bjNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing
 
''Set return code
 
sendmail = True
 
Call cmdUpdate_Click
 
End Sub

Thanks,

 

[VoG]

This may help http://www.excelkb.com/article.aspx?id=10221&cNode=1X5M7A

 

[Andrew Poulsom]

Have you tried passing a variant array, eg?

EMailSendTo = Array("Louise Cragg", "Jody Lee McGee", "Agency Mailbox")
objNotesDocument.SendTo = EMailSendTo

 

[Phendrena]

Have you tried passing a variant array, eg?

EMailSendTo = Array("Louise Cragg", "Jody Lee McGee", "Agency Mailbox")
objNotesDocument.SendTo = EMailSendTo

Interesting idea. How would I use the above in my IF statements

If Me.cboSubject.Value = "7+1 - Amendment Required" Then
    SendTo = "Louise Cragg"
    cc = "Cheryl Lumsden"
    bcc = "Dealer SupportTeam"
End If

Thanks,

 

[Andrew Poulsom]

Sorry, I don't understand. You have only one recipient there.

 

[Phendrena]

Sorry, I don't understand. You have only one recipient there.

The code is setup to send the email to different people depending on the subject :

If Me.cboSubject.Value = "7+1 - Amendment Required" Then
SendTo = "Louise Cragg"
cc = "Cheryl Lumsden"
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "7+1 - Unable To Locate Customer" Then
SendTo = "Jody Lee McGee"
cc = "Louise Cragg"
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Agency Amendment" Then
SendTo = "Agency Mailbox"
cc = ""
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Contingency Email Address Change" Then
SendTo = "Agency Mailbox"
cc = ""
bcc = "Dealer SupportTeam"
End If
 
If Me.cboSubject.Value = "Test" Then
SendTo = "David Bandey"
cc = ""
bcc = ""
End If

While not all the fields are used in each case i would also like it to send a copy of the email to user (set to 'Me.txtCM.Value = Application.UserName').

The array suggestion that you post would solve this problem as i could add several people to the SentTo field instead of having to use the CC & BCC fields. I could then use the BCC field to email a copy to the user.

 

[Andrew Poulsom]

Like this?

If Me.cboSubject.Value = "7+1 - Amendment Required" Then
    SendTo = Array("Louise Cragg", "Cheryl Lumsden", "Dealer SupportTeam")
End If

You will need to declare SendTo and EMailSendTo as Variant.

 

[Phendrena]

Like this?

If Me.cboSubject.Value = "7+1 - Amendment Required" Then
    SendTo = Array("Louise Cragg", "Cheryl Lumsden", "Dealer SupportTeam")
End If

You will need to declare SendTo and EMailSendTo as Variant.

Works very nicely.
Many Thanks.