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.
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.
Die Excel-Tabelle zur Erstellung von Outlook-Terminen habe ich wie folgt aufgebaut:
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).ValueSet 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 WithSet 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.
Hallo Roman,
vielen Dank für den tollen Artikel. Gibt es die fertige Excel Datei zum Download? Ich möchte ungern den Screenshot ins Excel tippen müssen.
Vielen Dank,
Gruß,
Martin Fröhner
Hallo Martin
Danke für Deinen Kommentar. Ich habe die Datei, auf welcher dieser Post aufbaut, soeben abgelegt unter:
https://www.excelblog.ch/media/Outlook-Termin%20erstellen.xlsm
Da hast Du einerseits die Excel-Vorlage und im Hintergrund auch das Makro. Gehe hierzu mit einem Rechtsklick auf ein Tabellenblatt auf "Code anzeigen" und öffne im VBA-Fenster, das Dir dann angezeigt wird, das Modul1, um den Code zu sehen.
Liebe Grüsse
Roman
Hallo Roman
Vielen Dank für das tolle Tool. Das erleichtert die arbeit bei vielen Terminen enorm.
Trotzdem die Frage eines Laien, wie kann ich bei mehreren Kalendern den angeben den ich benötige? Ich finde unter dem link irgendwie nicht das richtige zu dem Thema.
Könntest du mir da helfen. Danke
Hallo Popsy
Bei der Recherche bin ich auf folgenden Stackoverflow-Post gestossen:
https://stackoverflow.com/questions/26736236/create-outlook-appointment-in-specific-calendar-from-excel
Die Outlook-Termine werden aus deinem Standard-Ordner (Default) gesendet. Die Antwort aus dem obigen Link habe ich nicht getestet, es deutet aber darauf hin, als müsstest Du den "Outlook.Folder" definieren.
Dabei wird in der Antwort auf Stackoverflow einerseits die Variable "objfolder" definiert:
Dim objfolder As Outlook.Folder
... und etwas später dann darauf referenziert mit:
Set objfolder = myNamespace.GetSharedDefaultFolder(myRecipient, olFolderCalendar)
Dies setzt den Ordner, in dem der Termin erstellt wird. Kannst Du mit diesen Angaben etwas erarbeiten und auf Deine Umgebung zuschneiden?
Beste Grüsse
Roman
Hallo,
das Einstellen eines Termin klappte sofort - Danke dafür !
Kann ich aus Excel heraus auch einen Termin in Outlook löschen ?
VG
Peter
Hallo Peter
Ein Termin zu erstellen ist auf jeden Fall einfacher, da es hier lediglich um die Generierung eines Objekts geht. Was ist der Unterschied zum "Löschen" eines Termins? Du benötigst einen Parameter, um den gewünschten Termin zu identifizieren. Wie hast Du Dir vorgestellt, einen Termin zu löschen respektive welche Angabe hättest Du verfügbar, aufgrund derer Du einen Termin modifizieren/löschen könntest? Hast Du eine Art Journal oder eine Übersicht von Terminen, das als Basis dienen kann?
Liebe Grüsse
Roman
Hallo Roman,
ich möchte nur einen einzigen Terminlöschen.
Dafür habe ich das Datum (aus einer Excel-Zelle), den Betreff (Subject) und den Ort (Location):
Set Appoint = OL.CreateItem(olAppointmentItem)
With Appoint
.Subject = Subject
.start = StartTime
.End = EndTime
.Location = Location
.AllDayEvent = False
.Body = Greeting & Chr(10) & Body & Chr(10) & Chr(10) & Finish
.Categories = strKategorie
.ReminderMinutesBeforeStart = 10
.Save
End With
Hab' noch eine Frage zum Einstellen eines Termin in den Outlook-Kalender:
Wie kann ich den Termin direkt (über VBA) als "Privat" markieren ?
Hallo Peter
Um einen Termin als "Privat" zu markieren, setze einfach im Bereich zwischen "With Appoint" und "End With" (allerdings vor .Save!) diese zusätzliche Eigenschaft:
.Sensitivity = olPrivate
Liebe Grüsse
Roman
Hallo Roman,
der Parameter hat mir gefehlt, klappt !
Vielen Danke
Peter
Hallo Roman,
Finde es eine tolle Sache genau das haben wir gebaucht!
Kannst Du mi sagen wie ich mit deinem Skript, den Termin an dem Ansprechpartner senden kann?
Werde im Internen nicht schlau....
Gruss
Alessandro
Hallo Alessandro
Um Empfänger hinzuzufügen, müsstest Du weitere Parameter einfügen, wobei Du hier zusätzlich spezifizieren kannst:
Verwende beispielsweise die nachfolgende Angabe, um allgemein Empfänger hinzuzufügen:
.Recipients.Add ("E-Mail1; E-Mail2; ...")
Du kannst allerdings auch unterscheiden zwischen fixen und optionalen Teilnehmern:
.RequiredAttendees = "E-Mail1"
.OptionalAttendees = "E-Mail2"
Füge diese Parameter einfach zwischen die Zeilen "With Appoint" und "End With" ein, wie das z.B. für das Subject gemacht wird.
Liebe Grüsse
Roman
Ciao Roman,
leider klappt das nicht ganz bei mir, trotz diesen Parameter, kriege nur ich einen Termin über.
Könntest Du mir den VBA Text umschreiben und senden? Ziel ist es das es einen Termin an der Person der bei Ansprechpartner drin ist, gesendet wird.
Hallo Alessandro
Danke für Deine Rückmeldung. Ich sehe das Problem. Der Termin ist in ein Meeting umzuwandeln (.MeetingStatus = olMeeting). Danach kannst Du die Empfänger hinzufügen.
Als .Recipients.Add (Recipient) füge ich den Wert aus der Zelle B3 ein
- z.B. bei einer vorgängigen Definition wie im Initialbeitrag oder via:
Recipient = Range("B3").Value)
Dies wäre der gesamte Code-Abschnitt:
Set Appoint = OL.CreateItem(olAppointmentItem)
With Appoint
.MeetingStatus = olMeeting
.Recipients.Add (Recipient)
.Subject = Subject
.Start = StartTime
.End = EndTime
.Location = Location
.AllDayEvent = False
.Body = "Dein Termin-Text hier..."
.Display
End With
Set OL = Nothing
Füge anstelle von .Display einfach .Save ein, dann wird der Termin direkt verschickt.
Beste Grüsse
Roman
Wenn ich den Code übernehme und auf meine Bedürfnisse anpasse, steht in DayMeeting richtigerweise 30.03.2022, der Termin wird aber für den 30.12.1899 angelegt. Kann mir bitte jemand helfen ?
Ich ziehe meine Frage zurück. Ich habe meinen Fehler gefunden 🙂
Hi Markus
Super, dass es geklappt hat!
Liebe Grüsse
Roman
Danke Roman. Dein Code macht mir die Arbeit um ein vielfaches einfacher.
Hallo Roman,
das Script ist echt super. Ich bin auf der Suche nach einer Möglichkeit, einen Hyperlink in den Body einzufügen.
Für E-Mails funktioniert das mit .htmlbody, aber für den Kalendereintrag mag das nicht funktionieren. Siehst Du da einen Weg?
Habe meinen Fehler gefunden. Da meine Quelle einige Leerzeichen in der Form %20 und andere als %C2%A0 geliefert hat, ist der Link immer kaputt gewesen. durch eine WECHSELN() Funktion habe ich das ausmerzen können und mit HYPERLINK() eine Zelle mit dem kompletten Link generiert, welcher dann auch im Body des Termins anklickbar ist. Vielleicht hilft es ja noch jemandem.
Hallo Marc
Danke für Deinen Kommentar und super, dass Du eine Lösung gefunden hast und vielen Dank fürs Teilen hier!
Liebe Grüsse
Roman
Hallo Zusammen,
vielen Dank für diesen Wertvollen Beitrag, das kann einem unglaublich viel Zeit einsparen.
Ich wollte noch eine Sache fragen.
Ist es möglich auch automatisch einen Anhang hinzuzufügen basierend auf einer gegebenen URL?
Vielen Dank für Eure Hilfe im Voraus!
Hallo Siemen
Danke für Deinen Kommentar!
Du kannst einen oder mehrere Anhänge hinzufügen. Weise dazu vor dem Mail/Termin-Versand Dein Attachment (Pfad) einer Variablen zu, z.B. so:
pathAttachment = "C:\Users\...\MeinAttachment.pdf"
Daraufhin fügst Du innerhalb des Bereichs "With Appoint" und "End With" den folgenden Parameter hinzu:
.Attachments.Add (pathAttachment)
Falls Du weitere Attachments einfügen möchtest, wäre der einfachste Weg (es gibt natürlich noch andere, "schönere" Lösungen):
Weise oberhalb im Code zudem den Pfad einer Variablen "pathAttachment2" zu sowie unterhalb im Code dann analog oben einfach zusätzlich noch ".Attachments.Add (pathAttachment2)"
Viel Erfolg!
Liebe Grüsse
Roman
Hallo Roman,
danke für deine Schnelle Antwort.
Es hat funktioniert!
Ich habe die URL von einer Zelle Abhängig gemacht.
Dabei wird ein Fehler ausgegeben, wenn die Datei in dem Pfad nicht Vorhanden ist.
Kann man es auch einstellen, dass es trotzdem durchläuft?
Schöne Grüße!
Hi Siemen
Ja klar, das geht ebenfalls! Um zu prüfen, ob Deine Datei tatsächlich existiert, füge folgende Zeilen in Deinen Code ein:
Definiere einerseits die Variable "strFileExists":
Dim strFileExists As String
(ist optional, wir haben der Einfachheit halber nicht alle Variablen deklariert)
Führe nach Deiner Attachment-Zuweisung "pathAttachment = "C:\..." folgende Zeile ein:
strFileExists = Dir(pathAttachment)
Das wäre mal für ein Attachment; ich gehe nicht auf "pathAttachment2" ein.
Führe dann dort, wo Du bereits ".Attachments.Add (pathAttachment)" stehen hast einfach diesen Block ein:
If strFileExists = "" Then
'Nothing
Else
.Attachments.Add (pathAttachment)
End If
Sollte die Datei nicht existieren, gibt Dir "Dir(pathAttachment)" nämlich einen leeren-String zurück ("") - da würde bei der Prüfung oben nichts passieren - ein Outlook-Termin wird ohne Anhang versendet. In allen anderen Fällen wir das Attachment beigelegt.
Liebe Grüsse & frohe Ostern
Roman
Hallo Roman
Vielen Dank für die tolle Beschreibung. Damit kann viel Zeit gespart werden!
Als totaler Laie habe ich die Frage, wie ich einen spezifischen Kalender auswähle, wenn ich mehrere E-Mail-Konten im Outlook hinterlegt habe? (ich möchte die Termine im Teamkalender unter der allgemeinen Email-Adresse hinterlegen und nicht auf dem persönlichen Kalender bzw. auf meinem persönlichen Mailaccount). Hab folgendes versucht, aber leider klappts nicht:
Sub Outlook_Termin()
Dim oApp As New Outlook.Application
Dim outmeet As Outlook.AppointmentItem
Dim objfolder As Outlook.Folder
Set geburtsgslistesht = Worksheets("Test")
For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
Set outapp = Outlook.Application
Set outmeet = outapp.CreateItem(olAppointmentItem)
Set objfolder = myNamespace.GetSharedDefaultFolder(myRecipient, olFolderCalendar)
With outmeet
.Subject = "Test"
.Start = geburtsgslistesht.Range("N" & I).Value
.Duration = 30
.Body = "Test"
.Display
End With
Next
Set outapp = Nothing
Set outmeet = Nothing
End Sub
Liebe Grüsse und vielen vielen Dank für die Hilfe.