Listbox2 value send email

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code: 
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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.
 
Upvote 0
Patriot2879 said:
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.
Click to expand...
Good morning hope someone can help me please? have a nice day all
 
Upvote 0
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.
 
Upvote 0
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.
VBA Code: 
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
 
Upvote 0
Patriot2879 said:
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.
VBA Code: 
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
Click to expand...
Hi i have also tried amending to the below but get a compile error on 'Dim OutApp As Outlook.Application' please can you help?
VBA Code: 
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
 
Upvote 0
Try the following...

VBA Code: 
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!
 
Upvote 0
Solution
Domenic said:
Try the following...

VBA Code: 
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!
Click to expand...
thank you for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA is centering the image instead of aligning it to the left as usual in New Email
Replies
1
Views
214
Micron
M
M
  • Solved
Send multiple columns/rows by e-mail
Replies
6
Views
331
MasterBash
M
countryfan_nt
  • Question Question
VBA SMTP Sending Outlook mail has no errors, but nothing being sent.
2
Replies
17
Views
763
countryfan_nt
countryfan_nt
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
321
Patriot2879
P
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
331
Micron
M

Forum statistics

Threads
1,224,812
Messages
6,181,088
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top