This page has 2 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Use VBA to create Microsoft Team meetings
Did you know that you can create Microsoft Team meetings using VBA in Outlook? This blog shows you how!

Posted by Sam Lowrie on 26 June 2020

VBA Outlook Teams Meeting

Here at Wise Owl we have started to use Microsoft Teams for our online training courses. This means we are producing a lot of emails and meetings for courses and pre-course testing.

Wise Owl Training Outlook VBA Teams Meeting

I can't be the only one who is sick of having to click this darn button manually?

There must be a way of automating this process, rather than having to copy and paste all of the details in for each client. When I searched for help online however I hit a brick wall - Teams can't be referenced in VBA.

Here's the sort of information that we have for a course booking - how to turn this into a Teams meeting programmatically?

Typical email generated by our system with the information that I need to put into teams.

What I did notice was when you create an Outlook meeting there is the option (if Teams is installed) to add a Teams meeting:

With this button I won't need to reference Teams with my VBA; instead I can stick to Outlook!

The solution

Create the meeting in Outlook using the VBA following code, populating the fields you need in the meeting (there's a full list of the methods and properties for a meeting here):

Sub new_teams_meeting

'Create place to store meeting

Dim newmeeting As Outlook.AppointmentItem

Dim meetingparticipants As Outlook.Recipient

'Creates a new calendar appointment

Set newmeeting = Application.CreateItem(olAppointmentItem)

'Change from calendar to meeting

newmeeting.MeetingStatus = olMeeting

'Set the email subject as value in class

newmeeting.Subject = "Subject"

'Set start date/time

newmeeting.Start = #Start date and time#

'Set end date/time

newmeeting.End #End date and time#

'Add target email

Set meetingparticipants = ListOfEmails

'Set meeting as required attendance or optional

meetingparticipants.Type = olRequired

newmeeting.Body = "Set the body of the email"

'Show the meeting

newmeeting.Display

All this is fairly standard, but now comes the part I couldn't figure out: how to add the Teams meeting and a signature. The Sendkeys command tells Outlook to act as if you had pushed those buttons on the keyboard:

'Switch on tab keyboard short cuts

SendKeys "{F10}", True

'Switch to ribbon shortcuts

SendKeys "H", True

'Hit the Microsoft teams meetings button, requires teams to be installed

SendKeys "TM", True

'Now to add signature: Switch to meeting location button

SendKeys "{Tab}", True

'Switch to email body

SendKeys "{Tab}", True

'Highlight all the text

SendKeys "^+{END}", True

'Go to end of text so that the signature is at the bottom

SendKeys "{END}", True

'turn on tab shortcuts

SendKeys "{F10}", True

'select the Insert tab

SendKeys "N", True

'Open the signature drop down

SendKeys "AS", True

'Select first signature and close signature list

SendKeys "{ENTER}"

End Sub

One of the nice things about this is while a link to the meeting is generated, if you cancel the meeting won't be saved!

An example of what you might get from us when joining an online course (well, maybe with a few more details on it).

Thanks for reading and I hope this saves you some of the time it cost me!

Some other pages relevant to the above blog include:

This blog has 2 threads

Add a new post

From:	Shantanu
When:	09 Jun 21 at 11:40

How can we copy the Teams meeting link and paste it in an excel file after creating the Teams invite?

From:	SamLowrie
When:	28 Jun 21 at 11:04

Hi Shantanu,

Sorry for the delay.

Assuming you have the Excel library enabled add this to the code:

Dim xl As Excel.Application 'can store an excel application

Dim wb As Excel.Workbook 'Can store a workbook
Dim ws As Excel.Worksheet 'Can store a worksheet

Set xl = New Excel.Application 'create a new excel instance
Set wb = xl.Workbooks.Add 'create a workbook in excel
Set ws = wb.Worksheets(1) 'store first worksheet in workbook

ws.Range("A1").value = newmeeting.Body 'copy the body of the email (which has the teams link)

wb.SaveAs "C:\Users\Shantanu\Desktop\MeetingLink" 'save the workbook to your desktop

wb.Close 'close the excel instance

All the best,

Sam

From:	Madjry
When:	20 Oct 21 at 16:34

Hi SamLowrie

I am trying to save the Teams link to an excel sheet but its not working

it only shows the string assigned to mewmeting.Body..

exemplet if i assign "test" to newmeeting.Body

the value in my excel sheet shows "test" only..

i am sending bulk emails to my colleagues and i need to share a teams meeting link in email body so they can attend a meeting each fridays if they decide to

From:	JohnyDoe
When:	11 Feb 21 at 23:29

Thank you for the tip.

I don't like at the format of the MS-Teams invitation and I would like to change it. I'm not capable of doing this in Outlook, but would have been in Word. In Word I would use the following code:

   Sub MaTeams_Meeting_Format

    Selection.MoveDown Unit:=wdScreen, Count:=1
    Selection.MoveUp Unit:=wdLine, Count:=6, Extend:=wdExtend

    With Selection.Font
        Selection.Font.Name = "Arial"
        .Color = -wdColorBrightGreen
    End With

    With Selection.Style
        .NoSpaceBetweenParagraphsOfSameStyle = True
    End With

End Sub

How can I combine your code to add the MS-Teams invite and format it as per above?

From:	tdalon
When:	06 Feb 23 at 08:02

You can then copy/paste from Word to Outlook as explained here.

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

VBA Outlook Teams Meeting

The solution

Head office

London

Manchester