VBA - Send Email

Lotus Notes: Send worksheets to several recipients

The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 7.01 and later.

Task:
I have been receiving some e-mails on how to send individual worksheets to several groups of recipients. In this example each worksheet contain the list of recipients to send the actual worksheet to.
The case also includes a general error handling.
VBA code :

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

Comments:
The above example shows how it's possible to create a flexible solution to send individual
worksheets to several groups of recipients.

Reference from: http://www.rondebruin.nl/win/s1/notes/notes8.htm 

Lotus Notes example pages :

Lotus Notes: Save and Remove attachments
Lotus Notes: Save attachments and delete the associated e-mails
Lotus Notes: Retrieve e-mailaddresses from a Notes database
Lotus Notes: Send a workbook as attachment to a created e-mail
Lotus Notes: Send data from a range as part of an e-mail's body
Lotus Notes: Send formatted data from a range or chart as part of an e-mail's body
Lotus Notes: Send the active sheet as an attachment
Lotus Notes: Send worksheets to several recipients