Thank you again and again Randy for sharing your knowledge in a way we all understand to help us create fantastic applications. I wanted to share with you (and offer a suggestion) what I am trying to create which may also be an idea for others. I get carried away with untidy modules and long code that if I leave for a week or so it takes me a while to understand where I was at and what my code is doing. I am sure you could put together a better basic start which we could build on. I am currently trying to create an automated rotating job list that communicates with my roster via cell comments, text within cell and determining which car for job by colours of cells. Start times carry from roster as does finish times and breaks. When a job is allocated a report is generated that assists manager of who allocated and what time - this helps to keep the jobs allocated fairly. Jobs are allocated according to work status ( casual/ partime/full time) and also whether they are at work or not (training, sick, secondment) in rotation. Keep up the great work! I check all the time what you are up to and have implemented many of your ideas into small applications.
Thanks so much for sharing. Sounds like you have a great and interesting project. What type of business is it? If its something that is very common, perhaps I can create a project for it, however if its very unique and niche it may not have a broader appeal. Thank you for your Likes, Shares & Comments. It really helps.
I work in a team for Local Council that respond to reported jobs such as dumped rubbish, abandoned vehicles, overgrown vegetation, wandering dog pick ups, dog attacks etc but could apply to any team that responds to jobs. Could be allocated according to training, availability, location to job, rotation, time available before shift finish. The allocation process is linked to the main roster which takes information from roster in order to allocate jobs to staff on shift.
Oh yes that is very good. I will do my best to create a template in the future with some of these features. Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy! ...always great explanation! Thx so much.... What about the ‘Teams Meeting’ function, what would we have to consider/set up here in the VBA code? ...it would also be good to know how we can address the option: Categorise? ...Do you have any ideas/tips?
Hi and thanks so much, I am glad you enjoyed it. There are many features I would have loved to add, however I want to keep it simple and quick for these basic VBA training. I will explore more features in a more comprehensive application. Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks so much. You could use the following code, or something like it, to add the time zone: Sub AddAppointmentWithTimeZone() Dim OutlookApp As Object Dim OutlookNamespace As Object Dim CalendarFolder As Object Dim AppointmentItem As Object Dim TimeZone As Object ' Create Outlook application and access the default calendar folder Set OutlookApp = CreateObject("Outlook.Application") Set OutlookNamespace = OutlookApp.GetNamespace("MAPI") Set CalendarFolder = OutlookNamespace.GetDefaultFolder(9) ' olFolderCalendar = 9 ' Create a new appointment item Set AppointmentItem = CalendarFolder.Items.Add(1) ' olAppointmentItem = 1 ' Set appointment properties With AppointmentItem .Subject = "Team Meeting" .Location = "Conference Room" .Body = "Discuss project updates." .Start = #1/15/2025 10:00:00 AM# .End = #1/15/2025 11:00:00 AM# ' Optional: Set time zone (for supported versions of Outlook) On Error Resume Next .StartInStartTimeZone = .Start ' Set using local time zone .EndInEndTimeZone = .End On Error GoTo 0 .ReminderMinutesBeforeStart = 15 .BusyStatus = 2 ' olBusy = 2 .Save End With MsgBox "Appointment added to calendar!", vbInformation ' Clean up Set AppointmentItem = Nothing Set CalendarFolder = Nothing Set OutlookNamespace = Nothing Set OutlookApp = Nothing End Sub
Hi and thanks for your comment. It may be possible however I have not used the new Outlook yet. As it becomes more popular I will check out the integration. Thank you for your Likes, Shares & Comments. It really helps.
I tried the multiple appointments code and I'm running into an issue where it will only submit the last appointment. The previous appointments disappear from the Outlook calendar. For example if I have 2 appointments and I set the last variable to Display: I get the pop-up for the first appointment, and then it's immediately replaced by the 2nd pop-up - the 1st one doesn't stay there. Same for if I put Save for the last variable to just submit them: the 1st appointment appears briefly on the calendar, then disappears. The 2nd appointment appears on the calendar and remains. I assume I have some kind of setting that's causing the issue, but Googling didn't seem to show anything. If it's a unique ID issue with the appointments, is there a way to set that? Maybe that will cause them to persist on the calendar after saving them via the VBA code.
After messing around with the code for a while, the solution that worked for me was putting the OutlookApp.CreateItem(1) within the loop code. For some reason that was being lost with each iteration.
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
😱 𝗧𝗨𝗥𝗡 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗗𝗔𝗧𝗔 𝗜𝗡𝗧𝗢 𝗦𝗜𝗠𝗣𝗟𝗘 𝗦𝗢𝗟𝗨𝗧𝗜𝗢𝗡𝗦 𝗪𝗜𝗧𝗛 𝗔𝗜 ▶ www.excelforfreelancers.com/AiDataAnalystYTPinnedComment
Nice step by step introduction to combine the two programs together!
Great and thanks so much. I am happy to help and share
⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack
Thank you again and again Randy for sharing your knowledge in a way we all understand to help us create fantastic applications.
I wanted to share with you (and offer a suggestion) what I am trying to create which may also be an idea for others. I get carried away with untidy modules and long code that if I leave for a week or so it takes me a while to understand where I was at and what my code is doing. I am sure you could put together a better basic start which we could build on.
I am currently trying to create an automated rotating job list that communicates with my roster via cell comments, text within cell and determining which car for job by colours of cells. Start times carry from roster as does finish times and breaks. When a job is allocated a report is generated that assists manager of who allocated and what time - this helps to keep the jobs allocated fairly. Jobs are allocated according to work status ( casual/ partime/full time) and also whether they are at work or not (training, sick, secondment) in rotation.
Keep up the great work! I check all the time what you are up to and have implemented many of your ideas into small applications.
Thanks so much for sharing. Sounds like you have a great and interesting project. What type of business is it? If its something that is very common, perhaps I can create a project for it, however if its very unique and niche it may not have a broader appeal. Thank you for your Likes, Shares & Comments. It really helps.
Thank you so much Randy for this amazing video.
For sure, you are very welcome and I am happy to help and share
I work in a team for Local Council that respond to reported jobs such as dumped rubbish, abandoned vehicles, overgrown vegetation, wandering dog pick ups, dog attacks etc but could apply to any team that responds to jobs. Could be allocated according to training, availability, location to job, rotation, time available before shift finish. The allocation process is linked to the main roster which takes information from roster in order to allocate jobs to staff on shift.
Oh yes that is very good. I will do my best to create a template in the future with some of these features. Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy! ...always great explanation! Thx so much.... What about the ‘Teams Meeting’ function, what would we have to consider/set up here in the VBA code? ...it would also be good to know how we can address the option: Categorise? ...Do you have any ideas/tips?
Hi and thanks so much, I am glad you enjoyed it. There are many features I would have loved to add, however I want to keep it simple and quick for these basic VBA training. I will explore more features in a more comprehensive application.
Thank you for your Likes, Shares & Comments. It really helps.
Absolutely love this! What would the code be for adding the time zone?
Hi and thanks so much. You could use the following code, or something like it, to add the time zone:
Sub AddAppointmentWithTimeZone()
Dim OutlookApp As Object
Dim OutlookNamespace As Object
Dim CalendarFolder As Object
Dim AppointmentItem As Object
Dim TimeZone As Object
' Create Outlook application and access the default calendar folder
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set CalendarFolder = OutlookNamespace.GetDefaultFolder(9) ' olFolderCalendar = 9
' Create a new appointment item
Set AppointmentItem = CalendarFolder.Items.Add(1) ' olAppointmentItem = 1
' Set appointment properties
With AppointmentItem
.Subject = "Team Meeting"
.Location = "Conference Room"
.Body = "Discuss project updates."
.Start = #1/15/2025 10:00:00 AM#
.End = #1/15/2025 11:00:00 AM#
' Optional: Set time zone (for supported versions of Outlook)
On Error Resume Next
.StartInStartTimeZone = .Start ' Set using local time zone
.EndInEndTimeZone = .End
On Error GoTo 0
.ReminderMinutesBeforeStart = 15
.BusyStatus = 2 ' olBusy = 2
.Save
End With
MsgBox "Appointment added to calendar!", vbInformation
' Clean up
Set AppointmentItem = Nothing
Set CalendarFolder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing
End Sub
❤❤ This is so helpful! Thanks a lot
Awesome. I am happy to help and share
Another great training. You make it look so easy! Thank you
Thank you so very much, I really appreciate that and I am happy to help
Thanks a lot!
For sure, you are very welcome and I am happy to help and share
great job thanks
Thanks so much. Very kind of you Rob
Hi Randy,
is there any way to use new Outlook instead of classic one?
Hi and thanks for your comment. It may be possible however I have not used the new Outlook yet. As it becomes more popular I will check out the integration. Thank you for your Likes, Shares & Comments. It really helps.
I tried the multiple appointments code and I'm running into an issue where it will only submit the last appointment. The previous appointments disappear from the Outlook calendar.
For example if I have 2 appointments and I set the last variable to Display: I get the pop-up for the first appointment, and then it's immediately replaced by the 2nd pop-up - the 1st one doesn't stay there.
Same for if I put Save for the last variable to just submit them: the 1st appointment appears briefly on the calendar, then disappears. The 2nd appointment appears on the calendar and remains.
I assume I have some kind of setting that's causing the issue, but Googling didn't seem to show anything. If it's a unique ID issue with the appointments, is there a way to set that? Maybe that will cause them to persist on the calendar after saving them via the VBA code.
After messing around with the code for a while, the solution that worked for me was putting the OutlookApp.CreateItem(1) within the loop code. For some reason that was being lost with each iteration.
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.