Send Mail Using Lotus Notes With Active Sheet As Attachment Using Lotus Notes

[mams323]

Thank you Administrator for your kind efforts.

I still have a problem with the file name or path! please have a look to the picture hereunder:

Fox1.JPG


for mor details about the problem I faced :

Sub SendLotusMail()

    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim c00 As String, c01
    
    'path, name and extension for tempfile - change to suit
    c00 = "D:\TempFile." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
    c01 = ThisWorkbook.FileFormat
    'Copy active sheet
    ThisWorkbook.ActiveSheet.Copy
    'Save attachment
    With ActiveWorkbook
        .SaveAs c00, c01   ‘ Here is the problem I faced        
.Close False
    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 the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(1454, "", c00)
     'Add values to the created e-mail main properties.
    With noDocument
        .Form = "Memo"
        .SendTo = ActiveSheet.Range("E9").Value
        .Subject = ActiveSheet.Range("B2").Value
        .Body = "Message Here"
        .SaveMessageOnSend = True
    End With
     'Send the e-mail.
    With noDocument
        .PostedDate = Now()
        .Send 0, ActiveSheet.Range("E9").Value
    End With
    Kill c00 'Delete the temporary file
    Exit Sub
   
ErrorMsg:
        
    If Err.Number = 7225 Then
        MsgBox "The file cannot be found in the location ", vbOKOnly, "Error"
    
    ElseIf Err.Number = 5 Then
        MsgBox "Please ensure that you are logged in to Lotus Notes", vbExclamation, "Lotus Notes"


    ElseIf Err.Number = 8965 Then
        MsgBox "User is not logged in or user ID file is in use elsewhere and cannot be modified."
    
    Else
        MsgBox Err.Number & Err.Description
    
    End If
    
End Sub

Please have a look to the attached file:
Payroll-1.xlsm