Results 1 to 10 of 10

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

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0

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

    Dear All,

    I hope you are all doing well,

    Kindly help me and provide me the code that will send the activesheet to someone email located in the same activesheet in the cell E9.
    Please find the attached file wich inclue more details.

    Your kind efforts appreciated in advance.

    MSabra
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Instead of sending it as an attachment, are you fine with sending it as a part of the email body? The format and all will look exactly the same.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    Instead of sending it as an attachment, are you fine with sending it as a part of the email body? The format and all will look exactly the same.



    Dear Administrator,

    Thank you for your kind reply.

    Yes please, I want it to be as apart of the email body.

    Thank you

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Code:
    Sub Mail_Active_Sheet()
    
        Application.DisplayAlerts = False
        'path, name and extension for tempfile - change to suit
        c00 = "G:\My documents\TempFile." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
        c01 = ThisWorkbook.FileFormat
        'Copy active sheet
        ThisWorkbook.ActiveSheet.Copy
        'Save attachment
        With ActiveWorkbook
            .SaveAs c00, c01
            .Close False
        End With
        'Create mail
        With CreateObject("Outlook.Application").createitem(0)
            .to = ActiveSheet.Range("E9")
            .Subject = ActiveSheet.Range("B2")
            .attachments.Add c00
            .display '.Send
        End With
        'Delete tempfile
        Kill c00
        Application.DisplayAlerts = True
    
    End Sub

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Thank you very much Bakerman!

    Kindly be informed that I don't have Outlook application in my Computer! we are using another application called "Lotus Notes" if yo know it.
    In this case how shall I modify the code to suit our mail application.

    Thank you in advance

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Untested...

    Code:
    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 = "G:\My documents\TempFile." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
        c01 = ThisWorkbook.FileFormat
        'Copy active sheet
        ThisWorkbook.ActiveSheet.Copy
        'Save attachment
        With ActiveWorkbook
            .SaveAs c00, c01
            .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
    Last edited by Excel Fox; 07-01-2013 at 01:07 PM. Reason: bakerman's suggestion of killing included
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    @ Sam
    The attachment could also be removed afterwords by using Kill (if necessary)
    Don't forget to release the objects from memory.

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Right. Acknowledged that bakerman :D
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    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 :

    Code:
    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
    Last edited by Excel Fox; 07-02-2013 at 07:48 PM. Reason: Code Tags

  10. #10
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Was the workbook you ran the code from already saved on HD or was it a new one ?
    Because for me it works fine. Try this one
    Code:
    Sub tst()
    c00 = "D:\TempFile." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
    c01 = ThisWorkbook.FileFormat
    MsgBox c00 & "," & c01
    End Sub
    In the messagebox you should see D:\Tempfile. then extension (xls,xlsm) then number (56,52)

Similar Threads

  1. Replies: 2
    Last Post: 07-02-2013, 02:36 PM
  2. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  3. Replies: 4
    Last Post: 03-01-2013, 10:48 PM
  4. Outlook Send Mail With Multiple Recipient and CC
    By noobtron in forum Excel Help
    Replies: 2
    Last Post: 10-31-2012, 07:14 PM
  5. Send Lotus Notes Email Using VBA
    By ramakrishnan in forum Excel Help
    Replies: 1
    Last Post: 09-08-2011, 09:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •