CODEWITHSUNDEEP
ms-accessvbams-access-2007lotus-notes
mat duwe
mat duwe

Reputation: 153

failed to send email to multiple recipient through microsoft access 2007, only sent to first recipients

i want to send email through microsoft access interface silently. user just need to select the recipients in the listbox and click a single button to send the email to multiple recipient. i dont want lotus-notes interface appear to the user. i have no problem in using those command to send email:

 DoCmd.SendObject objecttype:=acSendTable, _
 objectname:=strDocName, outputformat:=acFormatXLS, _
 To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg, EditMessage:=False

but those method is not what i'm looking for because it will appear in the screen while sending the email. although i have set EditMessage:=False.

i have a procedure to send the email from access through lotus notes in the background. the procedure runs fine with single recipient but it will only send email to only one recipient if i select multiple recipients. i think the problem have something to do with the recipients string. recipients string example : 

eg1 : [email protected], [email protected], [email protected]

eg2 : [email protected]; [email protected]; [email protected]

email will be sent to the first recipient only

here's the sub procedure : 

Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)

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)

Set Session = CreateObject("Notes.NotesSession")

'Get the sessions username and then calculate the mail file name
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 = False Then
   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 & vbCrLf & vbCrLf
MailDoc.PostedDate = Now()
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.send 0, Recipient

'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

End Sub

Calling the email procedure on button click event :

Private Sub cmdSendEmail_Click()

Dim EmailSubject As String, EmailAttachment As String, EmailRecipient As String, EmailBodyText As String, EmailSaveIt As Boolean

EmailSubject = Me.txtSubject.Value
EmailAttachment = Me.txtAttachment.Value
EmailRecipient = Me.txtSelected.Value
EmailBodyText = Me.txtMessage.Value
EmailSaveIt = True

Call SendNotesMail(EmailSubject, EmailAttachment, EmailRecipient, EmailBodyText, EmailSaveIt)

End Sub

and here's how i take the multiple recipient string from the listbox :

Private Sub lstEmail_Click()

On Error Resume Next
Dim varItem As Variant
Dim strList As String

With Me.lstEmail
  If .MultiSelect = 0 Then
     Me.txtSelected = .Value
  Else
     For Each varItem In .ItemsSelected
         strList = strList & .Column(0, varItem) & ", "
     Next varItem
     strList = Left$(strList, Len(strList) - 2) 'eliminate ", " at the end of recipient's string
     Me.txtSelected.Value = strList

   End If
 End With
End Sub

i really cannot use the docmd.sendObject method because it still appear on the screen although i set EditMessage:=False. i dont know if it works okay with other electronic mail but my lotus-notes 8.5 doesn't work with that docmd.sendObject for sending email on the background. any help or suggestion?

Upvotes: 0

Views: 1106

Answers (1)

Per Henrik Lausten
Per Henrik Lausten

Reputation: 21709

The recipents field needs to be an array (list). Can you use split to make it an array?

MailDoc.sendto = split(Recipient, ", ")

Upvotes: 3

Related Questions