Excel spreadsheet holiday to Outlook Shared Calendar and update spreadsheet

[tigerdel]

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

[Ken Puls]

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.)

[tigerdel]

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

[Ken Puls]

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)

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.

[tigerdel]

Thanks again but I simply cannot get this to work now matter how hard I try

Knowledge gap me thinks

[tigerdel]

Hi

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

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

[Ken Puls]

Hi there,

Good work! I've adjusted the code a bit and annotated it so that you can follow which pieces are doing what:

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

[tigerdel]

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

[tigerdel]

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