Consulting

Results 1 to 12 of 12

Thread: Advice on how to automate process

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location

    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
    Last edited by Jacob Hilderbrand; 10-23-2019 at 08:03 PM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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. 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 expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    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 ?

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I am not sure that I would like to follow your advice
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Preliminary draft in attachment.

    Artik
    Last edited by Jacob Hilderbrand; 10-24-2019 at 10:48 AM.

  6. #6
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    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

  7. #7
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    File provided by Atik
    Attached Files Attached Files

  8. #8
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    new additions / changes
    Attached Files Attached Files

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Integrate all the data in the seperate workbooks.
    Use a pivottable to show summaries per dentist.
    Eventually you only need 2 worksheets for this.

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    This is what I mean, and more (mailing, etc)
    Attached Files Attached Files

  12. #12
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    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 . small steps small steps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •