Techblog: Automated email

Automated recurring email with Outlook (Office 365) without admin rights

Assume you meet with your friends on every first Wednesday in each month to play card games. And you want to send them an email reminder on the Monday before. For a similar purpose, I tried to set-up an automated recurring email with Outlook in Office 365. I had already sent automated emails easily from my private Ubuntu server. But the task was difficult in my office environment because of two issues. Firstly, Outlook has no ready available function for automated emails. And secondly, I am just ordinary user and have no admin authorization. 

I tried first the method described in the Microsoft documentation. This is designing a custom form with a VBA script in conjuction with a recurring Outlook task. But this method cannot be applied without an admin account, because running custom form scripts are since 2017 desabled by default and it requires setting registry keys to re-enable it. I also tried to realize the automated email with Microsoft Flow, but could not find a suitable template for that.

The path to success is a method which I found in a tutorial of  ExtendOffice.com. In the first step, a recurring appointment with reminder is created in the Outlook calendar. For this appointent, a new category must be used. I have named the category „Recurring email“. Finally, a short VBA script is created for the standard object „ThisOutlookSession“. Scripts for this object run automatically when Outlook starts. The script detects when the reminder for an appointment of the category „Recurring email“ is closed, composes then an email with the appointment title as email subject, the text string in appointment location as email recipients, and the appointment details as email body, and finally displays (VBA command: .display), or sends (VBA command: .send) this email.

It should be noted that VBA scripts are blocked in the default security settings of Office 365 unless the code is authorized by a self-signed digital certifcate.

Result and limitations:
When the reminder for the appointpent pops up and is quit by hitting „dismiss“ or „dismiss all“, the email is either created and displayed for further edits ,or directly sent. But because the VBA script is running on the local device (e.g. a desktop computer), it works only if the local device is powered on with Outlook running. Otherwise, the sending is delayed until Outlook on the device has been started again, the missed appointment displays the reminder pop-up and is quit. It is not sufficient to access Outlook on a synchronized mobile phone or through a web interface, because the necessary VBA script is only available on the local device. I found no way to run the script on the Office 365 server, which would allow full automation without the need to have a local application running and to manually quit a reminder to trigger the email. Another issue is the limitation of the length for the appointment location entry to 255 characters, which is not sufficient for the „To:“ line in case of multiple email addresses. I could resolve this by using group email addresses, another option would be to modify the VBA script and record the email recipients in a structured way in the appointment details section. The latter would also allow emails with more attributes, such as different entries for „To:“, „Cc:“ and „Bcc:“. But despite of the above mentioned limitations, I found the current solution for automated recurring emails in Outlook helpful and have it in use.

The example mentioned at the beginning of this article is a little tricky. The card game evening is on the first Wednesday in each month, and the reminder email should be sent on the Monday before. But this is not necessarily the first Monday of the month. If the new month starts on a Tuesday or Wednesday, then the Monday before the first Wednesday is the 4th or 5th Monday of the previous month. Nevertheless, this can be realized with Outlook. The recurring appointment is scheduled for every first Wednesday in each month, and the reminder is set to 2 days prior to this appointment. Since the quitting of the reminder triggers the email, and not the appointment itself, the email will be sent on the correct day, even it is in the previous month.

Hiking tour in the Qinling mountains

I have been fascinated by the view of the Qinling mountain range (  秦岭 )approaching from the Wei river valley since I have first seen it in the year 1998. The visit of the Taoist temple Lou Guan Tai ( 楼观台寺 ) in that year is unforgettable for me, as it was remote, pristine and atmospheric (unfortunately the place has meanwhile developed to a major and commercial tourist spot). Since then, I have returned to that area, which is close to the hometown of my wife, every one or two years. But it took me until last week before I had the opportunity for a longer hike in that mountains.

View to Qin Ling mountains from Wei river valley

We did park the car at a restaurant after entering the Qinling mountains in a narrow valley on a small local road.

Trout served in a small restaurant in the Qin Ling mountains

The hike takes with breaks approximately 6 hours and is 10 km long (round trip), with 1100 m climbing up and down. The path is narrow and well visible, but not marked or signposted.

For those who want to do the same hike, here are the coordinates of major waypoints. If you want the GPX files, please drop me a short note.

N34 02.369 E108 32.579 Exit on Expressway G5 (Xi’an to Hanzhong), take road S107 towards West.

N34 03.887 E108 24.769 542 m Leave road S107 and turn South on narrow road into a mountain valley.

N33 59.962 E108 24.460 849 m Park car at restaurant, start of hiking. Direction is East-Southeast, first 50 m are stairs. The path is first following the small river and later steeply ascending the mountain ridge.

N33 59.395 E108 25.749 1738 m Small and lonely hiking path is merging with a more populated path coming from North (see comment later). Continue on that path uphill towards South until you reach the crest and continue on the crest further South. Don’t miss that turnoff on your way back.

N33 59.034 E108 25.695 1959 m Guanyin temple ( 观音山 )

N33 58.920 E108 25.553 1936 m End of path at another small temple. Go back the same way from here.

We were told that the Guanyin temple can be also reached, with less climbing, from a higher starting point which we had visited three days earlier. Both ways merge at the forking point mentioned above. I add here the coordinates for the alternative approach:

N34 03.595 E108 28.254 529 m Leave main road S107 here and drive on narrow road uphill.

N34 02.181 E108 27.304 934 m We left the car here and started hiking. The road continues however to the area of the next temple.

N34 02.255 E108 26.824 1115 m Temple

N34 02.353 E108 26.807 1104 m Another temple with nice view to Xi’an and the Wei river valley.

N34 01.418 E108 26.503 1203 m We stopped hiking here and turned back to the car; it should be possible to continue to the Guanyin temple from her.

 

Firmenlauf

Am 28. Juni findet der jährliche Firmenlauf in Basel statt. Ich hatte bisher zweimal teilgenommen, nämlich 2014 und. 2018. Beim ersten Mal erreichte ich Platz 1372 (38:14 für 6000 m), vier Jahre später reichte es immerhin für Platz 1371 (33:15 für 6000 m). Alles deutet also darauf hin, dass ich diesmal Platz 1370 haben werde. Ich werde dann nach dem Lauf berichten.

Nach dem Lauf: Leider war es an diesem Nachmittag noch sehr heiß, 30 Grad im Schatten. Vielleicht deshalb nur Platz 1491 mit 34:32 für 5900 m.

B2Run Basel 28th June 2019

Quiz

What it the fragrant, but moderately toxic compound in this plant?

The picture has been taken on 6th April 2019 at the edge of a woods on the Sissacherfluh (700 m) near Basel. Author and license: Wolfgang Schäfer, own photograph, public domain.

I will post the answer in one week.

Answer: The plant is Galium Odoratum, or Sweetscented Bedstraw, German name is „Waldmeister“. It contains the active compound Coumarin (German: Kumarin), which gives not only this plant but some cinnamon varieties and also hay its characteristic smell. The plant is used as ingredient of May Wine (German: Maibowle). It is advisable to limit the quantity and to use only plants before flourishing, otherwise the too high coumarin concentration can cause head ache.

Structural formula of coumarin = 1,2-benzopyrone = 2H-chromen-2-one.
Author and license of picture with formula: Emeldir – public domain
https://commons.wikimedia.org/w/index.php?curid=32231331

Beschränkung für Kommentare / Restrictions for comments

Leider muss ich das Kommentieren meiner Beiträge zeitweise auf angemeldete Besucher beschränken. Ich hatte in den letzten sechs Monaten 4500 Spam-Kommentare, und das Löschen wird mir jetzt zu zeitaufwendig. Ich möchte auch darauf hinweisen, dass Kommentare nur nach meiner Freigabe öffentlich sichtbar sind.

Sorry to inform that I limit temporary comments to my posts to registered users only. I had in the previous six months 4500 spam comments, and the deletion of them is meanwhile causing too much work. Please be also aware that your comments are only published after I have reviewed them.

Techblog: Pseudonetzwerkbrücke

Das Problem:
In unserer neuen Wohnung in Basel sehen wir uns mit dem Problem konfrontiert, dass der vom Internet- und IP-TV-Provider gestellte Wireless Router wegen der Lage des Glasfaseranschlusses mit allen Endgeräten drahtlos kommunizieren muss. Leider sind aber sowohl mein Desktopcomputer sowie mein Homeserver für Datensicherung und Home-Cloud nicht WLAN-fähig. Ich möchte keine LAN-Kabel durch Zimmertüren legen. Wie kann man hier vorgehen?
„Techblog: Pseudonetzwerkbrücke“ weiterlesen

Abschied

Nach etwas mehr als drei Jahren in China heißt es jetzt, wieder Abschied zu nehmen. Mifang ist schon seit Oktober in der Schweiz und ich werde wohl auch zum Jahresende nach Basel zurückkehren. Meine Business Unit passt ihre regionale Struktur der Vorgabe von Clariant an, weshalb „Nord Asien“ (China, Korea, Japan) geteilt wird: „Greater China“ (China, Hongkong, Taiwan) wird eigenständige Region, Korea und Japan werden in die neue Region „Asien Pazifik“ eingegliedert. Mein Nachfolger in China ist auch schon festgelegt, und ich freue mich, dass es wie schon vor zwölf Jahren beim Wegzug aus Korea ein lokaler Mitarbeiter ist. All das ist vorgestern bekanntgegeben worden und soll schon am 1. Januar umgesetzt sein. Ich werde aber im Januar nochmal wegen der Übergabe nach China zurückkehren.
„Abschied“ weiterlesen

Drachenbootrennen

Gestern habe ich zum ersten Mal an einem Drachenbootrennen teilgenommen. Es war eine große Veranstaltung im Shanghai Aquatics Center, einer Traings-und Wettkampfstätte für Wassersportarten mit einer mehreren Kilometer langen Regattastrecke im Vorort Qingpu. Sowohl das Wetter wie auch die Stimmung waren grossartig. Das Shanghai Centre war mit sechs Teams vetreten, von denen fünf in unserem Lauf gegeneinander antreten mussten.
„Drachenbootrennen“ weiterlesen

Umzug in Shanghai

Mifang arbeitet seit Oktober wieder in der Schweiz, weshalb wir in den letzten Wochen ziemlich mit Umzug und Wohnungssuche in Basel beschäftigt waren. Es hat sich aber gelohnt und wir konnten eine schöne und zentrumsnahe Wohnung in der Nähe des Kannenfeldplatzes finden. Mifang wird dort im Dezember einziehen. Ich bin in Shanghai jetzt auch in eine kleinere Wohnung umgezogen. Der Auszug war stressiger als gedacht, da die reichen chinesischen Vermieter der alten Wohnung trotz einer unglaublich hohen Monatesmiete beim Auszug noch tagelang nach Kleinstschäden in der Wohnung gesucht haben, die sie in Rechnung stellen könnten. Auch war der Einzug in die neue Wohnung erst mal nervig, weil ein vorher versprochener Balkon gefehlt hat, aber nach Beschwerde habe ich dann doch noch ein anderes Apartment im gleichen Compound, aber diesmal mit Balkon bekommen. Unter dem Balkon ist leider eine Restaurantterrasse mit abendlicher Musikbeschallung. Trotzdem fühle ich mich hier jetzt sehr wohl, denn die Wohnung ist sehr schön und die Lage einzigartig. Ich bin in den „Shanghai Centre“ Apartments in der Nähe des Jing’an-Tempels und kann die Altstadt (French Concession) in wenigen Minuten zu Fuß erreichen. Das Bild zeigt den Blick von meinem Balkon.

View from Shanghai Centre
View from balcony of Shanghai Centre