How to simplify routine tasks using VBA

Hello, Habr! I am Sergey Chebarev, an analyst in the data visualization team of Severstal-Infocom, and today I will tell you how you can automate your work routine using VBA and Office applications. VBA (Visual Basic for Applications) is a simplified version of Visual Basic, built into many Microsoft Office products, and accordingly, these products can be friends with each other.

The most obvious application of VBA (which is still often and not quite correctly called macros) is the automation of typical tasks with many repetitive steps that take a long time to do with your hands, boring and fraught with fatigue errors. Let’s consider one of these tasks.

Create events in Outlook via Excel

So, I have a task: I need to create events in the calendar and send invitations to users with data to log into the terminal server.

It sounds not very difficult at first glance, but:

  • Invitations must be sent from a shared mailbox

  • The body of the invitation must contain the corporate design style

  • There is a limit on the number of users on the terminal server per day, so you need to keep track of the number of events

Accordingly, it is not enough just to open Outlook with a calendar, before creating an event, you need to check whether this invitation will exceed the limit for the current day, set a time range, transfer the body of the letter from a file or from a previous invitation. All this requires a large number of actions and takes a noticeable amount of time.

How can you simplify this routine using Excel, Outlook and VBA? I tell

Let’s describe the steps in steps.

1. Create an Excel workbook and get data from the calendar

Using the “Create Request” tool, select the source “From Microsoft Exchange” and enter the mailbox where the events are held. After connecting, PowerQuery opens with data from mail (messages, calendar, etc.). We only need the calendar data.

All created events are now visible in the table, but we also need to see the days where there are no events yet. To do this, we create a new request and call the List.Dates function:

She will create a list of days for us, starting from today, 21 days ahead:

Now let’s combine 2 request data into one. To do this, we translate the sheet with dates into a table using the “To table” tool on the “conversion” tab and go to the query, where we have collected all the invitations from the mail, after which, on the “Home” tab, select the “Add queries” tool. We select the previously created table with dates and combine:

* It is important that when joining, the columns in the tables must have the same names.

We create additional columns “Subject”, “Location” and “Reservation status”, which will carry information about the subject of the event, the location of the event and from which request the data came from: if from the calendar, then “Reservation”, if from the called function – “Free”. We make a summary on the “Booking status” column to determine the number of free windows by day and assign a status for booking. Close PowerQuery and get the following table:

Excellent! Having created the column “Windows are free”, we have freed ourselves from counting events and see the possibility of booking.

2. Creating events in Outlook

Now we need to write a VBA script to create events in Outlook. To do this, go to the “Developer” tab in Excel and open VisualBasic:

In the window that opens, select the worksheet where the summary table for events is located:

We will run the code using a double click on the rows of the “Availability of booking” column, and provided that the status of the booking is “Book”:

To work with Outlook, we will use the GetObject (“Outlook.Application”) function. GetObject is a function for VBA applications. It is used to attach to launched objects. More details on the syntax of the function can be found For more information, see Microsoft documentation.
To maintain corporate style, the code looks for the previous invitation and copies the body of the letter from it. Next, a new invitation is created, and all the necessary information is entered into it.

subjectStr = "Шаблон приглашения" -  ‘Поиск заданного приглашения(шаблона)
    For Each oAppointmentItem In objNavFolder.Folder.Items
    Set P = oAppointmentItem
        If InStr(oAppointmentItem.Subject, subjectStr) > 0 Then
            Set N = oAppointmentItem
            N.Copy ‘Делаем копию шаблона
            N.Location = Cells(b, 2) ‘место  события из ячейки таблицы
            N.Subject = Cells(b, 1) ‘название события из ячейки таблицы
            N.Start = Cells(b, 3) + 0.33333 ‘время начала события
            N.End = Cells(b, 3) + 0.999 ‘время окончания события
            Exit For
        End If
    Next oAppointmentItem

Let’s test it. Open the file, look for a line with the date that interests us, and double-click on the booking status, run the script:

The invitation for the day we have chosen with the required subject and body of the letter has been successfully created, it remains only to indicate the recipient

3. Updating data

To update the data when the book is opened, save the script to the book itself:

In addition, turn off background refresh in the table connection properties:

Let’s sum up

So, we have simplified the creation of the same type of events in the calendar, and we are also sure that we will not exceed the limit of events per day. This allowed us to get rid of routine work and reduced the likelihood of human error.

Of course, ideally, our learning platform could be refined and the booking process fully automated. But VBA scripts are good because they can significantly simplify life without developing and modifying existing systems. They will come in handy where a script is written in a few hours, and it takes weeks or months to fully automate and refine existing solutions.

Interestingly, decisions like this may not always be obvious. For example, this task did not initially use Excel at all. It seems that since you need to create events, you only need to use Outlook. But Excel helps you collect and process data from a variety of sources.

If you are interested in similar ways to get rid of the routine, write comments, I will talk about other cases in the following articles.

Similar Posts

Leave a Reply Cancel reply