Excel VBA to create multiple meeting invites in Outlook
Вставка
- Опубліковано 31 лип 2024
- In this video i demo how to create and send multiple meeting invites with a single click of a button.
Demo workbook can be found here
drive.google.com/file/d/1RiCq...
#Outlook
#ExcelVBA
#Meeting
Your videos are easy to follow and informative. Thank you for the help!
Thank you very much for the feedback. This is much appreciated
Thank you very much.
I included the code below if that is helpful.
Option Explicit
Sub CreateOutlookTasksWithReminders()
Dim OutApp As Outlook.Application, Outmeet As Outlook.AppointmentItem
Dim i As Long, ws As Worksheet
Set ws = ActiveSheet ' Worksheets("Sheet1")
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Set OutApp = Outlook.Application
Set Outmeet = OutApp.CreateItem(olAppointmentItem)
With Outmeet
.Subject = ws.Range("A" & i).Value
.RequiredAttendees = ws.Range("E" & i).Value
.Start = ws.Range("B" & i).Value + ws.Range("C" & i).Value
.Duration = 10
.Importance = olImportanceHigh
.ReminderMinutesBeforeStart = ws.Range("D" & i).Value
'.Location = "Microsoft teams"
.MeetingStatus = olMeeting
'.Display
.Send
End With
Next i
' Clean up
Set OutApp = Nothing
Set Outmeet = Nothing
End Sub
I have been searching online tutorials for this problem without success. Your code worked the very first time and is exactly what i was looking for. Thank you does not express how thankful I am for you as a resource. May God bless you.
Amen. Thank you very much for your feedback. Glad to be of help
Hi, want to say thanks for your base demo file. I edited that and managed to get what I wanted it to do. This will save me plenty of time at work!
Edit: oh and I will be sure to check out your other videos, I already see a few which can totally make life easier for me :)
I am glad you found it helpful. With minor tweaks, you can always get it to meet your needs. Thanks for the feedback
You are the BEST! This is the most i have ever understood the VBA code I am writing. Thank you so much! You are such a great teacher!
Wow, thank you! Maybe teaching is hereditary afterall. Glad it was useful to you
Hi Victor, this is great! The explanations were clear and simple. I'm sure this would come in handy one way or another.
Thank you for the feedback. On it coming in handy, absolutely. Please do let me know when you eventually get to use it 😁
@@ExcelMoments that sense of humor :)
This was so helpful! Thank you so very much!
You are welcome. Thanks for the feedback
Great Stuff....I have been able to use it and you have saved me tons of time. Thank you very much.
Great to hear!
Very clear! Thank you!
That's so great to know.
Just subbed THANK YOU - Work smarter not harder.
Thanks
Thanks for sharing this.
It's really helpful
Thank you very much for the feedback
Excellent.🙏👍 Thank you so much
You're most welcome
Thanks a lot. Your voice is very good. It is slow and steady which is helping us to understand this better. Appreciate your effort. Kindly let me know if we can MS teams meeting link as well.
You are most welcome. I will play around and provide feedback. A part of me feels that I have done something in the past!
Hello Victor,
I have problem trying to remind the attendees twice before the meeting.
Say, 30 minutes and 15 minutes
Thank you for the video. It has really helped
@ExcelMoments - How to send a single Teams Meeting to all those listed in excel column A that are required and all those listed in excel column B as optional? I've tried maneuvering the code several times but can't seem to get it. Any help would be appreciated. Thank you!
Hello, I know its been some time since the release of this video - but is there a way to add images/tables into the body of this invite using .htmlbody code?
Hello, thanks so much for this video. I'm very new to VBA - wondering if I can have formulas in my data set for the start/end date/time fields and still have this macro work (note I set up the macro almost identical to yours except I am using .Start and .End rather than .Duration)? I am trying to automate the setup of about 100 or so appointments that are largely the same quarter to quarter, so it would be huge if I can just set it up with some formulas so that the dates more or less roll forward each quarter based on like 3-4 key milestone dates. So far I've been getting an run-time error saying the object does not support this method. Any thoughts would be appreciated!
Hello Molly, Having formulas is not a problem at all. it would work irrespective. On what line of the code are you getting the run-time error, that line should be highlighted whenever you run the code and it stops, that way i may be better positioned to help.
The other thing is are you calculating the duration from start and end times or how is that working exactly, because duration needs to be minutes and if that is not the case, it may throw the code off. But I am sure once I hear back from you, we should be able to make progress and get the issue resolved
Can we add a signature to the body that includes a graphic?
How would you turn off the request for the meeting response? (i.e. accept, tentative, or denied)
Hi. this worked great for me and I was able to add it to send from a shared mailbox, but when I do so, it still shows up on my calendar instead of the shared one and the invitation does not get received by the invitee. Can you assist with this and do you think it is somehow due to my exchanger server setup or restrictions? Thank you!
I'm new to VBA.
Do you have an introductory video on it.
The thing be like sQL for my eye and its been a while I even did that
Fantastic video. Is there any way to send this invites from a created calendar different from my default one? What I want is this events to show on my created calendar named "schedules" as opposed to my default "calendar". Thanks again for this content!
Is there any code of setting the meeting options like bypass the lobby for everyone and whi present the screen for everyone
Is there a way to make it pull time zones from a cell in the spreadsheet? I have a list of items I want added to my calendar but they're all in local times and I want to add them in my time zone. If you can select the time zone from the drop down when you manually add an appointment, there must be a way to do this? I see there's a .StartTimeZone option, but I keep getting an error. Any help would be appreciated.
Hello Ruben, This is a very good question. There many options to the timezone "String" at least on my windows system. About 140 of them, but you have some standard short ones like "UTC","UTC-11","UTC-09","UTC+12","UTC+13" which should work if you do a test. Maybe I should ask what time zone you are interested exactly
.StartTimeZone = Outlook.Application.TimeZones("UTC+13")
You can try that and provide feedback. But we can discuss extensively on the other options after now
Thanks. Your video is so useful. I still have a little problem. When adding "Send" action in the code but the meeting can't be automatically send. I don't know why. Can u help me?
So you use a .send rather than a .display and what happens exactly, maybe that will help us troubleshoot
can you please share the VBA code to book meeting room. my requirement is like once after enter the Meeting start and end time, it has to show available meeting room . once i click on available meeting room which i am like then it has to be booked automatically and show in my meeting calander. let me know if that possible
I am completely new to this but your videos are extremely helpful. I really appreciate it. I keeps getting an error message for the line
Set setupsht = Worksheets("Setup")
Not really sure why. The merror message is "Subscript out of range."
Help please. Thank you.
Thanks for the feedback. Do you have a worksheet named "Setup" in the workbook?
@@ExcelMoments ah, I see. Got it. I have a different name. Thank you so much!
@@nhivu7096 You are welcome
Hi, great video! How and where would I add a function to make the meeting invites send from a different email account? I have a shared account and I would like the invites to be sent from that email address so that the rest of the team can edit them if needs be
Hello Melem, Thanks for your comment and question. I have this demonstrated in ua-cam.com/video/4LKw_C4gsZ8/v-deo.html.
The code should look something like this
Dim Acctused As Account
Set Acctused = OutApp.Session.Accounts("victor@excelmoments.com")
.SendUsingAccount = Acctused
Thanks a lot for this video, it was very helpful. Can you also let us know how to create a MS Teams invite?
Yea. I was actually planning to show that, so, yes, I would try put up a video on that
Now you have it here ua-cam.com/video/bhbPM7FbyhQ/v-deo.html
Hello, this has been very informative and easy to understand. As you have mentioned in the video, it's helpful for recruiters to send multiple invites to candidates. I have a template for the interview and i would want the template to be integrated in the invite. Can you please help me. ?
If your template is similar to mine, it should not be a problem, just change the codes to reflect the appropriate column. In any case, you can reach me on vicmomentum"gmail.com
@@ExcelMoments Hello Victor, thanks much for your response. I have sent you an e-mail regarding my concern.
Thank you for your explanation and code. I enjoyed working through this! But, when I run the code I have trouble with both Display and Send. I tested with 4 lines and 4 meetings going to the Required Attendee for 4 different dates and 4 different subjects. When I run the code with Display the expected meeting request window pops up with the correct info, but for the second meeting request the first one is overwritten, and so on until the result is only the final meeting request in the window (I ran for 4 meetings and expected 4 meeting requests but ended up with only one). When I ran this with Send I could see the 4 meeting requests in the recipient mailbox but the first 3 were canceled and only the last one presented the recipient the button to accept the request. It appears that the meeting requests were interpreted as changes to the prior meeting and were canceled. Any suggestions?
did you been found an anwser?
Unfortunately not
Option Explicit
Sub SendInviteToMultiple()
Dim OutApp As Outlook.Application, Outmeet As Outlook.AppointmentItem
Dim I As Long, setupsht As Worksheet
Dim olNs As Outlook.Namespace
Dim CalFolder As Outlook.MAPIFolder
Dim response As Integer
Set setupsht = Worksheets("Setup")
Set OutApp = Outlook.Application
Set olNs = OutApp.GetNamespace("MAPI")
Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar)
response = MsgBox("Are You Sure?", vbYesNo)
If response = vbNo Then
Exit Sub
End If
For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
Set Outmeet = CalFolder.Items.Add(olAppointmentItem)
If setupsht.Range("E" & I).Value "" Then
With Outmeet
.Subject = setupsht.Range("A" & I).Value & " (" & setupsht.Range("B" & I).Value & ")"
.RequiredAttendees = setupsht.Range("E" & I).Value
.OptionalAttendees = setupsht.Range("F" & I).Value
.Start = setupsht.Range("C" & I).Value
.Duration = setupsht.Range("D" & I).Value
.Importance = olImportanceNormal
.Body = setupsht.Range("A" & I).Value & vbLf & vbLf & _
"(This message was sent automatically by me)"
.MeetingStatus = olMeeting
.ReminderMinutesBeforeStart = 15
.Location = ""
.Display
.Save
.Send
End With
End If
Next I
Set OutApp = Nothing
Set Outmeet = Nothing
End Sub
@@michaelwoodall2717
try this, it work for me :)
If someone doesn't want to use VBA , for emails we can write hyperlink mailto in excel sheet to send email from there , do we have any hyperlink for teams / meeting invite ? please help
Not that i am aware of
Hi ExcelMoments, Im trying to tweak the code with multiple recipients in the excel by column. Unable to do it, And the code shown in video does not populate as meeting rather it shows up as aapointment.
I already typed out a response, but forgot to send. For the meeting/appointment thingy, can you confirm you used olMeeting as the value for the MeetingStatus property. Are you trying to send same meeting to multiple recipients, is that your objective?
@@ExcelMoments Following as I am currently trying to set this up as well. So 1 Teams meeting, All those listed in column B will be required attendees, all those listed in column 3 are optional. Thoughts?
Now will the meetings invites send through excel or do I have to send them one at a time? Thank you!
IHello Michael. Thanks for your comment, though I am not certain what you mean by "Send through excel", but to provide some guidance. The way this is set up, if you have 10 recipients, a separate invite will be sent to each of the recipients but with a single click of a button. So you click once, and 10 invites are sent to 10 people with the respective information.
Hi Victor, fab video! Really helped me with my problem at work. Just one quick query, how can I add multiple Required Attendees using your code? Thanks a lot!
You mean if the required attendees column have more than 1 person per row?. If they are separated by a comma in the cell, Outlook should pick them up as multiple recipients for that invite
@@ExcelMoments Thank you! I can work with this, but is there a way to make it work if I have multiple columns containing attendee emails? Just to save me a little more time.
@@alexlambmusic93 it depends on the scenario, if you have them in multiple columns per row, easiest thing would be to concatenate the addresses in another column using say TEXTJOIN, so that you can simply loop based on that column.
For those who would like to get an idea of how to switch accounts in outlook and decide which account sends the meeting invites, watch my video here ua-cam.com/video/4LKw_C4gsZ8/v-deo.html
Hi thank you for the Video! I am receiving an error when I try to run it. "Compile error: User-defined type not defined" shows up, and highlights the line "OutApp As Outlook.Application". Do you know what the issue here would be?
Did you add a reference to the outlook application from the Visual Basic Editor? i.e Tools>References and then check the box against Microsoft Outlook xx.0
Very nice! Congrats :)
Is it possible to insert a picture with hiperlink in the body?
The Appointment body does not lend itself to easy manipulation, especially because the htmlbody property does not work as it should. If this were an email, it would be much easier. But, never say never
It's really useful. Shall I get this program in word or in comments where i can copy this program instead of writing pls..
Hello, the workbook is linked in the video description. You can download and you have the code in there ---- docs.google.com/spreadsheets/d/1RiCqVbNncZvnYSRTdOtvRgHbdK-mxYc9/edit?usp=sharing&ouid=115481529442131367093&rtpof=true&sd=true
Hi Victor, this is really helpful. I need to create a meeting invite using macros with Bold and Italic templates. Please help me with this
The meeting invite sadly does not lend itself to customization of the body(Bold italics and formatting) as it would with say an Email sent from VBA. Power Automate desktop may be more flexible in this regard , but never say never
Hi, thanks for the awesome content! At helps a lot.
Everything is fine till .Send. It displays perfectly the invitations but when I try to run it with .Send I receive “run-time” error. How can I solve this?
Just a quick question. Are you using both .display and ,send? or you commented out the .display and used just .send?
Awesome tutorial. Can someone please help with recurring meeting syntax, .RecurrenceType = olRecursWeekly and .DayOfWeekMask = olMonday and .PatternStartDate = is giving me syntax errors
For the PatternStartDate you can use something like .PatternStartDate = #4/11/2021#, which part is giving an error, so we can advise appropriately
@@ExcelMoments I looked at your other video, Its working now. Thanks a lot
Hello, this is really helpful video. I created this for my team. Can you please help me in adding the zoom link to this invite?
Sadly, I haven't done that. But it would be worth exploring
Hi, i am having difficulties even though i have followed your codes. When i try running the module there is a complie error that says user-type not defined. May i know how to solve this issue? Thank you!
Can you send a sample of the code as you have it, so I can take a look. On what line of code does the error come up? If you can tell
@@ExcelMoments I followed the same exact code in this tutorial but ended up with a compile error for the second line which reads user-defined type not defined.
@@sherlynlxm6233 Are you sure you have set the reference to outlook and in the correct Project. Thats the major reason why you would get that error
@@ExcelMoments yes i did
hi i am facing the same issue please suggest i have already enabled reference as outlook module
Hello,
Can you please tell how can we use this code to schedule a zoom meeting on outlook. Also I have a set list of attendees in excel and I want my code to go to that sheet and record all the emails and put it in the req attendees
In this case 1 invite with all the members as required attendees? is that what you mean?
@@ExcelMoments yes
@@sakshammittal3456 You could do something like this
.RequiredAttendees = Application.WorksheetFunction.TextJoin(",", True, setupsht.Range("E2:E6"))
Concatenate all the names in the range with say a comma delimiter, that should work or you could concatenate them on the worksheet and then point to that cell in the VBA code
Hi Victor, how can we extract the body of the email from a word document?
You want to pick the body from a word document, is that what you are trying to achieve?
@@ExcelMoments correct.
can be done with different subjects? Like Interview | Name candidate - Name interviewer?
Absolutely, if they sit in different columns on the spreadsheet, you can either concatenate them into one column the way you want it to appear and point VBA to that column or you can do the concatenation in the code, either way, very possible and doable
@@anisvs5347 On which of the lines does it error out? but first things first. Did you add a reference to Microsoft Outlook from the Visual Basic environment, Tools>Reference>Microsoft Outlook xx,0?
Hi! how do change the meeting status from busy to free on the macro?
Do you mean the status of the meeting or your status? if that's something we can change on the UI(whgich i know we can) then we should be able to change it within the code
@@ExcelMoments Hi! Victor, i was able to find out how. It was very simple. Just changing the busystatus to free. All good now. By the way your videos are excellent. In my opinion is the best channel I've seen for excel. Please keep them coming
Hello, can you please tell me how to add From option
Hello Valibhav,,
The code should look something like this
Dim Acctused As Account
Set Acctused = OutApp.Session.Accounts("victor@excelmoments.com")
SendUsingAccount = Acctused
Whatever account you have in there will be what will be used in the From field.
You can see more details here ua-cam.com/video/4LKw_C4gsZ8/v-deo.html
Hi Victor, I really liked your code. I am stuck at one instance. What if there is no data in one of the cells for the start time , how do I ensure the code will skip and go to next cell or if the data is missing in multiple cells and it has to skip till it reaches the next available cell. I would really appreciate if you can help.
A couple of methods to approach it. But what I would suggest is say you add a new column(say column G) with a function like COUNTBLANK or COUNTA to determine whether a column is blank or if some columns are. Then in the code, We can do na IF Cells(I,"G").value>0 Then...... before anything, just so that the code only runs when the cells are all filled. Does that help?
@@ExcelMoments Victor I am not a pro in Coding. I think we are still missing something here. For example in your excel sheet if Najeem & James do not have a start time in column C, how should I code such that it will skip Najeem & James and go to Grace and send out an email.
If you are interested on just skipping those that have missing start times, then you can in the code above, you must have a line in there that looks like ...if cells(I,"C')"" Then...... This means that the lines that follow would only be executed when column C for that row has a value or when there is a start time, so it would skip all rows with missing start time and execute for those that have
@@ExcelMoments Victor, thank you for the comments. It works now for me.
Hi!,
Your videos are super instructive and really well explained!
I wonder if you could help me with this:
I'd like to know if it's possible to have a column with Start Date and other column Start Time and then merge it into member .start . I've been trying to do sth like this:
|| .Start = setupsht.Range("D" & i).Text & setupsht.Range("E" & i).Text || but keeps saying type mismatch, do you know how to solve the problem?
thnaks you in advance.
I found how: .Start = setupsht.Range("D" & i).Text & " " & setupsht.Range("E" & i).Text
now I wonder how to skip the .Attanchments.Add if it's empty
@@carlapujol92 Thanks for your comments and feedback. I am glad you were able to fix the Date and Time issue before I showed up. if for example, you have a column to indicate whether there is an attachment or not, or you have a column where you put the path to the attachment, then you can do something like if cells(i,"P")"" then .attachments.add............. that way it only adds attachment if there is a text in column P(hopefully, it is valid too. But you can explain your scenario more, if this did not answer it
@@ExcelMoments I wrote
If Not IsEmpty(setupsht.Range("I" & I).Value) Then .... and works perfectly fine :D
@@carlapujol92 Alright then. That's good. Welldone
@@ExcelMoments do you know how to set importance or busy from a column?
For example in importance, I have seen that should be something like olHigh, olNormal or olLow.
I've tried to write "ol" & setupsht.Range("A" & I).Value but it returns a mismatch error.
Do you know how to solve it?
hi everytime i press play it is poping up Sub or function not defined
Are you using my workbook as shared? If No, Did you also add a reference to Outlook? If No, that may be the reason some codes are not running
@@ExcelMoments how to add reference to outlook can you please suggest
@@srishtisingh2719 You open the visual basic environment(ALT+F11), then on the menu, Go to tools> References...Scroll down till you see Microsoft Outlookxx.0,e. g 13.0,16.0, this would depend on the version of office you have installed. Check the box beside it, do OK and test the code again
Hi your video is amazing.
Can you help how we can add a signature saved in outlook in meeting invites?
Hi Lokesh. Thanks for your feedback. I should put up a video shortly. But one method can be found on ron de bruin's site. www.rondebruin.nl/win/s1/outlook/signature.htm. I will show a different one
Thank you so much for replying.
The method on the website is for .htmlbody
That is not working in appointment.
@@LokeshSharma-hl8pe I see. So my alternative is to use Application.sendkeys, which is what i intend to do a video on
'Using Send keys
SendKeys "^{END}", True 'Go to the end of the appointment body
SendKeys "~" 'Press Enter to create a line between the body and signature
SendKeys "%", True 'ALT Key
SendKeys "N", True 'N takes you to the insert tab
SendKeys "AS", True 'Signature
SendKeys "{ENTER}", True 'Pressing enter selects the first signature in there
@@ExcelMoments This seems to be not working on my code unfortunately. But thanks again for helping :)
@@LokeshSharma-hl8pe it depends on how you are using it, right? Do you have a signature set up in outlook already. Does it produce an error when you try using it. it would be nice to see your code 'cos it works for me
Hi Victor, I have different calendars in my outlook through which I need to send invites to different calendars. Please help me with that.
Hello Vaibhav, so how many different calendars do you have from which you are sending, and what's the logic for determining which of the invites is sent from a particular account?
@@ExcelMoments I am working in HR field. I need to send interview invite one to the interviewer from organization calendar and other to the interviewee from my personal calendar
@@VaibhavSharma-ki4ud Alright, that's fine. So I take it that you have 2 set of mails to send , 1 to interviewers, 1 to the interviewees. So why not set up two sheets, 1 for each category (having all the details like in this video) and when sending for the interviewers, you specify the account to use like i had shown in a previous response to you, and then for the interviewees a different account. I take it that you have both accounts on your outlook
@@ExcelMoments yes I did the same but showing error
@@VaibhavSharma-ki4ud what error is it showing ? and on what line?
Hello Victor
How can we add recurrence pattern to this code. I want some of my meetings to occur weekly some monthly and other daily..
Hello. Kindly check out my video ua-cam.com/video/mrKq9iqRyBk/v-deo.html where i explained how to add recurrence to an appointment/meeting invite
@@ExcelMoments I did but couldnt understand how I could make it dynamic is there any way in which I could fetch the details from excel columns itself..
If you could help me with a code for that...
@@garima971 You can probably use an IF in VBA which references the values in the cell, so IF(cells(1,"H").value="weekly" Then...do a weekly recurrence, then do an else for daily and monthly, that for me would be the easiest way to set it up
The code looks at the worksheet and on that row figures out what pattern is required and the VBA code already has that, so it knows what to use
Is that something you can set up?
@@ExcelMoments How do I use this with the With RecurrPat attributes
I have put up a video that shows how to add a teams meeting to the invite. please do check it out ua-cam.com/video/bhbPM7FbyhQ/v-deo.html