How to send mail to multipie recipients from lotus using excel macro
I am using following code right now for sending mail from lotus using excel macro for single client its working fine
Sub SendLotusNotesMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session1 As Object
Dim EmbedObj1 As Object
Dim recep As Variant
Dim ccRecipient As Variant
ReDim recep(15)
ReDim ccRecipient(10)
Dim A As String
Dim B As String
Dim C As String
Dim D As String
Dim E As String
Dim y As Integer
Dim x As Integer
y = 2
x = Range("a65536").End(xlUp).Row
MsgBox (x)
Do While x > 1
'Open and locate current LOTUS NOTES User
'On Error GoTo testing
Set Session1 = CreateObject("Notes.NotesSession")
UserName = Session1.UserName
MailDbName = "Mail\yourmaildatabase.nsf"
Set Maildb = Session1.GETDATABASE("yourmailserver", MailDbName)
'Example: Set Maildb = Session1.GETDATABASE("maildb/server/company", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
MsgBox (UserName)
' Create New Mail and Address Title Handlers
'attachment1 = Application.GetOpenFilename(, , "Please select file to send")
D = "D" & y
E = "E" & y
attachment1 = ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("Main").Range(D) & "_" & Format(Date, "mm-dd-yyyy") & ".xls"
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.form = "Memo"
A = "A" & y
B = "B" & y
C = "C" & y
recep(0) = ThisWorkbook.Worksheets("Main").Range(A)
'ccRecipient(0) = ThisWorkbook.Worksheets("Main").Range(A)
subj = ThisWorkbook.Worksheets("Main").Range(B)
mailbody = ThisWorkbook.Worksheets("Main").Range(C)
MailDoc.sendto = recep
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = subj
MailDoc.Body = mailbody
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", attachment1, "")
Call MailDoc.Send(False)
'MsgBox ("Hello world!")
y = y + 1
x = x - 1
Loop
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session1 = Nothing
Set EmbedObj1 = Nothing
End Sub
now i want to send mail to multiple recipients.Here All recipients are in single cell.
Overall structre of excel sheel is as follows-
first cell contains recipiets email address.
second cell contains subject of email.
third cell contains body of email.
fourth cell contains reprort name ( without any extension )
Now when i run macro mail goes rowwise to recipients.But In case of multiple recipients mail only goes to first recipient.So tell me what i do for here for sending reports to multiple clients.
thanks in advance
regards
Shailendra Gupta



