Please Help in VBA code

[Abouya]

I'm using this code to send an email the excel file to a specific email by clicking on a button. Is there a way to show a list of emails and user can choose which emails to send to instead of sending it to one predefined email in the VBA code?

Thank you so much.

here is the VBA module code:

 Sub Module6()
 Dim Msg As String, Ans As Variant
 Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

Msg = "Clicking 'Yes' will send Your Project(s) to "person XXX"
     
        Ans = MsgBox(Msg, vbYesNo)
    Select Case Ans
Case vbYes
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name & " " & Format(Now, "dd-mmm-yy")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "xxxxxxxxx@outlook.com"
        .CC = ""
        .BCC = ""
        .Subject = "Capital Project(s)"
        .Body = "Please Find the file attached."
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

 

[Ranman256]

in a form , create a list box. This listbox has all persons and their emails.
user picks a person in the list.
you could also pick a file to send as attachment. (tho uncomment the .attachment.add code)

.to = lstBox

 

[Abouya]

in a form , create a list box. This listbox has all persons and their emails.
user picks a person in the list.
you could also pick a file to send as attachment. (tho uncomment the .attachment.add code)

.to = lstBox

Thank you for responding. is Listbox the name of tyhe table containing the persons and their emails? could you please be more specific how to do this. thanks a lot again. I really appreciate.