Outlook-Termine aus Excel erstellen

Mit VBA können nicht nur in Excel selbst Arbeitsschritte automatisiert werden, sondern es lassen sich auch andere MS Office-Produkte wie Outlook anbinden. Dieser Beitrag illustriert, wie aus einer Excel-Tabelle basierend auf einzelnen Zellinhalten ein Outlook-Termin erstellt werden kann.

Um die Funktionalitäten zu erschliessen, ist die Microsoft Outlook Object Library in VBA zu aktivieren. Gehe dafür wie folgt vor:
Öffne die VBA-Umgebung, indem Du entweder einen Rechtsklick auf ein bestehendes Tabellenblatt machst → "Code anzeigen..."oder mit der Tastenkombination Alt + F11. Unter "Extras" findest Du dann "Verweise", wie im Screenshot unten illustriert.

VBAVerweise

Suche da nach "Microsoft Outlook 16.0 Object Library" und aktiviere diesen Eintrag. Beachte: Die Version kann abhängig von Deiner MS Office Installation sein.

MOfficeObjLibrary

Die Excel-Tabelle zur Erstellung von Outlook-Terminen habe ich wie folgt aufgebaut:
OutlookTerminFile

Dem Benutzer stehen im oberen Bereich Eingabefelder zur Verfügung, welche sowohl als Steuerungsfelder für den effektiven Outlook-Termin dienen, aber auch die Texte unterhalb wie die "Anschrift" beeinflussen (Du = Lieber/Liebe; Sie = Sehr geehrter Herr/sehr geehrte Frau). Dies kann mit Wenn-Dann-Formeln erreicht werden und den Ablauf weiter optimieren, ist aber nicht zwingend. Zudem habe ich drei Sektionen (Einleitung - A; Hauptteil - B; Abschluss - C) in den Zellen 21, 23 und 25 eingepflegt, um die Email weiter zu strukturieren. Sämtliche Angaben werden durch das Makro weiterverarbeitet und an die entsprechenden Stellen des Termins als Variablen weitergereicht.

Lege nun in der VBA-Umgebung (siehe oben) ein neues Modul an und setze folgenden Beispielcode ein:

Sub TerminErstellen()

Dim OL As Outlook.Application, Appoint As Outlook.AppointmentItem, ES As Worksheet, WB As Workbook

Set WB = ThisWorkbook
Set TP = WB.Sheets("Terminplanung")
Set OL = New Outlook.Application

'Load Variables
Recipient = TP.Cells(3, 2).Value
DayMeeting = TP.Cells(9, 2).Value
StartTime = TP.Cells(10, 6).Value
EndTime = TP.Cells(11, 6).Value
Location = TP.Cells(12, 2).Value
Project = TP.Cells(13, 2).Value
Subject = TP.Cells(17, 2).Value
Greeting = TP.Cells(19, 2).Value
BodyA = TP.Cells(21, 2).Value
BodyB = TP.Cells(23, 2).Value
BodyC = TP.Cells(25, 2).Value
FinishA = TP.Cells(27, 2).Value
FinishB = TP.Cells(28, 2).Value

Set Appoint = OL.CreateItem(olAppointmentItem)
With Appoint
.Subject = Subject
.Start = StartTime
.End = EndTime
.Location = Location
.AllDayEvent = False
.Body = Greeting & Chr(10) & Chr(10) & BodyA & Chr(10) & Chr(10) & BodyB & Chr(10) & Chr(10) & BodyC & Chr(10) & Chr(10) & FinishA & Chr(10) & FinishB
.Save
End With

Set OL = Nothing

End Sub

Zuoberst werden die Objekte bestimmt und die Variablen geladen (das Tabellenblatt "Terminplanung" wird als "TP" deklariert, die Inhalte für beispielsweise den Empfänger oder den Betreff werden aus den Zellen B3 respektive B17 ausgelesen).
Im unteren Bereich (ab "Set Appoint") wird das effektive Outlook-Objekt - der Termin - erstellt, mit sämtlichen Angaben. Beachte: Hier ist aktuell kein Empfänger eingefügt, was für Testzwecke dazu führt, dass der Termin in Deinem eigenen Kalender angelegt wird. Du findest zu diesem AppointItem-Objekt weitere Eigenschaften unter diesem Link:

https://docs.microsoft.com/en-us/office/vba/api/outlook.appointmentitem#properties

Unter .Body siehst Du eine Zusammensetzung aus diversen Elementen. Mit "Chr(10)" werden Zeilenumbrüche eingefügt, um die Email besser zu strukturieren, das Resultat ist in der letzten Abbildung in diesem Blog ersichtlich. Die beiden Abbildungen unten illustrieren den erstellten Termin mit diesem Makro - sowohl für die Kalenderübersicht als auch wie erwähnt die zusätzlichen Informationen in der Email selbst.

OutlookTerminOverview

OutlookTerminDetails

If-Statement mit Platzhalter & Like-Operator (VBA)

Bei einer Suche - beispielsweise auf Google - kann mit Platzhaltern gearbeitet werden. Das Fragezeichen (?) ersetzt dabei ein einzelnes Zeichen, das Sternchen (*) ist ein Platzhalter/eine Wildcard für eine beliebige Zeichenfolge vor, innerhalb oder nach dem Suchbegriff. Verwendet man in VBA ein If-Statement, können ebenfalls solche Platzhalter zur Anwendung gebracht werden. Nehmen wir an, basierend auf einem User-Input im Format "Qx-JAHR" (bspw. Q2-2019) soll eine bestimmte Prozedur für eben dieses Quartal gesteuert werden. Normalerweise ist ein If-Statement oder besser gesagt ein If-Then-Else-Statement wie folgt aufgebaut:

If MeinBegriff = "Q2-2019" Then
Weiterer Code für diesen Fall

ElseIf Weitere Prüfungen
Weiterer Code

End If

Im obigen Fall wird Bei "MeinBegriff" handelt es sich um eine Variable, die vorhin in der Prozedur initialisiert wird. Sie kann das Resultat eines User-Inputs sein oder sich auf einen bestimmten Zellwert beziehen. Generell gilt jedoch: Da sich die Jahreszahl über die Dauer verändern wird, benötigen wir eine dynamische Variante mit Platzhaltern im Format "Q2-20*". Das Gleichheitszeichen (=) wird Operator genannt. Weitere Möglichkeiten sind "<>", ">" oder "<=" etc. Desweiteren gibt es den "Like"-Operator, den wir in diesem Fall verwenden.

Der Code für ein If-Statement mit "Like-Operator" lautet:

If MeinBegriff Like "Q1-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q2-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q3-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q4-20*" Then
Weiterer Code für diesen Fall

End If

Wenn der Wert der Variable "MeinBegriff" also ähnlich (like) ist, wird der entsprechende Fall ausgeübt.

Eigene Funktionen erstellen (Meter in Meilen & Celsius in Fahrenheit)

Eine Vielzahl an Funktionen und Formeln sind bereits standardmässig in Excel verfügbar. Dem Standarduser genügt dieses Angebot grundsätzlich, doch können auch da eigene Funktionen den Umgang mit Excel erleichtern. Dieser Blog zeigt auf, wie benutzerdefinierte Funktionen und Formeln angelegt werden können, die danach jederzeit verfügbar sind. Als Beispiel werden wir hier Meter in Meilen sowie Grad Celsius in Fahrenheit umwandeln.

Meter in Meilen

Die erste Funktion, die angelegt werden soll, ist die Umwandlung von Meter in Meilen (eine englische Meile beträgt 1'609.344 Meter). Öffne hierzu eine neue Arbeitsmappe und gehe in die VBA-Umgebung (Alt + F11 oder Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite befindet sich die Projekt-Übersicht - dort sind alle geöffneten Excel-Dateien zu sehen. Mittels Rechtsklick auf die neue Arbeitsmappe lässt sich ein Modul einfügen, wo die Funktion eingepflegt werden kann:

NeuesModulEinfuegen

Im angelegten Modul1 wird nun die Funktion eingetragen. Für die Umrechnung von Meter in Meilen gilt folgender Code:

Public Function MeterInMeilen(ByVal X As Double)

MeterInMeilen = X / 1609.344

End Function

Die Formel nennen wir "MeterInMeilen". Achte bei der Vergabe der Namen darauf, dass diese nicht bereits vorhanden sind. In unserer Excel-Datei kann alsdann mit der Umwandlung begonnen werden. Die Funktion findet sich unter sämtlichen anderen - gebe in Excel einfach "=M" ein und die Vorschläge erscheinen in einer Auswahlliste, dort ist auch "MeterInMeilen" aufgeführt:

EigeneFunktionenAuswahl

Um die Umwandlung der Massangaben zu veranschaulichen, habe ich eine kleine Tabelle angelegt:

MeterInMeilen

In Zelle "B3" sind 1000 Meter eingetragen, dieser Wert kann selbst gewählt werden. In Zelle "C3" kommt unsere benutzerdefinierte Funktion zum Einsatz. Füge da nun die folgende Formel ein:

=MeterInMeilen(B3)

Ein simpler Bezug auf die Zelle "B3" reicht bereits aus, um die Meterangabe in Meilen zu konvertieren. Uns interessiert zudem, wie wir die Rückkonvertierung vornehmen können. Dazu wird folgender Programmcode eingetragen, um die Funktion "MeilenInMeter" anzulegen:

Public Function MeilenInMeter(ByVal X As Double)

MeilenInMeter = X * 1609.344

End Function

Diese Zeilen können direkt unterhalb von "End Function" der ersten Funktion in Modul1 eingetragen werden. In der Excel-Beispieltabelle kann für die Meter-Rückkonvertierung die neue Formel eingegeben werden:

=MeilenInMeter(C3)

Das Resultat davon ist wiederum exakt 1'000 Meter.

Celsius in Fahrenheit

Analog der Konvertierung oben soll nun zudem Grad Celsius in Fahrenheit umgewandelt werden können. Folgender Code erfüllt diese Anforderung:

Public Function CelsiusInFahrenheit(ByVal X As Double)

CelsiusInFahrenheit= X * 9 / 5 + 32

End Function

Wiederum als Beispieltabelle in Excel wird in Spalte C die Berechnung von diversen Temperaturangaben vorgenommen.

CelsiusInFahrenheit

Die angegebenen Werte sind korrekt, man könnte zudem die Dezimalstellen einblenden (beispielsweise 22 Grad Celsius entsprechen 71.6 Grad Fahrenheit). Die angelegte Funktion kann damit ebenfalls umgehen, da der Variablentyp "Double" gewählt wurde.

Auf die Rückkonvertierung wird hier nicht eingegangen, wir lassen dies als mögliche "Übungsaufgabe" offen. Bei Fragen kann stets die Kommentar-Sektion verwendet werden.

Zuletzt ist anzumerken, dass neben komplett neuen Funktionen wie den oben gezeigten Umwandlungs-Formeln auch bestehende Standard-Formeln individuell optimiert werden könnten. Ein Beispiel ist der SVERWEIS, bei dem Benutzer je nach dem immer wieder Probleme bekunden - bei dem Verweis gibt es diverse Fehlerursachen. Mit einer eigenen Funktion (z.B. analog der INDEX-VERGLEICH-Kombination) kann der SVERWEIS benutzerfreundlicher gestaltet werden und zugleich auch Daten links von dem Suchkriterium ausgeben.