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

10 pts.
Lotus Notes
Microsoft Excel
Microsoft 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 = "Mailyourmaildatabase.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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Make sense?
Mike K

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • IT Knowledge Exchange Community Update for 11/18/08 - ITKE Community Blog
    [...] >> How to send mail to multiple recipients from Lotus using Excel macros [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: