Email cell contents via Lotus Notes

[fenfool]

I'm trying to set up a spreadsheet for a co-worker that would let the user select an item from a drop-down, which would automatically send an email to a list of users in a separate worksheet. The email would be a review of the entries in the particular row in which they are working on, which is a maintenance log entry.

This is a maintenance log, Columns A-E are information about the problem; Column F is a drop-down with just the number 1. The idea is that when the user selects this, the spreadsheet sends an email to a list of address (in a worksheet named "Email") containing the information in Columns A-E for that row.

When the machine gets fixed, the maintenance worker selects a repair code in Column G, which populates the date in Column J; Column H is an open text description of the repair, Column I is a drop-down with the number 2, this would send an email with all the pertinent information, basically everything in that row from column A-J, except F & I aren't necessary.

I found a script that will send an email, but with a hard-coded message to a hard-coded email address. I'd like it to pull the information from the particular row it was selected from, and to an email list on a separate sheet so that the user can add/remove them as needed.

Any help would be greatly appreciated, this is certainly new terrain for me!

 

[CalcSux78]

Wow, it should be easy enough, but I'm not sure where your data is to give you a more detailed response.

I haven't used Lotus Notes in 15 years, but this might get you started. (I couldn't test, let us know how it works out)

Sub Send_LotusMail()
     'http://www.rondebruin.nl/win/s1/notes/notes5.htm
     'Note: Need to add "Microsoft Forms 2.0 Object Library
     'for sub to run properly.
    Dim noSession  As Object, noDatabase As Object, noDocument As Object
    Dim sRecipient As String
    Dim sSubject   As String
    'Dim vaRecipient As Variant     'use if sending to multiple addresses
    
    Dim sBody As String
    Dim Data As DataObject
    
    
    sBody = "This is the text for your email" & vbLf & _
            "Type message here."
    sSubject = "This is the subject line."
            
    
    sRecipient = "[EMAIL="none@none.net"]none@none.net[/EMAIL]"
    'If sending to multiple addresses use:
    'vaRecipient = VBA.Array("[EMAIL="first@xldennis.com"]first@xldennis.com[/EMAIL]", "[EMAIL="second@xldennis.com"]second@xldennis.com[/EMAIL]")
    
    
    'Instantiate Lotus Notes COM's objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")
    
    'Make sure Lotus Notes is open and available.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    
    'Create the document for the e-mail.
    Set noDocument = noDatabase.CreateDocument
    
    'Add data to the mainproperties of the e-mail's document.
    With noDocument
       .Form = "Memo"
       .SendTo = sRecipient
       .Subject = sSubject
       .Body = sBody
       .SaveMessageOnSend = True
    End With
    
    'Send the e-mail.
    With noDocument
       .PostedDate = Now()
       .Send 0, vaRecipient
    End With
    
    'Release objects from memory.
    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 distributed.", vbInformation
 
End Sub

This example is a chopped up version of the one from Ron deBruin's site, but you should find it easier to locate and change the recipient and message body. If you post more detail as to where the recipients are located within your workbook and how that is determined, we can submit a more detailed solution.

 

[fenfool]

Thanks for the response! The email recipients are all manually entered into a worksheet named "e-mail", in column B of that sheet (column is their actual names). Right now there are set to be around 3 or so recipients, maybe a few more, but not a ton.

 

[fenfool]

And I want to include the text from Column A-Column E from the row in which the email was initiated. So if I click the trigger in Row 2, it will send the text in A2:E2; if I click the trigger from column 10, it would be A10:E10, etc. There would be a second on in each row that would gather the values in Ax:Jx where x= the two from which it was triggered.

 

[fenfool]

Well, we are migrating to Outlook in three weeks, so I guess even this much isn't worth pursuing...any thoughts on doing this for Outlook? I do not yet have Outlook to be able to test it, so it's a bit tricky.

 

[CalcSux78]

I would start here: www.rondebruin.nl/win/section1.htm Ron has made a ton of examples that are easy enough to tailor around just about any scenario.

 

[fenfool]

Got it...Outlook was much easier, I was able to get it to work exactly the way they requested. Thanks for the help!