Looping through combo box

Hi all,

I am responsible for creating and distributing commission statements monthly to our sales force. Currently I have a Combo Box(Form Control) that has a list of every employee. I click on the individual names, the Cell Link puts the number that the name is in the list, which in turn pulls up their employee ID which I have vlookups pulling in the data for that particular employee. I then click a button which uses the code below and a statement is generated and emailed.

The list of employees has grown to great to individually click each name and I need to create a loop to select the name, run the code below, and then move on to the next name. Thanks for your time!

Code:

---

Sub WSRA_Button5_Click()
 Dim PDFFileName As String
    Dim PSFilename As String
    Dim LogFilename As String
    PSFilename = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V3")
    PDFFileName = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V4")
    LogFilename = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V7")
    'Print the Excel range to the pdf file
    ActiveSheet.Range("E5", "R72").PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, prtofilename:=PSFilename
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFilename, PDFFileName, ""
    Kill PSFilename 'delete postscript temporary file
    Kill LogFilename 'delete log file
Dim strrecipient As String
Dim strstatement As String
strstatement = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V4")
strrecipient = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V5")
Dim pathname As String
Dim dname As String
pathname = strstatement 'defines attachment
dname = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V2") 'defines date for subject
Dim objol As Outlook.Application
Dim objmail As Outlook.MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(Outlook.olMailItem)
    With objmail
        .To = strrecipient 'enter in here the email address
        .Subject = dname
        .Body = "Please find a copy of your " & Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V2") & " attached." & _
            vbCrLf & vbCrLf & "If you have any questions, please contact Ryan Daltry or your manager. A statement with greater details on your Annual Accelerator will come later this week. " & vbCrLf
        .NoAging = True
        .Attachments.Add pathname 'adds attachment to email
        .display
    End With
    Set objmail = Nothing
    Set objol = Nothing
    SendKeys "%{s}", True 'send the email without prompts
End Sub

---

Personally, I would loop thru the actual Employee list on the Worksheet and not even use a Combo Box.

ComboBox loop code

Code:

---

Dim i As Long
For i = 0 to Combox.ListCount - 1
  EmployeeName = ComboBox.List(i)
Next

---

To return the value of the second column in a multicolumn Control

Code:

---

For i = 0 to Combox.ListCount - 1
  EmployeeNumber = ComboBox.List(i, 2)
Next

---