L
leerem
Hi All,
I'm having a problem with a piece of code that has been made
available but i cant get it to work. I'm trying to open Lotus Notes to email
updates to individuals.
Code as follows:-
Sub Send_Active_Sheet()
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "D:\Store Returns\data\Attachments"
Const stSubject As String = "Store Updates"
Const vaMsg As Variant = "The Individual Store Updates as requested." _
& vbCrLf & "Kind Regards"
Const vaCopyTo As Variant = "(e-mail address removed)"
Dim stFileName As String
Dim vaRecipients As Variant
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String
'On Error GoTo Error_handler
MsgBox " You have made at least 5 Updates" & vbNewLine & vbNewLine & _
"These Updates will now be emailed to Lee Remsbery", vbOKOnly & vbInformation
Application.ScreenUpdating = False
'Copy the Update sheet to a new temporarily sheet
ActiveWorkbook.Unprotect Password:=Passwd
Sheets("Update").Visible = True
Sheets("Update").Select
With ActiveSheet
.Copy
stFileName = .Range("A1").Value
End With
stFileName = "Attachment"
'Save and Close the Temporarily Workbook
With ActiveWorkbook
.SaveAs stPath & ".xls"
.Close
End With
' Create a list of recipients.
vaRecipients = VBA.Array("(e-mail address removed)")
' Instantiate the Lotus Notes COM's Objects
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
' If Lotus Notes is not open then open-mail part of it
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
' Create the e-mail and 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
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaRecipients
End With
'Delete the temporarily workbook
Kill stAttachment
'Release objects from memory
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
MsgBox "The e-mail has successfully been created and distrubuted",
vbInformation
Error_handler:
End Sub
My problem is when it gets to "Instantiate the Lotus Nots COM's Object' it
falls over with run-time error 429 'ActiveX component can't create object'
I'm aware that the object must find a key with an .lic extension, which may
be the problem. if so can anyone help with this issue or maybe this is not
the problem and I've totaly overlooked something. Does anyone have any ideas.
Please Help!!
I'm having a problem with a piece of code that has been made
available but i cant get it to work. I'm trying to open Lotus Notes to email
updates to individuals.
Code as follows:-
Sub Send_Active_Sheet()
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "D:\Store Returns\data\Attachments"
Const stSubject As String = "Store Updates"
Const vaMsg As Variant = "The Individual Store Updates as requested." _
& vbCrLf & "Kind Regards"
Const vaCopyTo As Variant = "(e-mail address removed)"
Dim stFileName As String
Dim vaRecipients As Variant
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String
'On Error GoTo Error_handler
MsgBox " You have made at least 5 Updates" & vbNewLine & vbNewLine & _
"These Updates will now be emailed to Lee Remsbery", vbOKOnly & vbInformation
Application.ScreenUpdating = False
'Copy the Update sheet to a new temporarily sheet
ActiveWorkbook.Unprotect Password:=Passwd
Sheets("Update").Visible = True
Sheets("Update").Select
With ActiveSheet
.Copy
stFileName = .Range("A1").Value
End With
stFileName = "Attachment"
'Save and Close the Temporarily Workbook
With ActiveWorkbook
.SaveAs stPath & ".xls"
.Close
End With
' Create a list of recipients.
vaRecipients = VBA.Array("(e-mail address removed)")
' Instantiate the Lotus Notes COM's Objects
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
' If Lotus Notes is not open then open-mail part of it
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
' Create the e-mail and 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
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaRecipients
End With
'Delete the temporarily workbook
Kill stAttachment
'Release objects from memory
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
MsgBox "The e-mail has successfully been created and distrubuted",
vbInformation
Error_handler:
End Sub
My problem is when it gets to "Instantiate the Lotus Nots COM's Object' it
falls over with run-time error 429 'ActiveX component can't create object'
I'm aware that the object must find a key with an .lic extension, which may
be the problem. if so can anyone help with this issue or maybe this is not
the problem and I've totaly overlooked something. Does anyone have any ideas.
Please Help!!