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 Roman, benötige Hilfe!
Wie kann ich dich erreichen?
Hi Mangold
Danke für Deine Nachricht. Möchtest Du es via Kontaktformular hier auf der Website versuchen? Danke Dir!
https://excelblog.ch/kontakt/
Liebe 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.
Hallo Raphi
Entschuldige, ist leider etwas spät die Antwort. Allenfalls hast Du es auch lösen können. Für die Allgemeinheit hier der Lösungsansatz:
Setze am Anfang Deines Makros einige Parameter mit diesen Code-Zeilen:
Dim outApp As Outlook.Application
Dim outNameSpace As Namespace
Dim outSharedName As Outlook.Recipient
Dim outCalendarFolder As MAPIFolder
Dim outAppointment As AppointmentItem
Dim SharedMailboxEmail As String
Danach vor der Termin-Erstellung:
SharedMailboxEmail = "gewünschterAccount@..." - muss dem Outlook-Account und der Schreibweise entsprechen!
Zudem:
'Get NameSpace and CalendarFolder
Set outNameSpace = outApp.GetNamespace("MAPI")
Set outSharedName = outNameSpace.CreateRecipient(SharedMailboxEmail)
Set outCalendarFolder = outNameSpace.GetSharedDefaultFolder(outSharedName, olFolderCalendar)
Set outAppointment = outCalendarFolder.Items.Add(olAppointmentItem)
Verwende neben den Eigenschaften (zum Outlook-Termin), die Du reinreichst (wie z.B. ".Subject", ".Body" etc.) zudem:
".SendUsingAccount = Outlook.Session.Accounts.Item(1)"
Liebe Grüsse
Roman
Hei,
ich habe eine Tabelle mit Meetings bei den sicher der Zeitpunkt (Tag/Uhrzeit) nach dem Import in den Kalender nachträglich geändert werden muss.
Es sind Meetings über das gesamte Jahr verteilt.
Wie kann ich eine Aktualisierungs-Option in die VBA einbauen sodass geänderte Ereignisse nicht dupliziert sondern aktualisiert werden?
Zur Unterscheidung könnten die Ereignisse ja eine Nummerierung bekommen.
Hallo Roman,
danke für dieses "Tool" Ich habe von Excel VBA gar keine Ahnung und google oder Youtube mir alles zusammen, was ich beöntige.
Ich habe eine Datei erstellt, die aus den Tabellenblättern eine PDF dort generiert, wo die Exceldatei gespeichert ist. Dieses PDF möchte ich dem Termin in Outlook beifügen.
Da ich diese Datei an diverse Kollegen schicken will und nicht weiß, wo sie diese abspeichern, ist eine Vorgabe des Speicherortes nicht möglich.
Mein PDF :
Dim DateiName As String
DateiName = Range("M3") & ".pdf"
Range("A1:G58").ExportAsFixedFormat Type:=xlTypePDF, Filename:=DateiName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Mein Termin :
Dim oApp As New Outlook.Application
Dim oTermin As Outlook.AppointmentItem
Set oTermin = oApp.CreateItem(olAppointmentItem)
'Termin erstellen
With oTermin
.Subject = Tabelle2.Range("M3").Value
.Start = Tabelle2.Range("M4")
.Duration = 180
.Body = "Interessent bezüglich Vertragsablauf ansprechen" & Chr(10) & "Ablauf ist der " & Tabelle2.Range("D14").Value
.Display
End With
Set oApp = Nothing
Set oTermin = Nothing
End Sub
Wie kann ich die zuvor generierte PDF in den Termin einfügen ohne einen Pfad anzugeben ?
Vielen Dank vorab
Nino
Hallo Nino
Du kannst die zuvor generierte PDF-Datei in den Outlook-Termin einbetten, indem Du die PDF-Datei in eine Variable lädst und diese Variable als Anlage im Termin einfügst. Hier ist ein Beispielcode, der die PDF-Datei in den Termin einfügt:
Dim oApp As New Outlook.Application
Dim oTermin As Outlook.AppointmentItem
Dim oAnlage As Outlook.Attachment
Dim DateiName As String
Dim pdfData As Variant
'PDF-Datei in eine Variable laden
DateiName = Range("M3") & ".pdf"
pdfData = CreateObject("Scripting.FileSystemObject").OpenTextFile(DateiName, 1).ReadAll
'Termin erstellen
Set oTermin = oApp.CreateItem(olAppointmentItem)
With oTermin
.Subject = Tabelle2.Range("M3").Value
.Start = Tabelle2.Range("M4")
.Duration = 180
.Body = "Interessent bezüglich Vertragsablauf ansprechen" & Chr(10) & "Ablauf ist der " & Tabelle2.Range("D14").Value
'PDF-Datei als Anlage hinzufügen
Set oAnlage = .Attachments.Add(DateiName, olEmbeddeditem, 1, "PDF-Datei")
oAnlage.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001F", pdfData
.Display
End With
Set oApp = Nothing
Set oTermin = Nothing
Set oAnlage = Nothing
In diesem Beispiel wird die PDF-Datei in die Variable pdfData geladen, die dann als Anlage im Termin hinzugefügt wird. Der Parameter olEmbeddeditem im Attachments.Add-Aufruf gibt an, dass die Anlage in den Termin eingebettet werden soll, und der 1-Parameter gibt an, dass die Anlage als "ICon"-Anzeigeformat eingebettet werden soll.
Das oAnlage.PropertyAccessor.SetProperty-Statement speichert die PDF-Daten als Binärdaten im Anlagenobjekt, damit sie beim Öffnen des Termins angezeigt werden können.
Hoffe, das hilft!
Liebe Grüsse
Roman
Hallo Roman.
der generierte Termin wird angezeigt als "speichern & schließen" und muss erst umständlich mit Klick auf "Terminplanungs-Assistent oder "Teilnehmer einladen" als solches -ja ich würde sagen- aktiviert werden und es erscheint die Möglichkeit zum senden.
Lässt sich das aus dem VBA heraus irgendwie steuern, das ein senden direkt erscheint?
Denn alle Angaben sind ja schon getätigt und ein Klick auf "Teilnehmer einladen" ist überflüssig.
Hallo Marcus
Wenn der Termin ohne weitere Anpassungen erstellt und versendet werden kann, dann müsstest Du lediglich innerhalb von "With Appoint [...] End With" den Befehl .Save als letzte Angabe einfügen. So wird der Termin direkt gespeichert und verschickt (ohne weitere Klicks).
Dies setzt natürlich voraus, dass Du den Empfänger - via .Recipients.Add ("email@mail.com") - automatisch hinzufügst. Für mehrere Empfänger müsste ein "Array" durchlaufen werden, um sie in einer Schleife hinzuzufügen. Ich belasse diesen Kommentar mal bei dieser Info.
Liebe Grüsse
Roman
Guten Tag und vielen Dank für dieses sehr gute vba tool.
Frage: nach jeder Aktualisierung werden die Termine erneut in Outlook eingetragen. Gibt es die Möglichkeit mehrfach gleiche Einträge zu vermeiden?
liebe Grüße Wolfgang
Hallo Wolfgang
Wir können unseren Outlook-Kalender entsprechend abfragen, z.B. filtern, ob ein Termin mit selbigem Betreff sowie der gleichen Start- & Endzeit bereits existiert.
Den Code starten wir gleich wie im initialen Post, bis und mit den Variablen-Deklarationen.
Sub TerminErstellen()
[... gemäss initialem Code ...]
'Check for existing appointment
Dim olFolder As Outlook.Folder
Dim olApt As Outlook.AppointmentItem
Dim olItems As Outlook.Items
'Specify the Outlook folder to search (Calendar / Default)
Set olFolder = OL.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
Set olItems = olFolder.Items
'Define search criteria (using Subject, Start, and End time)
Dim strFilter As String
strFilter = "[Subject] = '" & Subject & "' AND [Start] = '" & StartTime & "' AND [End] = '" & EndTime & "'"
'Apply the filter
Set olApt = olItems.Find(strFilter)
'Check if appointment already exists
If Not olApt Is Nothing Then
MsgBox "Appointment already exists.", vbExclamation
Else
'Create new appointment
[... gemäss initialem Code...]
End If
Set OL = Nothing
End Sub
Roman,
vielen vielen Dank. Obwohl ich mich sehr eingeschränkt auskenne funktioniert alles so wie ich es will. Danke.