Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    email a schedule

    Hi,
    New to this and need some help. I have created a schedule on a spreadsheet for my employees. What I would like to do is if an employee has given an email address (in column c), then to email their schedule only to them (columns d:x). I do not want to include their total hours (column aa) in this email. I would like the subject of the email to be pulled from r1 and r2:u2. As part of the body, I would like to have it read Hi (pull from column b for their name), your schedule is as follows:. Finally if its possible but not a must id would like for it to include with their schedule d5:v5 and d4:v4 (but this is not a must have). Any help with this would be greatly appreciated. Thanks!
    june 16 - june 22.xlsx

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    ronny,

    The attached workbook will do what you want. You must have Outlook running prior to running the code. I have adapted the code to the Stewards sheet only but it could easily be added to the Attendants sheet as well.

    Select an employee's name in column B. The code is designed not to run if you mistakenly select other cells or there is no email address present. With an employee selected click the "Email selected employee" button. The employee's schedule will be extracted into an email with the subject line and body of email set as you requested.

    HTH,
    Maud

    schedules.png

    Code:
    Public Sub EmpSchedule()
    Application.ScreenUpdating = False
    'DECLARE AND SET VARIABLES
        Dim OutApp As Object
        Dim OutMail As Object
        Dim Schedule As Object
        Dim strBody As String
        Dim strSubject As String
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        Row = ActiveCell.Row
    '----------------------------------------------------------
    'CHECK IF EMPLOYEE'S NAME IS SELECTED AND EMAIL ADDRESS PRESENT
    If Not Intersect(ActiveCell, Range("B6:B17", "B24:B31")) Is Nothing Then
        If Cells(Row, 3) = "" Then
            MsgBox "The selected employee does not have an email listed."
            Exit Sub
        End If
    '----------------------------------------------------------
    'CREATE TEMPORARY COPY OF EMPLOYEE SCHEDULE
        Range(Cells(300, 4), Cells(303, 24)).ClearContents
        Range(Cells(4, 4), Cells(5, 24)).Select
        Selection.Copy
        Range("D300").Select
        ActiveSheet.Paste
        Range(Cells(Row, 4), Cells(Row + 1, 24)).Select
        Selection.Copy
        Range("D302").Select
        ActiveSheet.Paste
    '----------------------------------------------------------
    'BUILD EMAIL COMPONENTS
        strBody = "Hi " & Cells(Row, 2) & ", your schedule is as follows:" & vbNewLine & vbNewLine
        strSubject = Range("R1") & " " & Range("R2") & " " & Range("T2") & " " & Range("U2")
        With OutMail
            .To = Cells(Row, 3).Value
            .Subject = strSubject
            .Body = strBody
            .Display
            SendKeys "^+{END}", True
            SendKeys "{END}", True
    '----------------------------------------------------------
    'PASTE INTO OUTLOOK MAIL
            Set Schedule = OutApp.ActiveInspector.WordEditor
            Range("D300:X303").Select
            Selection.Copy
            Schedule.Application.Selection.Paste
            SendKeys "^+{HOME}", True
            SendKeys "{HOME}", True
        End With
        On Error GoTo 0
    '----------------------------------------------------------
    'CLEANUP
        Set OutMail = Nothing
        Set OutApp = Nothing
        Set ScheduleT = Nothing
        Cells(Row, 2).Select
    End If
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jun 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    maud,
    this is great!!! is there anyway to have it automatically scan and send it without having to do it individually?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Absolutely! The code can be made to loop through each employee initiated by one button click. Before I complete that change and add the code to the second sheet, are there any changes or additional employees that you want to add?

  5. #5
    New Lounger
    Join Date
    Jun 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud, this is the current employee schedule. All employees are currently working for me. In the future I'm sure it will change. In the past i would just delete an employees row if the no longer worked for me. Would that affect the coding?

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I will make it so it will not. Any changes to the design (rows, columns, etc)? Just leave the lines blank but do not add or remove rows
    Last edited by Maudibe; 2014-06-28 at 15:29.

  7. #7
    New Lounger
    Join Date
    Jun 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No. Everything else is set as is.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Ronny,

    I have completed the workbook (both sheets). Here are some of the features that were added:

    1. Email management form where employee names are updated and email addresses cam be added, edited, or deleted
    2. Checkboxes to indicate which employees will have their scheduled emailed
    2. Checkboxes are automatically enabled/disabled depending on whether the employee has an email on file
    3. Checkboxes can be selected individually or by a Select All checkbox
    4. Auto update all the dates by entering just the start date
    5. Bulk emailing of schedules
    6. customized salutation in body of email
    7. Print button that will automatically print only the schedule region of the sheet
    8. Non-password protected sheets upon opening the workbook to protect formulas and formatting
    9. Additional lines on the Stewards sheet for future additional employees.

    Let me know if you need any help in the future.
    Maud

    schedules2.png

    schedules5.png
    Attached Files Attached Files
    Last edited by Maudibe; 2014-06-28 at 14:43.

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You couldn't pay for that sort of service! Maud gets my vote for "above and beyond".

    cheers, Paul

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Wow, a great compliment when it comes from you! thanks Paul

  11. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts

  12. #12
    New Lounger
    Join Date
    Jun 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ted,
    this is amazing. above and beyond what I had expected. and I agree with paul on this. not sure how this forum works, but if there is a way to rate you or something like that please let me know. the spread sheet is great, though im running into one problem. I believe its a small one. ive tried to edit the emails and print and im running into the message below. I assume its a setting error for the computer but please let me know what I have to do.

    error 1.docx

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Ronny,

    Thank you so much. It was a fun project to collaborate on.

    You are running 64 bit Office. Here is the adjusted file to accommodate this. Remember, have outlook running prior to sending the emails.

    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2014-07-01 at 19:40.

Posting Permissions

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