Results 1 to 10 of 10

Change Excel VBA To Include Input Box For Emailing using Lotus Notes

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-20-2008
    Posts
    162

    Change Excel VBA To Include Input Box For Emailing using Lotus Notes

    I currently use the following code - with no problems at all, and it emails to the recipients listed ok. But i would like a pop up box with a selection of managers - which allow the user to select just one, so that the manager gets a copy too.

    Is this at all possible......

    Sub SendWithLotus()
    
        Dim noSession As Object, noDatabase As Object, noDocument As Object
        Dim obAttachment As Object, EmbedObject As Object
        Dim stSubject As Variant, stAttachment As String
        Dim vaRecipient(2) As Variant, vaMsg As Variant
    
        Const EMBED_ATTACHMENT As Long = 1454
    
       'Get the name of the recipient from the user.
        Do
        vaRecipient(0) = "[email protected]"
        vaRecipient(1) = "[email protected]"
        vaRecipient(2) = "[email protected]"
        Loop While vaRecipient(2) = ""
    
        'If the user has canceled the operation.
        If vaRecipient(2) = False Then Exit Sub
    
        'Get the message from the user.
        Do
        vaMsg = ActiveWorkbook.Name
        Loop While vaMsg = ""
    
        'If the user has canceled the operation.
        If vaMsg = False Then Exit Sub
    
        'Add the subject to the outgoing e-mail which also can be retrieved from the users
        'in a similar way as above.
    
        stSubject = ActiveWorkbook.Name
        
        'Retrieve the path and filename of the active workbook.
        stAttachment = ActiveWorkbook.FullName
    
        '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(EMBED_ATTACHMENT, "", stAttachment)
    
        'Add values to the created e-mail main properties.
        With noDocument
            .Form = "Memo"
            .SendTo = vaRecipient
            .Subject = stSubject
            .Body = vaMsg
            .SaveMessageOnSend = True
        End With
    
        'Send the e-mail.
        With noDocument
            .PostedDate = Now()
            .Send 0, vaRecipient
        End With
    
        'Release objects from the memory.
        Set EmbedObject = Nothing
        Set obAttachment = Nothing
        Set noDocument = Nothing
        Set noDatabase = Nothing
        Set noSession = Nothing
    
        'Activate Excel for the user.
        AppActivate "Microsoft Excel"
        MsgBox "The e-mail has successfully been created and is ready for you to replicate.", vbInformation
    End Sub
    Regards JonesZoid
    Last edited by JonesZoid; 03-04-2010 at 09:33 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1