Question

How to send mail to multipie recipients from lotus using excel macro

Lotus Notes, Microsoft Excel, Excel macros

Hi ,

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

Overall you have, the secret is in this line of code:

recep(0) = ThisWorkbook.Worksheets("Main").Range(A)

This is resetting the 1st item in an array, you need a multi-value array:

Redim recep(0 to 100) as string

Then loop thru the cells in Excel that contain the recipient addresses and increment the array items

arrayCount = 0

'this is generic code you need to change appropriately
do until cellrange is nothing
recep(arrayCount) = ThisWorkbook.Worksheets("Main").Range(cellRangeLetter)
arrayCount = arrayCount + 1
loop

Make sense?
Mike K
mkinder@acadiasolutions.com