Thread: 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......

Code:

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) = "mail1@email.com"
    vaRecipient(1) = "mail2@email.com"
    vaRecipient(2) = "mail3@email.com"
    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

is this being run in lotus notes?

ok no prob. ask a mod to move this to the programming sub forum. you'll get more response

go to the main page,
http://www.excelforum.com/index.php
scroll down to bottom
in" whats going on" "currently active users" look for a name in green font then pm with your thread url and ask them to move it.
errr my mistake its already in there!
but i'd advise you to change title to something like
"change excel vba to include input box for emailing using lotus notes"
or some such

hi JonesZoid,

Maybe the attached example will help...
I've used named ranges on the Ref Table sheet to create a list of possible email recipients & have then put them into a userform for the user to choose from.
Note, I may have included some un-necessary code (I copy & pasted from previous files) & it could probably do with some more appropriate error checking but hopefully it will provide a glimmer of hope ;-)

btw, I don't have Lotus Notes so I haven't tested the final product.

hth
Rob