Results 1 to 9 of 9

Thread: Excel spreadsheet holiday to Outlook Shared Calendar and update spreadsheet

  1. #1
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0

    Excel spreadsheet holiday to Outlook Shared Calendar and update spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All

    I have an Absence Request Form for Employees to Request Leave and Notify Siccknees that I want to do the following:

    In the Absence Tab
    I would like it too:
    1. Read Name, Start date, end date
    2. Check to ensure Request not already in Shared calendar
    3. Open Outlook meeting request
    4. Enter Name, start date, end date and message and send to manager
    5. Enter dates in appropriate sheet for that month as T [Tentative]
    I am guessing I need to Send details to Outlook and somehow save the Absence Form so the Manager can then click the Approved by Manager button [to be password protected] and then update the appropriate sheet as below
    1. When approved by the manager, convert the T to H [if full day vacation] or HD [if half day vacation] or LTH [if Half Lieu Day taken] or LT [if Full Lieu Day Taken] or S [if sickness]

    I have anonymised the entire file so you can play

    Any ideas
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Wow... okay, you're building an entire system here. I'm going to start by telling you that, in order to avoid overwhelming the experts here with the amount of work, you should be breaking this down into bite sized pieces.

    I assume that the calendar file is stored on the network somewhere, and that everyone has access to it? (Probably most importantly the managers?)

    Given that, I'm assuming that you want to:

    • Have the user complete the Absence request form
      • They fill in the name, start and end dates, type, message, etc..
      • They click "Send to Outlook"

    • Clicking "Send to Outlook":
      • The calendar is marked with the "T" (are you sure you don't want a T-Full, T-Half, T-...?)
      • An email is sent to the manager telling them to review it


    When the manager gets the email:


    • They open the file and review the request
      • Are you going to build a form to review all "Tentative" vacation for that manager?
      • They will have the option to "Approve" or "Decline"

    • If they click "Approve":
      • The worksheet gets updated to show the correct absence type. (Would probably be easier with a T-type as listed above)
      • And Outlook appointment is created to mark the approved leave in the user's calendar? Manager's calendar? Both?

    • If they click "Decline":
      • The tentative items are removed from the calendar
      • And email is sent to the user telling them it's been declined and why


    That's a bit more than you originally had, but I'm curious if that's the general gist of what you're after. If it differs at all, please let us know where.

    For reference, I'll try and help where I can, but I'm most likely going to give you pointers, not write the code for you. Sending email via Outlook is easy. I don't recall calendar appointments being too tough.

    It's the marking/unmarking of the vacation days that concern me. Your data structure isn't ideal for that... it would be better suited if you had one list of vacation days rather than 12 tables, then you could use PivotTables to update. It would take some work to convert it, but would probably be easier to maintain in the long run. Regardless though, it's up to you and certainly CAN be done in your current format. There's just a few tricks that I think you'd want to observe to keep it maintainable as you add new employees. (Like creating named ranges over your 12 tables on the Holiday Master page.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0
    Hi Ken
    Thank you so much for your response andyour Full System description is very true.
    I had a long discussion about thistoday and I think that we should dumb down considerably.
    So what I have said we should do is tojust code the Absence form so that it creates a meeting request in an OutlookShared Calendar [Called - Absence] and sends the request to the manager for approval.
    Adding the absence to the WorkBook would only take themanager a few seconds
    As you rightly say coding to send anemail is fairly simply [even for me as fairly inept coder] I managed to get thesystem to send emails for other forms but I wouldn’t know where to start withmaking it create a meeting request – I also haven’t ever been able to get mysend mail code to be able to add the signature either
    I think that would the better route –what do you think??
    Could you help me out with how to codeso it does the above??
    Derek

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    That will make it easier to start, for sure.

    I started to cobble something together, but I don't have time to test this myself right now, but maybe it will get you started. It very well contain bugs that you'll have to work through.

    I do know that working with Shared Calendars is a bit different than working with your own, and I believe the overall gist of this is correct, although I don't have the true calendar name. Maybe with a bit of trial and error and web searches you can figure out the syntax for it.

    You will need to set a reference to the Microsoft Outlook xx.0 Object Library to use this (in Tools-->References)

    Code:
    Sub AddToOutlookCalendar(sCalendar As String, dtStart As Date, lLength, sAppointmentName)
     
        Dim olApp As Outlook.Application
        Dim olApt As Outlook.AppointmentItem
        Dim objFolder As Outlook.Folder
     
        Set olApp = New Outlook.Application
        Set objFolder = OpenMAPIFolder("Path to the folder you want to add the appointment in")
        Set olApt = objFolder.Items.Add
     
        With olApt
            .Start = dtStart
            .End = .Start + lLength 'TimeValue("0:45:00")
            .Subject = sAppointmentName
            .Location = "Anywhere"
            .Body = ""
            .BusyStatus = olBusy
            .ReminderSet = False
            .Save
        End With
     
        Set olApt = Nothing
        Set olApp = Nothing
     
    End Sub
    Sub test()
        Dim lHours
        
        'Set length of appointment here
        lHours = 1
        Call AddToOutlookCalendar("Absence", Now, lHours / 24, "Test")
        
    End Sub
    If you're looking for an easy to use piece for email coding, which does support plain text signatures, I have a class module that you can import (http://www.excelguru.ca/content.php?...il-Integration)

    I'll follow up with you later on this, but let me know how you make out.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0
    Thanks again but I simply cannot get this to work now matter how hard I try

    Knowledge gap me thinks
    Last edited by tigerdel; 2012-08-28 at 11:42 AM. Reason: Errors

  6. #6
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0

    Excel spreadsheet holiday to Outlook Shared Calendar and update spreadsheet

    Hi

    I managed to get this working but need help with where it gets info from

    Code:
    Sub app()
    Dim olApp As Outlook.Application
    Dim a As Outlook.AppointmentItem
    Dim wk As Worksheet
    Set a =Outlook.CreateItem(olAppointmentItem)
    a.Start = "24/04/2010 14:00"'from sheet "absence cell B7
    a.End = "4/2/10 17:00"       'from sheet"absence cell B9
    a.Duration = "24"            'all day
    a.Body = "meeting withme"   'fromsheet "absence cell B13
    a.Location = "myOffice"
    a.ReminderMinutesBeforeStart ="5"  'Noreminder
    a.ReminderSet = True                'False??
    a.RequiredAttendees = "Badawy,Edgar" 'Know how to do this but also need optionalattendee
    a.MeetingStatus = olMeeting
    a.ResponseRequested = 1
    a.Subject = ""                  'needname in Absence b5 plus Absence Request
    CallAddToOutlookCalendar("Absence", Now, lHours / 24, "Test") 'this calls an error
    If False Then
    a.Close olSave
    a.Send
    Else
    a.Save
    a.Display
    End If
    Set a = Nothing
    Set wk = Nothing
    End Sub

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Hi there,

    Good work! I've adjusted the code a bit and annotated it so that you can follow which pieces are doing what:
    Code:
    Sub app()
    Dim olApp As Outlook.Application
    Dim a As Outlook.AppointmentItem
    Dim wk As Worksheet
        'Trigger error handling
        On Error GoTo ExitPoint
        
        'Set worksheet variable to allow shorting coding lines
        Set wk = Worksheets("Absence")
        
        'Create appointment
        Set a = Outlook.CreateItem(olAppointmentItem)
        
        'Set appointment defaults
        With a
            .Start = wk.Range("B7") 'from sheet "absence cell B7
            .End = wk.Range("B9")       'from sheet"absence cell B9
            '.Duration = "24"            'all day <-- are you sure you need this? You set start/end times
            .Body = wk.Range("B13")   'fromsheet "absence cell B13
            .Location = "myOffice"
            .ReminderMinutesBeforeStart = "5" 'Noreminder
            .ReminderSet = False        'Set true or false
            .RequiredAttendees = "Badawy,Edgar" 'Know how to do this but also need optionalattendee
            .OptionalAttendees = "Anyone else want to come?"
            .MeetingStatus = olMeeting
            .ResponseRequested = 1
            .Subject = wk.Range("B5") & " Absence Request"  'needname in Absence b5 plus Absence Request
        End With
    
    ExitPoint:
        'Save the appointment
        a.Save
        
        'Check if any errors were encountered in creation
        If Err.Number <> 0 Then
            'Error encountered. Ask user to complete manually
            MsgBox "Sorry, I encountered an error. Please complete the rest of the request manually"
            a.Display
        Else
            'No errors, so close and send the request
            .Close olSave
            a.Send
        End If
        'Release variables
        Set a = Nothing
        Set wk = Nothing
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0
    Wow thanks for the compliment I amlearning more about this VBA coding
    I now have a problem with dates
    I have a flight request sheet thathas 3 date fields but only want to call my calendar when for 2 of those fields.
    I thought I had it but oh no despitedespite calling only 2 fields it still shows the calendar for all the fieldswith a date format grrrr
    I have attached the sheet and theVBA and would so happy if you could take a look and let me know where I havegone wrong??
    My problem is B28 is DOB field andthis is set to go to 1991 and I don’t know how to change it to go back to 1950and still choose today’s date as a default
    Sample.xlsm

  9. #9
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0
    Man my typing is rubbish tonight –should have read
    Wow thanks for the compliment I am learning more about this VBA coding by the day thanks to you
    I now have a problem with dates
    I have a flight request sheet that has 3 date fields but only want to call my calendar when for 2 of those fields.
    I thought I had it but oh no despite calling only 2 fields it still shows the calendar for all the fields with adate format grrrr
    I have attached the sheet and the VBA and would be so happy if you could take a look and let me know where I have gone wrong??
    My problem is B28 is DOB field and this is set to go back only to 1991 and I don’t know how to change it to go back to 1950 and still choose today’s date as a default
    Last edited by tigerdel; 2012-09-01 at 09:20 PM.

Posting Permissions

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