PDA

View Full Version : Looping through combo box



txstate08
04-20-2018, 07:14 AM
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!

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

SamT
04-20-2018, 01:36 PM
Personally, I would loop thru the actual Employee list on the Worksheet and not even use a Combo Box.

ComboBox loop 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

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