How to Send Multiple Staff Appointments From Excel To Google and Outlook Calendars: [Part 2]

hello this is Randy with Excel for freelancers and in this video is part two of an excel appointment scheduler in which we are going to be adding a multi staff scheduling we are going to be adding Google Calendar integration as well as Outlook calendar integration so stay tuned it's going to be a fantastic training all righty let's get started I'm really excited today because this is going to be part two of the excel appointment scheduler and if you'll remember and if you have not seen part one I suggest you do go ahead and go back and have a look at that video download that application because we're gonna take off from there I've made several additions to part two the first of which is adding the months so you can quickly select between months with some tabs up there I've added a drop-down list of staff I've added the ability to send to Outlook if you click list I've added the ability to send to Google Calendar and I've added a default duration as well along with the staff drop-down list I've added a staff sheet where we can add multiple staff there emails as well as assign a specific out with calendar and a specific zapier webhook for google calendar in which i'm going to get into alright let's go ahead and go over the on sheet changes now in order for us to track employees we also want to add them to our scheduling database and if you'll remember in part 1 we've created a sheet for each individual year of scheduling and this is holds our scheduling data so that's really important and we also have the ability now to schedule multiple staff and I did not want to create additional sheets for additional staff so what I decided to do was in cool those additional staff but what I've done is I've separated them and I've put the additional staff in the rows below as you can see here now our data extends way below okay and I'll show you how we did that first what I want to do is I want to know what staff has been selected so if we scroll over here right I've added the selected schedule row here which is 12 okay this is the selected schedule row so that's gonna help us out so when we select a specific row like as in 13 it's gonna know okay and next up we have the scheduled staff row and what I want to know is what staff which staff is selected okay and the staff here in this case Lisa which is row 5 as you can see here ok so if we go ahead and change that staff I want to know what row on the staff and now we change it to Fred which is Row 4 okay so if we look back and we see row 4 and it's a very simple all we've done is use the match formula ok and this match says basically what do we need to match M 2 and M 2 if we look that is the particular staff name and then we are going to match it with a named range staff ok our name range we've created a named range called staff and that covers all of the names here ok that covers all of our names here and I'll show you that named range let's go ahead and go into formulas name manager okay and then we'll scroll down to staff and you'll see in this case it's not just a straight named range I use the offset and why do we use this because I want to track only staff that are existing I don't want I don't want this drop-down list to include the entire column you know so we use the county basically what this does is it says offset count only those values with count a means only those cells with values in it so it's going to count those okay it's gonna say okay I want to use this row I don't want to count all of in this case we have three okay so if we go ahead and tap out and tap back in you see the dancing answer on only those three okay if we were to add just one right and then we go back into the name manager going back into staff and you tab over and you'll see now it includes that so basically what it's doing is it's counting and then it's updating the range based on this and why is this important well this is important because when Excel uses a drop-down list I don't want to include blanks I know I want this drop-down list to contain only the values okay and I don't want it to have blanks so that's very important so I love to use offset the offset and count a when using named ranges and there's other ways we can do it when those named ranges include formulas we can include others as well in which we will go over in future videos okay so that's how we got our staff table down list okay that is how we got our staff row and basically it's saying we're adding two right we're adding two why don't we add two because you'll see the staff start on Row three okay so if the first staff row number one is David right we don't want row one we want row three right so we need to add two to get to that row three so that's why we have there okay now I need to know also how do we we can't if you remember in the first video we put everything right basically all the scheduling from rows I think to in the schedule or up to row about 38 right 38 and then on row 40 we included the summary okay but what I don't want to do is I don't want it to conflict now if we have multiple staff we need to keep multiple schedules right because we have that we can't conflict them right so how did we do that well what we're doing here is we're gonna add rows 50 for each particular staff so if we select the first staff right we're not gonna add any roast that means we're not gonna add any roast so that means the first staff is going to be from two to four T the second staff is going to be starting at fifty right I just chose fifty because it's easier all the way to one hundred right and then the summary will end up being on road 90 okay the summaries on road 90 because so and then the third staff is going to be automatically sent information from 1 to 140 and then on row 140 we're going to include our summary so this is how we separate staff so that the data does not get mixed up okay and what this allows us to do is add multiple add multiple so if we add another so for example this tells us what road to start the schedule on so if we select the third staff right Lisa Lopez it's gonna tell us okay start it on row 100 but if we add another staff okay let's say Mary okay and we go ahead and go back to the schedule and then we select Mary from the list right it's going to be a multiple of 50 okay and so you'll see mary says okay start her schedule on the row 150 so that allows us and then we use this number in the VBA code to add and I'll show you how that works shortly okay because we've got a lot to cover in this video we've got Google Calendar Sync we got Outlook calendar sync we have multiple staff to cover and a little bit of a bonus on emailing schedule which I threw in there but didn't tell you okay so that's how that works so basically this formula how's that work it's basically saying we're gonna use that same match remember and then minus one because what I want to do the reason I want if it's the first staff this is going to return one right but I want zero for the first staff I want zero so zero times 50 okay it's zero that means we're not going to add any rose for the second staff we're gonna add 50 and so on so this formula helps us try which row the scheduling starts on which row first staff starts on Row 2 Oh messed up that formula bad Randy ok so let's go out of there and then and we here's the reason the reason why is we don't have remember you see that air ok let me show you that because we've selected the staff that no longer exists ok once we once we gather because we removed Mary already so let's go ahead and select on a staff alright good now we're good now we show the add Rosa 0 all right so what I was saying is staff one scheduling starts on Row two staff two on 50 ok or 51 ok so that's gonna help us separate the staff so that's why we have that in there ok also I have added what else that I add let's take a look oh we added the duration I've added a duration list because when I want to send to Outlook when I want to send to Google I want to know what the appointment duration is what is the appointment so this sets us a default of duration so it's very nice this allows us to send it to Google and Outlook with a specific duration so I've added this drop-down list from the previous one so those are the ads basically on the sheet I think we've covered everything and so that's gonna really help us there all right so let's go ahead and go into the code and have a look at how those changes affect the code itself alright if you have the developers you can get into vpa by clicking the Developer tab and then clicking Visual Basic you can also click alt f11 if you don't have the developers tab available just go ahead and click in the options under the file menu go ahead and go to customize ribbon and make sure the developers tab has been selected there ok alright let's go ahead all of 11 we go there we are and let's go ahead and take a look at the additions here ok one thing I've added a quick add is this code reset and you really will want to use this in your codes and this is gonna help us make the code a lot faster so basically when we run certain codes sometimes like for adding a lot of data every time we add it's going to calculate that data okay so what I'll do is I'll add reset code like let's go ahead and look at months so let's go ahead and add month load okay I think we added it um see where I've added it here okay loading the month right you see I've added stop code and reset code okay now what does that do okay what it does is that it turns off the calculation sends it to manual when we stop the code it doesn't enable events and it doesn't enable screen it so it turns all this off right and so that enables our code to run a lot faster and then we have to make absolutely sure that before we exit the sub that we reset the code that means we turn on events we set the calculation to automatic and we turn on screen updating so those are really important and I use this so you'll want to see this code reset you'll want to copy this and use this in your Mac that really helps speed things up but keep it in mind keep in mind that if you're running your code right and you've got you have the stop code and there's a break in here right let's just say there's a bug that codes still gonna be stopped so you know it's not going to calculate and strange things are gonna happen so when I sometimes do let's just say there's a bug in here right now let me show you let me just show you what let me create a bug okay okay now go ahead and run this macro okay okay oh let's go ahead and I want a creative actual bug here okay let's go ahead and I want to show you how that happens okay okay here's the bug okay so now code is stopped so let's go into Excel and take a look at this you'll see that under the formulas right calculations option it's set to manual okay and now we can't do anything so it's important that even during it if you're gonna use that when you run into that you want to make sure that not only do you reset the code but the but you want to go into here and go and just double click here and play that that's gonna re-enable so we're just gonna click run and that'll enable it so when we have a bug you'll have to make sure that you do reset the code if you're going to use that so be careful with that because when the stop code things in Excel don't calculate you know when you have this but it's a great great tool to have when when you want to use it so it's really important I wanted to go over that with you because you use that constantly you'll see that you've probably seen it in my past and you'll definitely see it in my future videos how we use stop code and reset code macros to help us and this helps us load the month a lot quicker so you'll see it's a lot of data but when we when we load the month you see it's very quick right see how quick that is you see how quick all that data loads and that's because we used to stop good let's go ahead and let's go ahead and comment that out just so we can see the difference of how the speed I don't have too much data but if you've got a lot of data it's got a big difference in the speed okay so I'm gonna comment those two out before we load the month let's take a look at the speed difference on that all right now we're gonna click January and you see it's lower and there it goes so it's if we have a lot of data it's gonna be really slow so so that's going to really help us moving forward to speed up our code and use that alright so I'm glad we got into that and let's go ahead and take a look at this code and you'll see on the add summary I've done something called I've added here add rows okay this is new okay it's called add rows and this is gonna help us say how many rows do we have to add how many rows do we have to add for this schedule and those add rows are based on the staff our add rows is this right here right here this value here staff number one we're not gonna add any rows staff number two we're gonna add 50 rows and that's gonna tell us where to put it on the schedule okay so add rows is b18 okay so you'll see on on moom we're adding the summary we're gonna have a dros be 18 okay and now what I've done here is when we are when we are programming add information to the sheets right we are adding those rows so instead of instead of the summary right remember the summaries always on row 40 but now it's 40 plus the add rows if it's staff 1 first up this add rows is gonna be 0 okay so this tells us if it's staff - it's gonna be 50 right staff 250 plus 40 which is 90 right so that means that summaries gonna go on row 90 on staff - and if you'll go into a database and we look at row 90 we see the summary is set here okay so that's for staff to write okay so that's gonna really help us and on staff 3 it's gonna be 140 ok so that's how we separated in the code and you'll see that throughout our code right so when we comes to loading month right we're going to pull the data from row 40 plus add rows when we're going to load the day also we're going to load the data from two plus the add rows okay so we're loading the information on m4t that's our day column from at from the range add rows to add or so basically I've added this in then that helps us differentiate which staff is which okay so those are the additions there all right so that's how that is how we handle multi staff I think we've covered that let's go ahead and get into how we are going to add to Outlook and how we're going to go add to Google Calendar okay so we have outlook sync max here and basically the idea with Outlook is we want to be able to create a separate calendar okay for each staff in Outlook and let's go ahead and go into outlook we'll take a look here and here is Outlook okay and I've got just one calendar here so what I want to do is I want to add one calendar for each staff and in our Excel we have three staff okay we have David Fred and Lisa so let's go ahead and add a calendar in Outlook for each of those persons okay so while we do is we're going to right click here click new calendar okay let's call it David okay and we want to make sure it's inside our calendar okay and click OK okay so now we have David we'll do that once more new calendar Lisa okay and a new calendar Fred okay and then we'll go ahead and select those three calendars so that we can view them okay so now we've got four calendars we don't want okay so now we have three counts we have David we have links that we have Fred on Tuesday at 30 so we can see all the calendars at the same time let's go ahead and and reduce that so we can see that in full now we've got those so that's good now we're set up now we have let's go back into Excel and now what I want to do is I need to get the calendar ID there's a specific ID number that has been assigned to each of those calendars it's like an internal ID number and we need to get that and what I did is I added some code here okay outlook see it's not so important that you want to know and there's really two things that you can get you can get a calendar ID and a calendar name okay we've assigned the calendar names and we've signed the calendar IDs you could theoretically you could theoretically use both okay in other words you could have separate column here for a calendar name but we really only need the ID and just in case I if you want to get that calendar name I've left that code right here this is it here's an EM folder name so basically what this what this code does first it says you select a specific row and if you have not selected a row in other words if C and the active cell Row is empty okay C active cell right so if it's empty right and we click this button it's gonna say please select a row containing the staff so C must contain the staff okay so that's how that this part of the code works basically what we're doing here is we're setting a specific Outlook we're calling up the Outlook calendar or setting it we're asking the user to pick a calendar with this code and then once the user chooses a calendar it takes that ID it takes that ID and it puts it in E okay Eve with the selected row so that's all we're doing we're taking the ID and we're putting in calendar E so let's go ahead and do that for each of those on David we're gonna click and I've assigned this macro to this button outlook sync Outlook calendar I've assigned it so let's go ahead add to calendar I've selected the row add to calendar and let's go ahead now it's this pop ups gonna say which calendar do you want to assign to David okay we've created it already so let's go ahead and click David okay and there's that long ID number okay and we can repeat the steps for our remaining two staff and Fred okay and then Lisa okay so it's a very simple process and now we've assigned to those and the only reason you might want to name is because if this these ID numbers don't really tell you if you a hundred percent sure that you got the right calendar so that's what I'm saying adding the calendar name in an additional column might be helpful okay for you so you can do that now we've now we've assigned a specific Outlook calendar for each staff so now when we schedule we're going to know when when we can do that right know when so now all the only other code is basically sent to Outlook okay okay so this code sends it directly to Outlook and the first thing is okay so now back on the scheduling sheet let's go back on the scheduling sheet okay this is our code I've only added a little bit to that okay so this these two rows are the only ones that I've added basically it says if p8 equals yes then send to Outlook if P nine value is yes then send to Google counters so these are the only two rows that I've added no let's go ahead and take a look what that means back in the scheduler we go here is our initial settings p8 send to Outlook yes or no p9 sent to Google Calendar yes or no okay so let's go ahead and click YES on this okay and let's go ahead and schedule something now we have Fred okay and here we can select Houska we're gonna be looking at okay let's go ahead and click on David okay and we have three different meetings and now I've just created those calendar let's go back let's see if I can separate this reduce this so we can look at both things at the same time okay all right and then I want to pull up that outlet calendar as well so you can view that as we scheduled appointment let's go ahead and pull up the Outlook calendar now and move that over a little bit and reduce the size so we can see both on the screen at the same time okay great now we've got it so let's go ahead and we have a default let's see we've got our default appointment duration at 60 minutes okay I've included a lot of minutes we'll just leave it at 60 for now and let's go ahead and schedule a 10:00 o'clock okay let's say meeting with Peter okay all right great and now that's on the January 5th so let's go ahead and locate January 5th there it is meeting with Peter okay great under David now let's click on dirt let's go ahead and hide this we don't need to see that and now let's also on January 5th let's click another staff Fred Fred errs okay and then say let's just call it lunch with definitely okay and there it is you see how quick that is and then Lisa okay lisa lopes all right and we can just double click on this and hit enter that's enough oh great now let's go ahead and change the duration of it alright let's go ahead and change it to 90 and let's go ahead and schedule another appointment and then meeting with Bob okay there we go and you see this one's 90 now we double click on here and you can see it's set for from 2:00 to 3:00 to 3:30 p.m. already on Lisa's appointment so that's working great now how does this work let's go ahead and go into how how we got this to work okay and we'll minimize that and let's go back in to the increase this again and slide this over so we can see how this working into the VBA model we go now under outlook sync okay remember it says if yes send to Outlook okay so ditz then this macro is gonna run basically what we've done and you can study this on your own basically we've set a lot of things as objects and this is called late binding late binding and you may very well have to have to add this to your calendar now go ahead and let's just see if this is run tools and references and let's see if it's works without the Microsoft Outlook library you may have you let's go ahead and run this okay it very well probably should cause a cause a bug but I want to go over this with you in case you run into that as well okay now without that reference library you're gonna get this bug variable not defined and why it doesn't recognize appointment item it doesn't know what that is okay because you are required so if you get an error that looks like this compile error variable not defined it thinks it doesn't understand that our appointment item because it doesn't have the definitions or the libraries and those come under references so we reset the code when we have a bug we go to tools and we go to references and we find we find what the Microsoft Outlook and you're it depending upon the version of Outlook you have right this could be thirteen fourteen fifteen or sixteen as long as you selected the outlook library that's really important okay and so once we have it and then once we go into tools references and you'll see now it's been selected okay so that's really important we'll need to use that we don't necessarily need it for email but we do when working with Outlook calendar we do need this reference library slip so that's very important if you do get a bug you'll want to make sure you locate your your Microsoft Outlook library regard of the number and go ahead and select that okay and now we can run it without without issue without without a any type of a problem okay so that's helpful and that that covers our library so that's important if you get that bug so basically we've also defined a staff row we need to know what staff row and we need to know what row we've selected so that we can add that to the schedule and we used to know the appointments start right and the appointment time the duration the appointment name and the calendar I do you remember that calendar idea is important and we get that from the staff page so let's go ahead and say first of all we need to make sure that pb8 is not empty because that will show us that we don't have a correct staff selected and if we go back into the schedule and we look under b8 remember that this is the staff room let's go ahead slide that's over this is the staff row so if there's an air it's gonna be set to blank so we're just checking that if it's blank it's gonna say you know the code says blank then it's an error okay so that's an important step so we need to make sure that it's not empty okay and then next time exit out of there and go back into the code okay so as long as not blank we set the staff row okay we need to know what row and the reason we need to know the staff row is because we need to get that calendar ID I need to know what bro is it Row three four or five because I need this Outlook ID I must have that that Outlook ID is in column E and whatever staff throw it so we need that staff row we know it's calling me but we don't know the row so the staff row is critical in order to get that so the calendar ID equals sheet three column E and then the staff row that's going to get us our calendar ID and then this is a double check if you have not assigned the calendar ID or it's empty we'll say please assign a calendar to this staff okay that's the message then it's gonna appear right away right Det it's going to activate g3 and it's going to tell the user to go to that specific area right so that's kind of a nice touch and I like that so watch what happens let's go ahead and delete this delete this calendar ID okay go back to the schedule click on again Lisa which is selected and now we have Outlook okay and now it's gonna say please the sign of calendar so the message by now watch now automatically it's gonna go to the staff and it's gonna select this cell so that that's kind of a nice touch so now we can click on it and assign it okay so it's a nice touch because it goes right to that so that's how we do that in the code activate the sheet go to the specific place and select and theoretically we could also launch one more macro we could launch this macro right this would be kind of a nice test let's try that okay so now why don't we do that so now let's go ahead and look now let when we delete it it's gonna launch automatically that macro okay so now when we double click this and inter it's gonna say please assign a calendar it's gonna go to the sheet and it's gonna launch this which is a nice touch also okay so so that's good I'll leave that there okay so that's one less step user has to do so we really want to guide the user on to help them you know so that they know how to do it and you know as much as we can't automate so it's always a nice touch alright so we've added that there and now let's go ahead and now this line of code adds the Outlook application so this this basically starts out looking says okay Outlook application is equal to oay lap okay and now get name space and this is gonna help us it's not so important you know exactly how to do this but basically this gets the name of the outlook okay and then we need to know what row has been selected okay so that's really important because we need to know we need to know what the date we need to know what the time is right so if we're gonna send this if we're gonna send this to Outlook I need to get this twelve o'clock time and how do I know this that's why we always use the selected row you see it's row six right and when we select a different row nine okay nine okay so I got to reset that but basically oh it's a change oh it's not selected my back so if we make a change then it's changed okay so it's not selected let me change that changed this guys are okay so it's really a change schedule so when we make a change right that's not not necessarily to selected but when we make a change we need to record that row and in the code how does that work in the code well it's really simple on on the schedule remember on the sheet directly on change worksheet changed not selection cannot selection worksheet change all we need to do is we just take this range b1 equals the target row okay set row for Google Calendar okay so basically all we've done is added the target row to be one and that's going to help us to know what row was changed so that's an important distinction so automatically we're setting whatever change we make okay we need to record that row not the solution alright so we've covered that because we need to know the time is L and the changed row okay L and the row that's changed will give us that time at that time snow we need to know what time to send to Google and what time to send to Outlook so that's why we have let's go back into the Outlook macros so we know that the appointment starts at L and the selected value and now why did we add M okay Outlook reads Outlook reads both the start date and the start time as 1 and remember in in Excel a time is simply a date plus the time okay so a date is like a whole value and the time is a decimal value so when you put those together you get the date and the time okay so we combining the date and the time here the date is m3 the time is l and the I should shade the Allen the selected row which actually the changed row so we're combining the data and the time and that's gonna we're gonna send that appointment start date in time to Outlook okay the name is whatever we've have in the selected row the name okay and the name is remembered in this case M right so it's dis is the name there's the name in this the name so that's just the text that goes in the field okay so we've got the boom and then the duration is p10 remember that's the default duration okay in this case 60 or 90 so whatever we've set in the duration p10 is here okay p10 is here that's the duration okay so all we need to get those variables so we can send those to Outlook and then the next code is simply sending it and now we're gonna set the folder which folder in in Outlook they call folders they call calendars folders okay so these are actually these are actually within outlook these are actually folders okay you see how it says all folders right so they consider this a folder this a folder and DISA falter okay so they consider those folders so that's why we say folders okay they are actually designing them as folders okay see all folders here okay so that's why it's called folder okay so and then we've set the appointment we've created an appointment we need to set an appointment and then we need to make sure that we set the olí 'tom outlook items okay so we're basically just setting up the calendar okay and we need to set an existing item okay and this will help us okay so the first part is the first part is is basically we're gonna say if existing item sucks the reason we add this in there what I want to do is I want to say hey if there is an appointment we need to search why are we searching if there's an appointment already at that time then update it if there's no appointment at it so this is two things basically it says if the existing item is nothing if there's no appointment that's one set go ahead and add it however if there is an existing appointment okay then we don't need this if there's an existing appointment go ahead and update it so basically you see how this one doesn't include the start okay so basically it doesn't include start because if there's an existing appointment we don't want to change it all right at that time we just want to update it maybe a subject maybe we want to update the duration we can add categories to this and also you see this is kinda interesting move folder outlook ads automatically appointments to the default calendar we don't want the default calendar we want to add it to specific calendars so basically if we just if we remove if we don't add this it's gonna remove that appointment to whatever your default calendar is but we need to say move it to Oh al folder remember Oh al folder is the calendar ID for that specific person okay Oh Oh folder is so this moves this actually it basically it sets an appointment on your default calendar and then it moves it to the one you want so that's actually how it's working behind the scenes okay so this is how this code works I'm moving a little quicker so we've got a lot to cover and I don't want you falling asleep again okay I'm watching you I know you're gonna be I'm watching you okay don't don't sleep so what we're gonna do is we're gonna basically say if it's nothing at it if it exists already updated so that's all that that code is doing okay so that's how that works that is how outlook how we're adding it to ala calendar and you can break apart this code a little bit more and see how it works this is some additional code that I'll all you may not want I'll go ahead and remove that I thought it might be helpful but I don't want it I don't want to get good to get it too confusing for you keep it nice and clear so that's all we're doing that's how we add to our calendar okay so next up we're gonna be moving to Google Calendar Google Calendar Sync and this is a little bit shorter code and with something we've gone over before using zapier or zapier I don't know how you call it but let's go ahead and do that let's go ahead and and add it to Google first of all why don't we go ahead and send to Google yes okay so what its gonna do is and now let's go ahead and go into Google Calendar and here's my Google Calendar and once again we're gonna create some calendars just like we did now look okay so let's go ahead and click a new calendar and as you can see we have of course three staff David Fred and Lisa so we're gonna create a calendar for each of those David ok create the calendar and now now we'll create another one Lisa ok create calendar and Fred ok so now we've got three calendars created ok and now we can go exit out of settings and now we have let's unclick this one now we have David Fred and Lisa in Google it's a little bit different they're gonna put all those on the same people on the same screen it's not a little bit divided differently but that's fine David Fred and Lisa we have now have three calendars with three distinct colors that Google had signed I believe we can update the colors to whatever we like David okay so we can update calendars we'll keep those the same unlike Lisa's oran no that's good I like it like that let's do green ok so we can see it they're very make sure they're very distinct and different ok David Fred and Lisa we have three different calendars now great and if you remember correctly back a few weeks ago we did say pure integration and if not I'll make sure to provide the link if you haven't seen that I'll make sure to provide a link cuz we're gonna move fast on this since it's something we've gone over already so basically what I want to do is I want to create a web hook which we've gone over the past I don't want to send it from Excel to Google so we can do that using zapier and we're gonna create a new web hook and basically what I want to do is I want to create a web hook and I want to catch a hook ok and I want to continue this step and basically what I'm gonna do is if you remember correctly web hooks this this part of the web book it's always the same for every web hook you create basically it's gonna allow us to send from Excel to anywhere in this case we want to send to Google Calendar so this parts all gonna be the same so what I want to do is I want to only copy this part this is going to change for every web and what I'm gonna do is I'm going to create three different web hooks and each web hook is gonna send to a different calendar so what I want to copy this part only this the only part that's changed okay I'm gonna copy that and what I'm gonna do is I'm gonna add that to let's go ahead and start with David okay and I'm gonna add that web book ID right here okay what that's gonna do is gonna say okay this part of the web hook is gonna change I want to make this for David okay so when we go back on let's go ahead and click on David's schedule here and let's go ahead and not go to Outlook just Google for now okay so now we have David's schedule selected and let's go ahead and go to that next step okay so it says make sure you follow the Sherman we've generated a custom okay we did that and now it's going to look for that and so now let's go ahead and quickly run our code and just by double clicking here and it's gonna send a web hook through our code and I'll show you how that works okay so it's gonna look for that code and it found it okay so our test is successful let's go ahead and view the web hook okay we have meeting with Peter that's correct 10:00 a.m. start finished 11:30 we've set it for 90 minutes okay so that works okay and I'll go over that with you so don't worry about that and so now what we want to do is we want to send it to Google Calendar so it says that the next step is Google counter select that okay and I want to create a detailed event detailed event okay and then save and continue and then I've already set up my Google Calendar it may if you haven't set this up it'll ask you you connect an account which is very simple just click the buttons follow steps save and continue okay so now it knows now it's since it knows it knows all my calendars now we've created three calendars okay we want to select David David's our first one okay and now it says well what do we want to we want to say okay meeting with Peter okay because that is the summary the description we don't need it's optional location we don't start date let's click there start ok and click the end and that's it it's just really three steps ok and we have lots of other options we'll ignore those for now they're not required for our purposes but you do it you should look through them there's so much you can add to this ok and then we'll go ahead and send it to Google Calendar let's send the test to Google Calendar and let's go ahead and I don't remember let's go ahead and I think it was the 5th right I believe there it is right here meeting with Peter 10:00 11:00 a.m. ok that worked out great so the test worked let's continue and finish our step finish and let's call this David calendar ok and then we'll turn our zap on that's critical you need to make sure you turn that zap on ok so now let's take a look ok now it's working we don't need to add another step just yet so here it is here in our zaps David is turned on so we know I'm gonna go ahead and delete this okay because it was just a test and now what I want to do is I want to pull up Excel all right and we're going to minimize that scroll over here ok and let's go ahead and say it's create a new appointment now we know this is David's calendar here let's go ahead and move it over here it's much better okay so we've got David Fred we've got them all selected so if as long as they're selected up here ok lunch with Bob okay it takes a takes of another second or two a little bit longer than it does in in Outlook but there you have it lunch with Bob ok and there that's working great so let's go ahead and do the same process for the remaining two for the remaining two staff we have back into zap here we go ok let's go ahead and make another zap okay you see how quick and easy this is once you get the hang of it it's real and we're going to start with a web hook okay catch a hook and then save and continue okay continue to step up there's no child Keys that we're picking off okay and we're going to copy just this part of it and this is gonna be different every time okay copy that back into Excel we go alright let's expand this so we can see what we're doing here back into the staff and we're gonna use this this is gonna be Fred schedule okay so we're gonna copy and paste this right here okay back into zap here we go and then we just say okay I did this and now it's gonna look it's waiting so now we got to quickly go back into Excel back into the schedule click on the right staff Fred I believe we're doing and then we can double click this and then now it's gonna send that hook to zapier because I just double clicked it and go back into step and it says test successful good so we can always view or hook just to make sure to lunch with Debbie 12:00 to 1:30 let's take a look at that so it caught it it caught it lets you Debbie perfect that's working great okay let's continue with that and we're gonna do the same thing it's continue and now we want to send to Google Calendar and create a detailed event save and continue and then save and continue we've already got that account continued and now we're going to we got stuff to write remember this is Fred so we're gonna connect his calendar on our web event here so the calendar and we're going to select Fred and go through the same steps that we've done before the summary is going to be the name start time okay and start time here and then the end time here we'll catch that hook and time okay good we just those three fields as long we need to do continue okay that looks good send tested Google Calendar let's go ahead and check that and there it is lunch with Debbie okay goodgoodgood you see how they're in different colors now right fred has a purple david has green okay so that worked great so the test worked great and we now we finish and that's go ahead and name it Fred calendar okay and make sure we turn that on all right excellent okay and then we want to see it on our dashboard so now we have two Fred calendar David calendar let's go ahead go back into Google let's go ahead and delete this one okay and now let's go ahead and test that out and once again reducing this move it over and we'll pick a different time let's see breakfast with Tina okay and then we'll wait usually about two seconds or three seconds and then it will appear in just a moment there it is breakfast with Tina at 9:00 a.m. okay that's working great and they're different colors now we just have to do one last one and that is for Lisa okay make us app all right starting with a web hook now you're getting the hang of it right save and continue continue and we're gonna copy that web hook ID which is right here okay and then okay I did this step now we're gonna take that ID and we're gonna copy that into Lisa's call them on the staff which is here right here okay we're set on that good now we're good to go on that let's continue and it's still looking for the job it's not finding because we didn't send it yet let's go ahead and send it okay back into the scheduling screen click on Lisa okay and we just have to double click on any any meeting on their schedule and now it will find it okay great you see how it just found it test successful I like to view it you can't - Fred's meeting 12:00 to 1:00 okay that's great and continue good now we're going to send to Google Calendar go through the same steps that we have done before twice before detailed event save and continue' save and continue' and then we'll go ahead and select Lisa's calendar here that we've already created add to the summary which is the name the start time once again under start and then the end under end so now we've created three different calendars and scroll down and continue all right we're good to go and send two tests to Google all right let's go ahead and see this there it is Fred's meeting and Lisa's in yellow excellent complete the last step which is to finish and name our zap Lisa calendar okay and then turn that on excellent so now we're done now we're done well we've got three different calendars and they're all synched to Google and let's go ahead and test that out delete our test and now let's go ahead back into Excel and we've already selected Lisa and we will just say meeting with Peter all right at 4:30 p.m. let's go ahead and scroll down so we can see that on this not there scroll down all right there it is meeting with Peter at 4:30 perfect so that's working just great so how does that work how did we get that to work let's go ahead and show you the code behind that so we can so we we can see how that's done although I think you have an idea because we did go over webhooks sent to Google Calendar the first thing we want to make sure of in the scheduling screen if p9 is yes and to go to calendar okay so if it's no we're not going to we're not going to enable that all right now under the Google Calendar Sync Metro macro we have done this before so we're gonna go over this real quick we we've defined our objects and our name and our webhook ID webhook ideas new okay as strings those are strings we've defined appointment start and end as dates okay start and end or dates we defined staff row so to grow and duration as long okay basically we're saying if again we're just checking to make sure that the staff is correct we've correctly scheduled to staff okay and also we need to make sure that there is a webhook ID okay remember saying basically is saying if F if the F and the staff row is empty and basically what that means is that what's going to the staff if this is empty F right and the selected staff row if this is empty we need to go and make sure that we've created a web hook for that particular staff which is why we have that part of the code there okay and then the next is we're just defining the selector rule remember b1 that's the row okay the duration is that duration that we've set under p10 just as just as we did in Outlook the start date again is also the date plus the time the date plus the time and in this case we also we need to set the duration okay now the appointment end the point end date is the duration plus this formula here now why that number okay that number is is basically what one minute is so basically we're taking the number of minutes and we're creating an actual time a decimal we need a decimal I need to I need to take this start and add a decimal to it right because ninety or ninety two the sig 90 minutes and that 90 doesn't really amount to minutes when we do the calculation so in excel we need to add the decimal so let me show you how that works okay so in order to get a one-minute what we want to do is we want to make it just a simple division so basically equals right one divided by 24 okay which is the all the hours in a day but we actually want to divide that by minutes so to get that we'll just do that this number divided by 60 so this is going to give us the decimal for actual minutes okay if we expand that'll let a bit under a decimal we'll see we'll see how that translates into into a number okay let's go ahead and reduce that so we can see how that translates into a number okay so here it is here's the number point zero zero six nine four and we've rounded up two points there so one minute is equal to point zero zero zero six nine five and that is the reason why we've taken the number multiplied it so that is how that is how we can we can see how that's done in the VBA code why we use six nine five okay so we've taken the duration 90 and multiplied it times and never miss and that's gonna give us the decimal format in order to figure out the end time of the appointment okay and then the appointment name is also M so what we've done is we've created a web hook and you remember there's three we didn't we've created a web hook and the difference is we've created this this is our default web so when you get yours you will want to make sure that the first part of your web hook matches this don't use my web hook okay because it won't get you anywhere yours will be unique to you this number here will be unique to your account it won't change for you for your account but you'll want to make sure this is from my account okay so you want to make sure you use ones for your account the web hook ID okay that is specific for each individual calendar edition so remember we have a different web hook ID for each one we're taking that idea and we're adding it to the link here okay and then so I'll make sure I'm gonna put in a text here so when you see I'm gonna just go ahead and say add your web web what's good web look link here okay that won't work unless you add your web hook link okay so that's very important you want to add that add that there so you can definitely see change this okay to your web hook link so that's a very important part and then we have the appointment name just the three variables appointment start and appointment end so that's all we needed there okay and so make sure it's not gonna work until you add your link here okay that's all you have to do so here's what you have to do you have to go in here okay go to your yours API here go into the dashboard click on once you've created your link or you're in the process right you can view your web book and just this part here you see that right here you got to copy that and paste that right in your code right here right here that's all you have to do okay that's it and I'll get rid of this here so that's all you have to do there okay so it's very simple in order to connect that okay great so that is exactly how we've added to Google Calendar all right and next up the last one we have is email schedule this is a cool little feature I wanted to add a little bonus on for you as if since we're getting close to an hour and I better end this soon we've added the ability to click on a particular let me go ahead and turn this since we don't have let me go ahead and turn this off for now because I've we need to make sure you put your link in before you do that and then we've getting the ability to email the scheduler which is a nice fit since we know we have email addresses here it wouldn't be nice if we could just email the schedule to our stuff so we're just clicking on the button will create an email and we go ahead and reduce that and it'll we can put text in there and we could say so in a PDF format so this is a nice feature that we can automatically add it to let me go ahead and reduce the PDF so this is what it would look like and so it'll fit the screen and so here it is so that that's a nice touch now how did we do that let's go ahead and go go over that and I'll you see Lisa's email address is already in there I've I have a subject that includes the month automatically it's a variable and as well as the name alright so that was really easy let's go ahead and take a look at that how we did that back into the V be a model we go under email schedule and it's a very simple code and we've added we've done email in the past we're not gonna go over too much about that all I did was set the schedule range right it's dim it has a range the file name basically email address subject and message okay first we set the schedule range to d3 through j40 we've set that range okay and that is basically this range right here okay and also what we did is we made sure that these buttons I don't want them to print so when we click size and properties right and we go ahead into properties we want to make sure that we don't want to print those buttons so this is unselected that's important all the buttons if you don't want to show that so all we've done is made this all the way down okay this print area I've just said which is d3 through j40 okay so I set that also while I'm at it make sure on your schedule when you hide this this is for information we went over this no one make sure you hide this because it's not important and you'll want to hide these columns too so you get a nice you know the first two columns you want to make sure you hide those okay and but for our purposes I want you to see what's going on I want you to see every part so I'll unhide them for you but when you you know release it you want to make sure you hide this bro and this column okay so back into the VBA we go where we're going over the amount so also wanna set when we create a PDF we need to save that PDF to somewhere on our computer and then we also need to attach that in an email so the first thing is important is we need to save it but now everybody's computers different so what we said is what I'm saying is just saved this at whatever path the workbook is so wherever your workbook is located it's gonna save it there which is kind of just a nice default path because everybody's computers paths and documents are different so this kind of just sets it and you want to save it as um which is d3 value which is the datum sorry which is the name of the calendar which a month okay d3 so I want to save that PDF as the name of the month and the year okay so that's what that's what I've done there I've saved it as and I'm added PDF so this is going to be our filename okay now what I want to do here is I can't create a PDF if there's already one I can't existing on my computer with the same exact name it'll it'll create an error right if we've if I try to create a PDF with the same exact name so this line of code says if the directory of file name is greater than zero basically that means if it exists then kill it kill it means delete okay so this deletes it if it's already been created so this is kind of a nice and and then we have on-air resume next so basically if it's not it'll just skip this if there's an if there's an error it'll skip it so that's kind of nice so what that does is just automatically deletes the PDF if it's already been created and now we're just saying the email is sheet three and b8 and remember this is the staff row staff or ob8 D and the staff row is your email address subject and you can put any subject you want your d3 value you know this is the month again then month your scores so we've said the schedule is the month and the year schedule okay so we added that in and then the message is see in the message is high and then I want to get their name right so remember b8 is the staff row b8 is the staff row C is the name okay so basically I want to say C right whatever row is their name okay so that's what we've done there here is your monthly schedule for the upcoming month and this has an enters a new line in the text okay please let us know via you can put whatever text you want in the subject okay and then basically what I'm saying is set this page up to our scheduled range remember our range is d3 to j40 that means this is going to set the print area to the Dryad rest to the address remember the address is this here okay and there's a few ways of doing this but that's one way and then this is the key line export okay this is a key line export has a PDF the file name is the file name the quality standard include Doc's okay include document properties you can ignore print areas no don't ignore it and you can also if you want to display this PDF you can just click true right and that'll display the PDF after after we've created this so you can also show if you want it to launch so that's it and then all we've done is do the email we create the email this creates the email send it to the email address send it to the subject put the message in and then here's the key attachments add file name remember this is the entire file name also display remember ok display that means I want the email to show but if you don't want it to show you wanna send it right away just click dot send okay and then comment this out that this if you put dot send it'll send it without showing without even showing it could be email so but I always you want to display the first but if you don't want to click send every time just changing out the dots in it'll send it automatically all right and then nothing and then we've cleared out that is it ok ah more than an hour on this training but I we had a lot to cover okay so let's go over ahead and go review what we've covered today we've added additional staff here and they we have an unlimited amount of staff we can simply select the staff and it's going to change and update the schedule accordingly based on whatever staff is selected we have added both Outlook and Google integration automatically simply by creating yes and that allows for multiple staff on multiple schedules we have added months so we can simply select the month here it's a quick way to get to where you want to go we have also added a default appointment time so when you select the number of minutes we can set the default appointment and we've added email schedules so we can quickly email our schedule our monthly schedule to whatever staff we have I've selected here and that is the summary that is it thank you so much for joining me on this amazing training I hope you like it please please share this with your friends in your favorite groups and I always appreciate your efforts to view these videos with your likes and shares thanks so much and have a great one

Channel: Excel For Freelancers

Views: 18,826

Keywords: Excel Appointment Scheduler, Excel Google Calendar, Excel Outlook Calendar, Appointment Scheduling In Excel, Excel Scheduler, Excel Scheduling, Excel Appointment Sync

Id: 3n-bzYQqJyE

Length: 64min 34sec (3874 seconds)

Published: Tue Jan 30 2018