You need to set some rules by which the macro will identify doctors' sheets and skip others (e.g., List, Sheet1, or Staff Summary). I understand that it cannot be accepted as a condition that each of the sheets with the name of the doctor begins with "Dr". If there is always a summary sheet in the received file and you always add a List sheet and both sheets are always on the left, then the macro can fill the ComboBox1 list with the names of the sheets starting from the third in order. Eg
Private Sub UserForm_Initialize ()
'Dim wks As Worksheet
Dim and As Long
Me.cmbPersons.Style = fmStyleDropDownList
Me.labMail.Caption = vbNullString
For i = 3 To ThisWorkbook.Worksheets.Count
Me.cmbPersons.AddItem ThisWorkbook.Worksheets (i) .Name
Next and
End Sub
You can also search for characteristic words in all sheets, which only appear in doctors' sheets. You can change the names of non-doctors sheets before running the macro, for example by adding "XXX" before the name.
You have to set the rules that should always be followed.
Again, I pay attention to spaces in sheet names. I understand that in the case of doctors sheets, you did not add extra spaces, but the "list " sheet (with a space at the end) is probably your work. However, a certain minimum of hygiene should be maintained.
To make sure that the sheet names are correct in the mailing list, you can use a small macro that creates this list (only if there is no "List" sheet)
Sub CreateList()
Dim wks As Worksheet
Dim i As Long
On Error Resume Next
Set wks = ThisWorkbook.Worksheets("List")
On Error GoTo 0
If wks Is Nothing Then
Set wks = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
wks.Name = "List"
For i = 3 To ThisWorkbook.Worksheets.Count
wks.Cells(i - 1, "A").Value = ThisWorkbook.Worksheets(i).Name
Next i
wks.Range("A1:B1").Value = Array("Person", "Email")
End If
End Sub
Artik