I have a single column listbox (lstEmployees) populated with names in a "Last, First" format. I would like to have a command button loop through this list and save an email for each name in the listbox with a unique PDF attached to each. I need each name to be selected individually and then the email created, as the selection of the name in the form populates a textbox (txtEmployeeName) with the selected name as “First_Last”. The PDF to be attached to the email follows the “First_Last” naming convention, which is why it is imperative that each name needs to be selected individually, allowing the textbox to populate with the modified naming convention that the code will look to in order to attach the unique PDF for that employee.
The code I have currently is listed below. The problem I am encountering is that it will generate the correct number of emails (the number of names in the list, 50), however, it will create 50 emails for the same person – the first person appearing in the listbox. What am I doing wrong that isn’t allowing the code to move through each name in the list?
The code I have currently is listed below. The problem I am encountering is that it will generate the correct number of emails (the number of names in the list, 50), however, it will create 50 emails for the same person – the first person appearing in the listbox. What am I doing wrong that isn’t allowing the code to move through each name in the list?
Code:
[COLOR=black][FONT=Verdana]Private Sub cmdEmail_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim i As Long[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]i = 0[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Do[/FONT][/COLOR]
[FONT=Verdana][COLOR=black] Dim myOlApp As Outlook.Application[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Dim objMailMessage As MailItem[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Dim stPath, stExt, EmailTo, unqPDF As String[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Set myOlApp = Outlook.Application[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Set objMailMessage = myOlApp.CreateItemFromTemplate("C:\Path\Template.oft")[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] stPath = "C:\Path\”[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] stExt = "_Sales_Report.pdf"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] EmailTo = Me.lstEmployees[/COLOR][/FONT]
[COLOR=black][FONT=Verdana] unqPDF = Me.txtEmployeeName[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] With objMailMessage[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] .To = EmailTo[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] .Attachments.Add stPath & unqPDF & stExt[/FONT][/COLOR]
[FONT=Verdana][COLOR=black] .Save[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]i = i + 1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Loop Until i = Me.lstEmployees.ListCount[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
