Advice on how to automate process

Hello all . I am trying to automate a process and need some advice . I have attached a file as it come to me from an external source. What I would like to do is send each worksheet to the respective dentist via email so that they can review and make comments. There will be multiple staff that will need to send these worksheets to the individual dentists. How can I go about automating the process . I will need to have something that I can search the email address for each dentist and then attach the worksheet for that dentist . Is it possible to create a search User form to select the dentist name and email --> link to their work sheet and attach the work sheet to an email --> then email with a generic text in the body of the email . I know that you can send an active sheet but the email search is the problem . I did find this https://chandoo.org/forum/threads/at...5/#post-203445 . The query seems to be matching my issue. I have added a list tab with the dentist names and email addresses , the email opens but not sure how to match the name from the list tab to the worksheet tab so that the worAttachment 25322k sheet can be attached to the email Attachment 25321

Advice on how to automate process

First do it manually, meticulously recording each and every step... What you looked at, what you saw, where you looked next, what you copied, what you opened, where you pasted, what you typed, Etc, Etc, Etc, and with great detail.

Do this for one Dentist.

Go home forget everything you did. Tomorrow, Don't think, just follow those steps meticulously for another dentist and correct them as needed. Yeah, unless you are not human, there will be errors.:banghead: Best if you can Shanghai a mail clerk or dishwasher to follow your notes and perform the task while you watch.

The next day, repeat the steps with the strangest, most different and difficult sheet in the book and record those steps with notes why they are needed.

Been there, done that many times. You won't believe how much time this will save you and us. It is NOT BS busy work, it IS necessary. And, there will still be errors, but many, many fewer.

I am not sure that I would like to follow your advice . Looking to minimise the head banging. . I have found this link https://www.mrexcel.com/forum/excel-...heet-name.html which gets me to the finding the sheet

HTML Code:

---

Sub FindWS()    Dim strWSName As String
   
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
   
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(s.Name, strWSName) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
   
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True End Function

---

it seems to work but is case sensitive . It opens the work sheet as per selection how do i get the "active " sheet to be attached to and email as an attachment . i have seen code that does this not sure where to place the code to do it . Can I create a user form with the above code and have an email button to send as per selection? What is the easiest ?

Quote:

---

I am not sure that I would like to follow your advice

---

Preliminary draft in attachment.

Artik

Hi Artik
I am not at work today so will try in next few days ( back in the office in 4 days time ) so if you dont hear immediately its not due to lack of interest . Its exactly the concept I was looking for.
many thanks for very assistance

File provided by Atik

new additions / changes

Integrate all the data in the seperate workbooks.
Use a pivottable to show summaries per dentist.
Eventually you only need 2 worksheets for this.

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

Code:

---

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)

Code:

---

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

This is what I mean, and more (mailing, etc)

apologies for late reply ...I do appreciate your assistance and advice
I have gone old school and created a macro that sends an email with the attachment based on the active sheet
Seems to be working well, If only I could get the User to stop printing the sheet and return via email :banghead: . small steps small steps