Lotus Script to send mail from excel data

HCL Domino Domino Forum
1

My Query is thatin the excel file has mail address like this

abc.mail@mail.com, xys.mail@mail.com,def.mail@mail.com

but system sends mail to first mail id and 2nd mail id picks half only

How to resolve this issue

When I run script in debug mode the “sendto” field has full address, I am not sure why the same picked while sending the mail.

Kindly help experts.

Sub Initialize

On Error GoTo ErrHandler

Dim session As New NotesSession

dim ws As New NotesUIWorkspace

Dim db As NotesDatabase

Dim mailDoc As NotesDocument

Dim fileName As Variant

Dim filePath As String

Dim EmailAddr As String 

Dim Emailaddr2 As String

Dim vendorname As String

Dim Form16FileName As String

Dim xlApp As Variant

Dim xlSheet As Variant

Dim i As Integer

Dim object As NotesEmbeddedObject

Dim curdoc As NotesuiDocument 

Set curdoc = ws.Currentdocument 	

Set db = session.CurrentDatabase

filePath = "C:\test\"

filename = "c:\test\"

fileName = ws.OpenFileDialog(False, "Select Excel File for Mailing Form 16", "*.xls|*.xlsx")



Set xlApp=CreateObject("Excel.Application")

xlApp.Application.WorkBooks.open fileName(0)

Set xlSheet = xlApp.Application.WorkBooks(1).WorkSheets(1)



i = 2



While Not(xlSheet.cells(i,1).value="")

	EmailAddr = CStr(xlSheet.Cells(i,1).value)

	'EmailAddr2 = CStr(xlSheet.Cells(i,2).value)

	Form16FileName = CStr(xlSheet.Cells(i,2).value)

	vendorname = CStr(xlSheet.Cells(i,3).value)

	Set mailDoc = db.Createdocument()

	mailDoc.form = "Memo"

'	mailDoc.Subject = "Your Form 16 for current year"

	maildoc.subject = curdoc.Fieldgettext("msubject")+ "-" +vendorname 

	Dim mailRTI As New NotesRichTextItem(mailDoc,"Body")

	Call mailRTI.Appendtext("Dear Sir,")

	Call mailRTI.Addnewline(2)

	Call mailRTI.Appendtext("Please find attached herewith ")

	Call mailRTI.Addnewline(2)

	Call mailRTI.Appendtext("Kindly acknowledge receipt of this mail")

	Call mailRTI.Addnewline(4)

	Call mailRTI.Appendtext("Thanks and Regards.")

	Call mailRTI.Addnewline(2)

	Call mailRTI.Appendtext("Team")

	Call mailRTI.Addnewline(1)

	Call mailRTI.Appendtext("abc India Pvt. Ltd")

	Call mailRTI.Addnewline(1)

	Set object = mailRTI.EmbedObject( EMBED_ATTACHMENT, "", filePath + Form16FileName)

	mailDoc.SendTo = EmailAddr

	'maildoc.sendto = emailaddr2

	maildoc.principal = "A/c Dept."

	Call mailDoc.Send(False)

	'		Call curdoc.fieldsettext("DispText",emailaddr) 

	i = i + 1

Wend

MsgBox "Total number of mails sent "+CStr(i-2)

xlApp.Application.WorkBooks.Close

Exit Sub

ErrHandler:

xlApp.Application.WorkBooks.Close

MsgBox "The Error in Initialize of Form 16 Mailer: " & Error & ", error no.:" & Err & " at line no. " & Erl & "."

Exit sub

End Sub

2

Subject: Send to is expecting an array

You’re providing it with a single string,.

So you’ll need to create an array from your cells value. Take a look at split to get you started.