UserForm sending a range of cells through Lotus Notes RRS feed

  • Question

  • Hi,

    I want to add a command button in my userForm to send email through excel 2010 via lotus notes.

    when the command button is clicked, i want the range from the active row to be selected.

    Email address should be picked up from Active Row from column J

    The body of the email should be:


    Please see listed below,

    Then text from the Active Row( coulmn A, column E and Column G) should be pasted.

    I have found some VBA codes to do the same but unable to amend as per my requirement, I have attached a copy of the example worksheet.

    Please advise....

        Sub Send_Unformatted_Rangedata()
       Dim noSession As Object, noDatabase As Object, noDocument As Object
       Dim vaRecipient As Variant
       Dim rnBody As Range
       Dim Data As DataObject
       Const stSubject As String = "Send only an unformatted range of data."
       Const stMsg As String = "Data as part of the e-mail's body."
       Const stPrompt As String = "Please select the range:"
       'This is one technique to send an e-mail to many recipients but for larger
       'number of recipients it's more convenient to read the recipient-list from
       'a range in the workbook.
       vaRecipient = VBA.Array("", "")
       On Error Resume Next
       Set rnBody = Application.InputBox(Prompt:=stPrompt, _
             Default:=Selection.Address, Type:=8)
       'The user canceled the operation.
       If rnBody Is Nothing Then Exit Sub
       On Error GoTo 0
       '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
       'Copy the selected range into memory.
       'Retrieve the data from then copied range.
       Set Data = New DataObject
       'Add data to the mainproperties of the e-mail's document.
       With noDocument
          .Form = "Memo"
          .SendTo = vaRecipient
          .Subject = stSubject
          'Retrieve the data from the clipboard.
          .Body = Data.GetText & " " & stMsg
          .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"
       'Empty the clipboard.
       Application.CutCopyMode = False
       MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    End Sub
    Saturday, February 23, 2013 7:48 PM

All replies

  • It is not clear how you want to arrange the data from the cells in column A, E and G of the row in which the selection is located however, the following will concatentate the data into a string and get the email address from cell J

    Dim strData as String, vaRecipient as String
    strData = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 0) & " " & ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 4) & " " & ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6)
    vaRecipient = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 9)

    To get the complete text of the message, replace

    .Body = Data.GetText & " " & stMsg


    .Body = "Hi," & vbCr & Please see listed below, & vbCr & strData

    I don't believe that you need these bits of the code that you included in your message

      'Copy the selected range into memory.

       'Retrieve the data from then copied range.
       Set Data = New DataObject

    Finally, is the following what you really want for the subject of the email

    "Send only an unformatted range of data."

    If not, change it to what you want it to be.

    I cannot comment on the validity of the Lotus Notes part of the code.

    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

    Sunday, February 24, 2013 9:37 AM