Send worksheet to multiple recipients

[akb110]

I have to send multiple sheets to different recipients through lotus notes. through some reference got the following macro for the same. Since i am new to macro. unable to write the fully.

Request some to help in writing the same completely.

regards


<TABLE style="BORDER-COLLAPSE: collapse" borderColor=#111111 cellSpacing=0 cellPadding=4 width="100%" border=0><TBODY><TR><TD width=15 colSpan=2>Code</TD></TR><TR><TD width=15> </TD><TD>Option Explicit <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sub Send_Sheets_Notes_Email() <o></o>
<o></o>
'Notes parameter for attaching the Excel files. <o></o>
Const EMBED_ATTACHMENT As Long = 1454 <o></o>
<o></o>
'A folder to temporarily store the created Excel files in. <o></o>
Const stPath As String = "c:\Attachments" <o></o>
<o></o>
'The subject for the outgoing e-mails. <o></o>
Const stSubject As String = "Weekly report" <o></o>
<o></o>
'The message in the bodies of the outgoing e-mails. <o></o>
Const vaMsg As Variant = "The weekly report as per agreement." & vbCrLf & _ <o></o>
"Kind regards," & vbCrLf & _ <o></o>
"Dennis" <o></o>
<o></o>
'Variable that holds the list of recipients for each worksheet. <o></o>
Dim vaRecipients As Variant <o></o>
<o></o>
'Variable which holds each worksheet's name. <o></o>
Dim stFileName As String <o></o>
<o></o>
'Variables for Notes. <o></o>
Dim noSession As Object <o></o>
Dim noDatabase As Object <o></o>
Dim noDocument As Object <o></o>
Dim noEmbedObject As Object <o></o>
Dim noAttachment As Object <o></o>
Dim stAttachment As String <o></o>
<o></o>
'Variables for Excel. <o></o>
Dim wbBook As Workbook <o></o>
Dim wsSheet As Worksheet <o></o>
Dim lnLastRow As Long <o></o>
<o></o>
On Error GoTo Error_Handling <o></o>
<o></o>
Application.ScreenUpdating = False <o></o>
<o></o>
Set wbBook = ThisWorkbook <o></o>
<o></o>
'Loop through the collection of worksheets in the workbook. <o></o>
For Each wsSheet In wbBook.Worksheets <o></o>
With wsSheet <o></o>
'Copy the worksheet to a new workbook. <o></o>
.Copy <o></o>
'Retrieve the worksheet's name. <o></o>
stFileName = .Name <o></o>
End With <o></o>
<o></o>
'Create the full path and name of the workbook. <o></o>
stAttachment = stPath & "\" & stFileName & ".xls" <o></o>
<o></o>
'Save and close the temporarily workbook. <o></o>
With ActiveWorkbook <o></o>
.SaveAs stAttachment <o></o>
.Close <o></o>
End With <o></o>
<o></o>
'Retrieve the list of recipients. <o></o>
With wsSheet <o></o>
lnLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row <o></o>
vaRecipients = .Range("A1:A" & lnLastRow).Value <o></o>
End With <o></o>
<o></o>
'Instantiate the Lotus Notes COM's Objects. <o></o>
Set noSession = CreateObject("Notes.NotesSession") <o></o>
Set noDatabase = noSession.GETDATABASE("", "") <o></o>
<o></o>
'If Lotus Notes is not open then open the mail-part of it. <o></o>
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL <o></o>
<o></o>
'Create the e-mail and add the attachment. <o></o>
Set noDocument = noDatabase.CreateDocument <o></o>
Set noAttachment = noDocument.CreateRichTextItem("stAttachment") <o></o>
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment) <o></o>
<o></o>
'Add values to the created e-mail main properties. <o></o>
With noDocument <o></o>
.Form = "Memo" <o></o>
.SendTo = vaRecipients <o></o>
.Subject = stSubject <o></o>
.Body = vaMsg <o></o>
.SaveMessageOnSend = True <o></o>
.PostedDate = Now() <o></o>
.Send 0, vaRecipients <o></o>
End With <o></o>
'Delete the temporarily workbook. <o></o>
Kill stAttachment <o></o>
Next wsSheet <o></o>
<o></o>
MsgBox ("The e-mails have successfully been created and distributed."), vbInformation <o></o>
<o></o>
ExitSub: <o></o>
'Release objects from memory. <o></o>
Set noEmbedObject = Nothing <o></o>
Set noAttachment = Nothing <o></o>
Set noDocument = Nothing <o></o>
Set noDatabase = Nothing <o></o>
Set noSession = Nothing <o></o>
<o></o>
Exit Sub <o></o>
<o></o>
Error_Handling: <o></o>
MsgBox "Error number: " & Err.Number & vbNewLine & _ <o></o>
"Description: " & Err.Description, vbOKOnly <o></o>
Resume ExitSub <o></o>
End Sub <o></o>
</TD></TR></TBODY></TABLE>

 

[shyrath]

If I read the code correctly, then the recipients are defined in Column A of first sheet.

so in A1 you put first recipient in A2 second recipient etc. etc.

vaRecipients = .Range("A1:A" & lnLastRow).Value

 

[akb110]

Which mean that I have to put recipients in coloumn A in the first sheet of the file which contains data. how do i link which sheet need to be send to each recipient.

I need to do following activity.

Sheet recipient
Sheet 1 Mr.A
Sheet 2 Mr.B
Sheet 3 Mr.C


Also could you please write code fully for me with lines that are to be deleted, which I can copy in macro. Since I do not understand macro code.

Regards