Listbox2 value send email

[Patriot2879]

Hi good afternoon and happy new year to you all. i hope you can help me with the code below please, i have a listbox2 which has data in, when i select a row in the listbox and click the commandbutton i want the values in that row to be populated in the body of the email, and have the email open. can you help please.

Private Sub CommandButton11_Click()

    Dim oApp, oMail As Object, _
    tWB, cWB As Workbook, _
    FileName, FilePath As String, _
    MailBody As String, MailSubject As String, Disclaimer As String
    Dim first As Boolean
    first = True
    Application.ScreenUpdating = False
    Set oApp = CreateObject("Outlook.Application")

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
    If first = False Then
            mailid = mailid & ";" & ListBox1.ListIndex(i)
    Else
            mailid = ListBox1.ListIndex(i)
            first = False
    End If
End If
Next
    MailBody = YourMessageBodyStringVariable
    
    MailSubject = YourMessageSubjectStringVariable


If MailBody = vbNullString Then
        Beep
        MsgBox "Must Provide Body Text"
        GoTo quitpoint
End If
If MailSubject = vbNullString Then
        Beep
        MsgBox "Must Provide Subject Line Text"
        GoTo quitpoint
End If

    Set oMail = oApp.CreateItem(0)
    With oMail
        .to = ""
        .Subject = ""
        .Body = MailBody & Chr(10) & Chr(10) & Disclaimer
        .Send
    End With

quitpoint:
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub

 

[Patriot2879]

Hope someone can help me please?

 

[Patriot2879]

Hi all good afternoon I forgot to mention that Listbox1 is located in UserForm1 not sure that is needed in the code. I hope someone can help me please.

 

[Patriot2879]

Hi all good afternoon I forgot to mention that Listbox1 is located in UserForm1 not sure that is needed in the code. I hope someone can help me please.

Good morning hope someone can help me please? have a nice day all

 

[Patriot2879]

Good morning, hope someone can help me please? My Listbox is located in a user form and I also have a command button. Once a row is highlighted in the Listbox then the command is clicked, I want the row selected in the Listbox to be in the body of the email. Hope someone can help me please as I am really stuck on how to do this. Thank you for your time.

 

[Patriot2879]

Hi all good morning, i have also tried amending the code to the below and i still cant get it to work, please can you help.

Private Sub CommandButton11_Click()
 Dim i As Integer
 
    For i = 0 To Me.ListBox2.ListCount - 1
        With Me.ListBox2
            If Me.opt_All.Value = True Then
                Call Send_Email(.List(i, 1), .List(i, 2), .List(i, 3), .List(i, 4), .List(i, 5))
            Else
            
                If .Selected(i) Then
               Call Send_Email(.List(i, 1), .List(i, 2), .List(i, 3), .List(i, 4), .List(i, 5))
              
                End If
            End If
        End With
    Next i
    

End Sub
Sub Send_Email(to_ As String, subject_ As String, Mailbody_ As String, attachment1 As String, attachment2 As String)

Dim OutlookApp As Object
Dim msg As Object

Set OutlookApp = CreateObject("outlook.application")
Set msg = OutlookApp.createitem(0)

With msg
    .To = to_
    .Subject = subject_
    .body = Mailbody_
      
    If Me.opt_display.Value = True Then
        .Display
        
    Else
        .Send
    End If
    
End With

 
End Sub

 

[Patriot2879]

Hi all good morning, i have also tried amending the code to the below and i still cant get it to work, please can you help.

Private Sub CommandButton11_Click()
 Dim i As Integer
 
    For i = 0 To Me.ListBox2.ListCount - 1
        With Me.ListBox2
            If Me.opt_All.Value = True Then
                Call Send_Email(.List(i, 1), .List(i, 2), .List(i, 3), .List(i, 4), .List(i, 5))
            Else
          
                If .Selected(i) Then
               Call Send_Email(.List(i, 1), .List(i, 2), .List(i, 3), .List(i, 4), .List(i, 5))
            
                End If
            End If
        End With
    Next i
  

End Sub
Sub Send_Email(to_ As String, subject_ As String, Mailbody_ As String, attachment1 As String, attachment2 As String)

Dim OutlookApp As Object
Dim msg As Object

Set OutlookApp = CreateObject("outlook.application")
Set msg = OutlookApp.createitem(0)

With msg
    .To = to_
    .Subject = subject_
    .body = Mailbody_
    
    If Me.opt_display.Value = True Then
        .Display
      
    Else
        .Send
    End If
  
End With

 
End Sub

Hi i have also tried amending to the below but get a compile error on 'Dim OutApp As Outlook.Application' please can you help?

Private Sub CommandButton11_Click()
Dim subject As String, Body As String
Dim OutApp As Outlook.Application
  Dim OutMail As Outlook.MailItem
 
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If OutApp Is Nothing Then
    Set OutApp = CreateObject("Outlook.Application")
  End If
  On Error GoTo 0
 
  Set OutMail = OutApp.CreateItem(olMailItem)
 
  With OutMail
     
    .To = ""
    .subject = ""
    .Body = Me.UserForm1.ListBox2.Column(1) & ", " & Me.UserForm1.ListBox2.Column(2) & ", " & Me.UserForm1.ListBox2.Column(3) & ", " & Me.UserForm1.ListBox2.Column(4) & ", " & Me.UserForm1.ListBox2.Column(5)
    .Send
  End With
 
  Set OutMail = Nothing
  Set OutApp = Nothing
 
End Sub

 

[Domenic]

Try the following...

Private Sub CommandButton11_Click()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim body As String
    
    If Me.ListBox2.ListIndex = -1 Then
        MsgBox "Please make selection, and try again!", vbExclamation
        Exit Sub
    End If
    
    With Me.ListBox2
        body = VBA.Join(Application.Index(.List, .ListIndex + 1, 0), ", ")
    End With
 
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
      Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    Set OutMail = OutApp.CreateItem(0) 'olMailItem
    
    With OutMail
      .To = ""
      .subject = ""
      .body = body
      .Display '.Send
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
 
End Sub

Hope this helps!

 

[Patriot2879]

Try the following...

Private Sub CommandButton11_Click()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim body As String
   
    If Me.ListBox2.ListIndex = -1 Then
        MsgBox "Please make selection, and try again!", vbExclamation
        Exit Sub
    End If
   
    With Me.ListBox2
        body = VBA.Join(Application.Index(.List, .ListIndex + 1, 0), ", ")
    End With
 
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
      Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
   
    Set OutMail = OutApp.CreateItem(0) 'olMailItem
   
    With OutMail
      .To = ""
      .subject = ""
      .body = body
      .Display '.Send
    End With
   
    Set OutMail = Nothing
    Set OutApp = Nothing
 
End Sub

Hope this helps!

thank you for your help

 

[Domenic]

You're very welcome, cheers!