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
, Outlook , , , , . .
, Excel, Outlook VBA?
.
1. Excel
« », « Microsoft Exchange» , . PowerQuery (, ). .
, , . List.Dates:
, , 21 :
2 . “ ” “” , , “” “ ”. :
*, .
«Subject», «Location» « », , , : , «», — «». « » . PowerQuery :
! “ ”, .
2. Outlook
VBA Outlook. “” Excel VisualBasic:
, :
, « », , «»:
Outlook GetObject("Outlook.Application"). GetObject — VBA. . Microsoft.
. , .
subjectStr = " " - ‘ ()
For Each oAppointmentItem In objNavFolder.Folder.Items
If InStr(oAppointmentItem.Subject, subjectStr) > 0 Then
Set N = oAppointmentItem
N.Copy ‘
N.Display
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
. , , , :
,
3.
, :
, :
, , , . .
, . VBA , . , , .
, . , Excel. , , Outlook. Excel .
, , .