Dear Team,
I am trying to use below code to send mails from Excel 2010 to Lotus note ,
I am getting below error , please help to solve the issue
___*
__________________________________________________________________________________________________________________________________
*
* "-----------------------*
*Microsoft Excel*
*---------------------------*
*Error number: 7000*
*
*
*Description: Notes error: No names found to send mail to.*
*---------------------------*
*OK *
*---------------------------*
*
______________________________________________________________________________________________________________________________________
*
*
*
*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 = "Group MIS"
'The message in the bodies of the outgoing e-mails.
Const vaMsg As Variant = "Group MIS Report as per agreement." & vbCrLf & _
"Kind regards," & vbCrLf & _
"Prashant Pawle| CORP-MIS"
'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 & ".xlsx"
'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("AA1:AA" & 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
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
