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. EgYou 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.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 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)ArtikSub 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