Advice on how to automate process

[mml]

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/attach-separate-worksheets-to-designated-email-addresses.34155/#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 wor25322k sheet can be attached to the email 25321

[SamT]

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.

[mml]

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-questions/58374-vba-find-sheet-name.html which gets me to the finding the sheet
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 ?

[SamT]

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

[Artik]

Preliminary draft in attachment.

Artik

[mml]

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

[mml]

File provided by Atik

[mml]

new additions / changes

[snb]

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

[Artik]

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

[snb]

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

[mml]

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