Thursday, November 18, 2021

Samsung Buds2 linked to iPhone - what works and what won't

Recently I got some new Samsung Galaxy Buds2 (2021). These are looking very nice and sound is good but it seems the Samsung Buds app is not supporting this new device on iPhone. As a consecquence, not all options and configuration is available as it would on a recent Android.

Yet, after playing around with the device on my iPhone, I can conclude so far what is working with Buds2 and iPhone:

  • Bluetooth connection can be setup within iPhone config Bluetooth
    • To force the Buds2 into a Bluetooth pairing, long press (> 3sec) on both Buds2 at the same time. They will let you hear they are in a Bluetooth discoverable mode
  • When a music player is active, music can be stopped and started with a single click on any of the Buds2
  • When someone is calling your phone and you want to pick-up the phone, you can double click any Buds2 in order to pick-up or end the phone call.
  • The Active Noice Cancellation (ANC) can be enabled and disabled by long pression any of the Buds2. Long pressing the Buds2 will enable the outside microphones so you can talk to someone and have a standard life conversation with someone in the room without the need of taking the Buds2 out of your ears. Long pressing again will disable these outside mics so surrounding noice will no longer be actively amplified.
So far, these are the functions that I have found still to be working even while connected to an iPhone and thus having a limited functionality set.

Friday, May 28, 2021

my “Chrome extensions” selection

  1. 'Improve YouTube!' (Video & YouTube Tools) – Extra YouTube player option and default settings
  2. AdBlock — best ad blocker – Block adds
  3. Advanced REST client – Play with REST webservices
  4. Postman – Play with REST webservices
  5. Allow Select And Copy – Some pages try to block select and copy functionality
  6. Don't Fuck With Paste – Som pages try to block copy / paste functionality
  7. Auto Refresh Page – Sometimes you want a page to refresh on regular base
  8. DownAlbum – Assist to download foto albums shared via web
  9. Image Downloader - Assist to download foto albums shared via web
  10. Export links of all extensions – Create overview of all Chrome Extensions installed
  11. GoFullPage - Full Page Screen Capture – Capture full web pages (incl scrolling down) as image or PDF
  12. Google Docs Offline – Support for offline Google Docs functionalities
  13. Honey – Search for coupon codes when shopping online
  14. IE Tab – Some sites still need Internet Explorer
  15. Local Explorer - File Manager on web browser – Allow to open Windows Explorer from website url
  16. OneNote Web Clipper – Clip website into OneNote
  17. Recently Closed Tabs – See list of recently closed tabs
  18. RoboForm Password Manager – Password manager integration
  19. Selection Highlighter – Highlight the text select in the full website
  20. SingleFile – Download webpage as single mhtml file
  21. TweetDeck by Twitter – Twitter with options and multi accounts
  22. User-Agent Switcher for Chrome – Easily switch user agent
  23. Video Downloader professional – download video’s
  24. Web Scraper - Free Web Scraping – download / scrap content of full site or convert site to table
  25. Multi-File Downloader – download video’s and images from site
  26. Screencastify - Screen Video Recorder – record screen
  27. Web Developer – options for debugging during web development

Wednesday, May 12, 2021

Home Assistant – DIY home IoT and automations

Moving away from IFTTT

I used to orchestrate my home IoT devices such as cheap Tuya or Ewelink Sonoff wifi relays using the cloud IFTTT (If This Than That) platform as it made it easy to setup some flows without having any local infrastructure. Yet, quickly the flows became a bit more complex to add smart integration with other platforms such as Tado and Ezviz Wifi doorbell. Initially I still was able to achieve most of the orchestration and rules by integrating other cloud platform such as apilio.io and later on switchur. But as the IFTTT suddenly decided to charge extra high monthly costs (besides the high rates they charge to the manufacturers supporting IFTTT), and my use cases and needs became too complex to manage well with IFTTT, I decided to look at some alternatives. Too bad IFTTT didn’t had a bit more respect for their community and early adopters, but after all, I’m very glad to made the move, as much more fine grained control is now possible.Home Assistant - Wikipedia

First I tried to play around with OpenHAB as a free home IoT platform, as it can easily run within a normal Windows environment (Java based) and has easy support for remote iOS/Android application. But soon I concluded I should switch to Home Assistant as it has a massive community support and a great look and feel with many devices and platform supported, including my favorites such as the Google Nest Hub and Google ChromeCast.

Setup Home Assistant

In order to run Home Assistant, one should check out the excellent installation documentation they provide. I chose to use a docker container in order to first test within a Windows environment but next run the same on an old small media pc on which I installed Ubuntu. Running perfectly on limited CPU, 2GB Ram and an 250GB SSD.

Home Assistant Docker

To just run home assistant docker in Windows, make sure to have installed WSL. In order to store your Home Assistant configuration outside of the docker container, a volume (folder) should be mapped from your OS towards the docker container. These files will be fully accessible from the Docker container, but will not be touched when upgrading or removing your docker container, to make sure your personal stored files are not lost. This mapping is possible with –v option:

initial install:

docker run --init -d --name="home-assistant" -e "TZ=Europe/Brussels" -w /config -v "<path>\ha-config\:/config" -p 8123:8123 homeassistant/home-assistant:stable

run docker:

docker run -d --name="home-assistant" -e "TZ=Europe/Brussels" -w /config -v "<path>\ha-config\:/config" --net=host --restart=unless-stopped homeassistant/home-assistant:stable

update docker:

docker update home-assistant

This will start your personal home assistant and make it accessible on port 8123.

Yet, after a while other option may be desired and other docker containers of other applications may be used (eg plex, youtube-dl, deepstack, etc). If you don’t want to type the full command with all option at each run, you can use docker compose to store your configuration option and allow to easily start, stop, upgrade etc. A docker-compose.yml file will just store your options for you to ease the commands to run.

A basic docker-compose.yml file used in Linux Ubuntu example shown below:

version: '3'
services:
  homeassistant:
    container_name: home-assistant
    image: homeassistant/home-assistant:stable
    volumes:
      - /home/myt/homeassistant/:/config
      - /home/myt/homeassistant/media/:/media
    environment:
      - TZ=Europe/Brussels
    restart: unless-stopped
    network_mode: host

With such as docker-compose file, below commands can be used. The container name homeassistant at the end of these commands is optional, it can be used if multiple dockers are defined in your compose file:

start with docker compose :

docker-compose up –d homeassistant

restart with docker compose:

docker-compose restart –d homeassistant

update with docker compose:

docker-compose pull

docker-compose up -d --build homeassistant

Remote access

The main development of the free and open source project Home Assistant is supported by the Dutch company Nabu Casa. They get their money to continue the development of Home Assistant by adding full remote cloud support to your local Home Assistant environment. This allows easy integration with Amazon and Google cloud services (assistant, text to speech, Nest Hub) and remote access to control your home with the Home Assistant app. These services require access to your local free Home Assistant deployment. Yet, these paid cloud services (5$/month) are not mandatory and all these cloud services can also be achieved when manually setup. It will just require some extra manual effort. Anyone who may not be familiar with Linux configuration and less IT minded will definitely be advise to use this paid subscription. Yet below approach is an alternative.

Different options exist to get remote access to your local server. As most will receive a dynamic IP address from their ISP that will regularly change, some forwarding is required in order to always know the public IP address of your local server. A dynamic DNS forwarder may be supported by your router or a little application can be installed on your server to update the public address of a DNS. Yet, even when the dynamic public IP address is know, this will require you to open a port on your router and allow inbound traffic from the internet towards your local server. Another solution that could be used is by running telebit.io (similar to ngrok but will keep for free your unique fixed dns whichs is only possible in the paid version of ngrok). With this little telebit service you can easily install and get a fixed telebit.io DNS linked to a specific port, such as port 8123 of your local Home Assistant by running below and confirming your email address:

> curl https://get.telebit.io/ | bash

> ~/telebit http 8123

Whatever solution is chosen, once a public accessible URL is available towards your local Home Assistant, it can be used within the Home Assistant iOS and Android apps and within the configuration of Amazon or Google Cloud services.

As soon as any remote public online access is enabled, I would strongly advice to make sure the server is regularly patched with security updates as well as enabling 2FA within Home Assistant, as you should with any other cloud service.

Integration of services and devices

Once Home Assistant is up and running, it allows you to easily connect many different devices and cloud services. These can be setup using the Home Assistant Configuration Dashboard (localip:8123/config/dashboard) –> Integrations. Yet, also many services will require to be defined within the ‘configuration.yaml’ file stored within the config folder of your Home Assistant. All information required to correctly define these in your local config can be found on the excellent documentation pages of Home Assistant.

Services

Below a list of the services I found most useful:

  • Google Calendar: whenever I need some activity to be scheduled on time, I do prefer to create a specific Google Calendar for it instead of hard coding it in scripts or automations. This makes it possible to get a clear overview of your schedules (using different colors for each calendar) and will also make it possible to easily add repetitions or exceptions (eg during holidays) by just planning it within your calendar as if you’d plan a regular meeting. Once the calendar is defined, an automation can be created that will trigger whenever a meeting starts and/or stops.
    • Good to know: within a trigger by state change, you can define a ‘From’, ‘To’ state and ‘For’ how long that state should be active before the action is triggered. Eg the calendar will change to state ‘on’ when a meeting becomes active. The status can always be checked easily using the ‘Developer Tools’ page. Yet, if both ‘For’ and ‘To’ are left empty, the automation can be triggered by any change. I found this very useful as I initially tended to copy the automation in order to get a trigger for all states expected, while a common trigger was easier.
    • HaCalendarTrigger
  • Alarm: an Alarm control panel can be defined which allows you to arm, disarm and many other options. To set this up, I followed much of the approach as explained here by Thomas. Based on groups of sensors and groups of users, I automatically arm and disarm my custom alarm. When leaving (armed_away) or when at home (armed_home) sensors will trigger the alarm which will start recording on the camera’s, send notification and flash lights. Yet, in contradiction with the approach of Thomas, I don’t use a group to initiate the triggers. Since, once the group has state ‘open’ it will no longer detect other doors or windows being opened. So if for example you would have a window open before leaving, the group state will be open before the alarm is armed. Next, if another window in the same group would open, the group state will remain open and thus not trigger the alarm. Using Google Nest mini, Hub and ChromeCast integration I can also start a sirene youtube video at full volume and flash blue led lights. Different advanced options exist in order to get advanced notifications with specific option and sounds. An example below of an alert notification in case of alarm triggered, this requires ios actionable notifications push category ‘camera’ to be defined in order to allow immediate action on the notification to disarm. The camera category has special treatment in order to allow to open a life stream of the camera when long-pressing the notification on the iPhone. Using the url option, a specific lovelace site in the Home Assistant can be opened whenever the notification is clicked. The update.caf default iOS sound made some sound that would take my attention when needed.
  • - service: notify.mobile_app_iphone
      data:
        url: /lovelace-alarm/alarm
        title: Alarm
        message: Alarm at home {{now().strftime("%H:%M:%S %d/%m/%Y")}}
        data:
          entity_id: camera.ip_cam_stream
          attachment:
            content-type: jpeg
          apns_headers:
            apns-collapse-id: alert
          push:
            sound:
              name: Update.caf
              critical: true
            category: camera
            thread-id: alarm

  • Telegram notification call: Still in order to make sure I would be aware of alarm events (eg at night of while away) I preferred to get called to make sure I would wake up. By integrating the CallMeBot api linked to telegram, this can be achieved. The bot will make a call and with text to speech read the pre-defined message eg indicating which sensor was triggered.
  • HACS: HACS is THE un-official Home Assistant Community Store. It supports many custom integrations of services and extra front-end customizations. Once HACS is installed, it will be very easy to find, install and update any other custom integration.

Devices and integrations

As I used to only use cloud based services with IFTTT, most of my devices can still be controlled via the different cloud manufacturers, eg Ewelink and Tuya. These have a huge set of cheap wifi devices connecting through their platform. Using a local Home Assistant, you could opt to disable most of these cloud accesses if you don’t trust the cheap China based firmwares. Yet, for me I do prefer to have a fallback to control my devices using Home Assistant and the specific device apps. If my Home Assistant would not be accessible remotely, I may still be able to control the devices directly.

  • Sonoff Ewelink wifi relays (+- €6 per relay): the relay just allows to turn on or off the current on a wire. Whatever is connected to it will thus get power on or off by it. I use it to control lights, water heating, cameras that doesn’t support full integration, garage ports, etc. Integration of Ewelink Sonoff devices is now well supported in combination with the standard Sonoff cloud firmware.
  • Sonoff Ewelink wifi power plug switch (+- €6 per switch): Same behavior as the wifi relay, but made for power cord connection
  • Sonoff Ewelink wifi door sensors (DW2) (+- €6 per sensor): these very small battery based sensors will just detect if the sensor is open or closed. Based on this the alarm can trigger if desired, etc.
  • tado central heating (base kit +- €200, +- €60 per extra optional radiator valve): this allows multi room valves to control temperature in each room separately and get constant sensor information on temperature and humidity of each room. Over time, I extended this to multiple rooms as it showed to give a high comfort: when needed separate room temperature can be increased while also allowing to limit the heating to minimum when not needed. So over time, this may result in some heating cost saving.
  • Ezviz wifi doorbell DB1 with camera (+- €100): doorbell that will call you on your phone when someone rings the bell, it includes sensor for detecting motion (before button is pressed) and camera to see life feed. It support local SD card recording and recording using Home Assistant, by doing so, no cloud recording subscription is needed (while possible). Motion detection integration within HA is possible, yet so far the bell button press event can’t be captured within Home Assistant yet.
  • Tuya wifi power plug (+- €6): I connect an AirWick power plug-in to this smart plug, so I can plan when the AirWick fragrance should be release (it’s too overwhelming when leaving powered on all the time). So based on a specific Google Calendar I repeat the AirWick to turn on and off at regular intervals. Using local tuya HACS or standard tuya integration, full integration within HA is working well.
    • Many of these Tuya smart power plugs also offer power monitor functionality. This can easily be integrated into the power moniotorring of Home Assistant. If the device only provided current, it can be converted into kWh using the Utility Meter integration.
  • Cheap IP camera’s (+- €15 per camera): some don’t allow full integration, but by connecting these to a wifi relay, still they can be turned on and off. Some cheap ip camera’s do support rtsp video streaming, which can be integrated into HA generic camera integration. In order to find the correct rtsp url to use, eg with ffmpeg, one can use the onvif device manager tool which will scan your local network and discover many cameras including the rtsp url, or using this site detecting your IP cam device type and url. Doing so, I discovered I was still able to integrate some cameras I didn’t expect they would work. Example configuration.yaml:
  • camera:
      - platform: ffmpeg
        name: Living webcam YCC365
       # PTZ support for YCC365 compatible cameras: https://github.com/fjramirez1987/PTZ-YCC365
        input: !secret YCC365_rtsp_url

    • I also bought a cheap Tuya based wifi IP camera (+- €15). Because it was based on Tuya, I expected to be able to get a better integration in Home Assistant platform. Yet, it seems these cameras don’t allow any RTSP life streaming feed of the camera image, nor ONVIF protocol. Even the motion detection notifications can’t be integrated, except within the Tuya application itself. So I’m still looking for a good wifi ip camera with optimal Home Assistant integration to be able to trigger any Home Assistant event as soon as a motion is detected. As a temporary workaround I now trigger a Tuya wifi plug state when a motion is detected. With Home Assistant local tuya HACS integration, this plug state switch can immediately be detected and next this can trigger any other event within Home Assistant. This allows alarm trigger on camera motion detection, but is not optimal off course. Since the AirWick sense is only connected to this Tuya wifi smart plug, briefly turning it on and off again at some motion detection has not much further impact.Since 10/2021 the Tuya integration within HA has been renewed and now much better support is available, including camera support!
    • When you’d have multiple camera’s to shown in a web lovelace, it might be interesting to look at the WebRTC plugin which make it possible to have faster rtsp streaming.
  • Tuya water irrigation wifi control (+- €35): open or close a water to allow remote garden irrigation
  • Tuya wifi roller blind motor (+- €40): remote control roller blinds and by integrating within Home Assistant they can become smart and eg take into account sun state or anything else
  • Tuya wifi IR controller (+- €15): allows to control all infra red devices via wifi. this allows to ‘integrate’ many old ‘dump’ devices, althout the integration is not always optimal since you can’t get any feedback or status info of the device.
  • Audi connected car integration: see mileage, state of car doors, windows and locks
  • Google ChromeCast, Audio ChromeCast and Google Nest Hub (€35 - €100): ChromeCast devices allow great interaction and Home Assistant has good support for these.

Personal tips for automations and scripts

Scripts to return to automatic default state

While defining different automations and scripts, you will notice this will probably be a incremental approach. Specific occupations will occur, which you’ll notice you may want to cover over time, extending your automations and scripts to cover more exceptional cases or combinations etc.

Personally, I tried to use the automations to trigger and initiated special activities, yet, within the ‘Action’ part of the automation, I would try to use as most as possible a call to a script. This allows you to re-use many actions as the same script can be called at other triggered events. This will make sure you won’t have to redefine the same actions over and over again for each automation.

For many group of devices I also tried to follow the same setup as used by tado: define a default standard schedule and allow to overrule it manually whenever desired. As soon as the manual overruling is no longer needs, return to the default automatic schedule.

  • I most often define a default automatic schedule using a specific Google Calendar: eg when outside lights need to turn on and off, when camera’s need to turn on and off, when water heating needs to trigger, etc.
  • Within the Home Assistant web and app interface, all buttons are available to overrule this automatic default schedule
  • I created for all these groups of devices a script to return to automatic state whenever launched. This ‘automatic’ or ‘default’ script became very important. Whenever a specific intervention is ended, I would not call to turn on or off a specific device, but always call this ‘auto’ script in order to make sure the device can return to the state it should be as defined by the default schedule.

For example: I created a default schedule to light up some outside lights in the evening based on a specific Google Calendar. But I don’t want to keep these light on all night, only in the evening and morning. Yet, I return home in the middle of the night, I still want these outside lights to light up for a short time. So the lights follow the schedule in the calendar and whenever I return while the sun is down, I make sure the lights will light up. But after a while, the light will not be turned off but the ‘auto’ script will be triggered to make sure they return to their default schedule. Sometimes it will still be in the evening, so they can still remain on for a while, yet if middle of the night they will be turned off. I applied the same approach for cameras, heating, water heating, alarm etc. These ‘auto’ scripts tent to become complex with different conditions to cover all cases, but once defined, any automation can easily call such ‘auto’ script without worrying about all special cases and combinations.

Parallel processing

While writing different scripts and automations in Home Assistant, one should remember when an automation or script sequence is launched, all commands will be executed in the specific sequence one after another. If multiple actions would need to be executed in parallel, different automations can be created that get triggered. Eg by creating a variable (‘Configuration > Helpers’). As soon as the state of this variable changes, all automations depending on this helper variable state will be triggered and execution will be started in parallel. For an alarm activation this may be needed to make sure the different actions get triggered asap, such as recording, lights, notifications etc.

Icons Material Design

It is possible to assign custom icons to devices, buttons, scripts etc within Home Assistant. The easiest way to achieve this is by using Material Design icons, which are natively supported, eg using ‘mdi:account’ to get a kind of account/user type of icon.

As many icons are supported by Material Design, I mostly use this materialdesignicons.com website with good search capabilities in order to easily search through the long list of icons and try to find some icons that matches best my needs. Since late 2021 extra support has for Material iconas has been added withing HA which allows very easy Material Design icon selection within HA itself.

Most valuable uses cases to automate

  • Full autonomous alarm system: turning on and off based on location of the family members, see details above. It will turn on and off the in house cameras making sure the camera’s are only capturing when needed so to limit capturing private privacy in house activity.
  • Warn if the garage gate is open, with smart notifications to easily close the garage gate or trigger the alarm. As long as the garage gate remains open, the warning notification will triggered regularly (every 30min) to keep on warning the gate remains open.
  • Whenever the garage gate is open, start the garage camera to record any motion detected
  • Warn when leaving the house while some doors or windows are still open, indicating in the notification which door or window is not closed.
  • Warn when windows are open for a too long time, while it’s cold outside.
  • Warn when the battery of door and window sensors are getting low
  • Control the lights outside the house, to turn on and off based on sunset and sunrise or alarms
    • Flash the front door lights several times when arriving home and freezing is expected the next day so to warn the car windshield should be protected against frost.
    • Turn off the lights in the middle of the night till morning, but when arriving home in this period, still turn on the front door lights for a short period while entering the house.
  • Turn on hall light at night or when cloudy weather
  • If motion is detected at the front door, cast a life feed to Google Nest Hub to see what’s happening
    • These use cases quickly became a bit more complex as exceptions where desired to only start the casting when someone is home and no casting on the hub was active. If some music casting was active, it would continue the music after a short interruption to show the front door camera stream.
  • Start camera recording when alarm is triggered
  • Warn if my car is unlocked for a long period or when it’s unlocked while no one of the family is close to the car.
  • When taking a bath in the bathroom, make sure the temperature of the bathroom is high enough and hot water heating is enabled.
  • Disable the bathroom ventilation system at night (as it makes too much noise).
  • Detect and notify high humidity (mostly in bathroom). If high humidity is detected, allow to easily turn on heating and enable bathroom ventilation system.
  • If a room heating is on yet some window is open in the same room, send a warning notification.
  • If warm outside and no rain is expected in coming days and it didn’t rain much in previous days, start the water irrigations system for a while early in the morning.
  • Turn on some color led lamp behind television when casting and making the led color randomly change when casting music.
  • When a song is playing on ChromeCast or Nest Hub, allow with one click to retrieve artist and song name and query it for immediate mp3 download on YouTube-DL to store the music in our offline music Plex collection. With some iOS shortcuts, any song we hear (eg on radio) can be identified (eg with MusiXMatch / Shazam) and the song name and artist can be forwarded through Home Assistant (since remotely accessible) which will trigger the mp3 download using YouTube-DL. This way, the YouTube-DL site and API don’t need to be publicly/remotely accessible, so all queries are forwarded via Home Assistant with REST API.
  • Monitor power consumption of fridge/freezer to get a warning if a long period is detected in which no power is used or very high power is used. I want to (try to) detect if the fridge has lost power or if the freezer door wouldn't be closed correctly (resulting in high power usage).
  • Monitor power consumption of our home. I have made a REST custom integration to fetch Pixometer.io values to be retrieved from the Pixometer.io API.
  • Check if traffic to work or home might be slower than expected and show a map of the traffic using the Waze travel time integration.
  • 'Afvalbeheer' integration (installed via HACS) to get details of local waste collection. This allows to show specific warnings/notifications. If waste will be collected next day, but the garage door didn't open in last X hours or the phone is connected for charging in the evening, send special notification to highlight the waste collection. Example sensor configuration.yaml for Belgium (RecycleApp):
    • sensor:
    • - platform : afvalbeheer
      wastecollector: RecycleApp
      builtinicons: 1
      resources:
      - restafval
      - gft
      - papier
      - pmd
      postcode: 1000
      streetnumber: 1
      streetname: straat
      cityname: Gemeente
      printwastetypes: 0
      upcomingsensor: 1
      dateformat: '%d-%m-%Y'
      name: Recycle
      dutch: 1

Frontend web / app lovelace

I like to have all house automation easily accessible within the Home Assistant app using the interface (lovelace). Over time I’ve build different views and splitted these up as described below:

    • General quick-switch screen with big buttons to easily control all devices, lights etc.

     

    • Alarm screen with immediate view on main sensors and camera and allow to trigger alarm or cast camera to hub
      • A separate screen is available to show photo and video album gallery with recent camera captures of motion detections
      • A similar alarm main info screen is build, but with specific limited buttons specifically to cast to Nest Hub showing main info on small screen without need to scroll
      • It can also show some graphs with recent alarm and sensor activities. Whenever I show graphs, I added a selector to easily define the history horizon to show. This is performed using a simple helper input_number variable that holds the number of hours of history to be loaded.
        • Standard history graph lovelace entity config:

        type: history-graph

        entities:

          - entity: sensor.speedtest_download

        hours_to_show: 48

        refresh_interval: 0

        type: 'custom:config-template-card'

        variables:

          - 'states[''input_number.hours_of_history''].state'

        entities:

          - input_number.hours_of_history

        card:

          type: history-graph

          entities:

            - entity: sensor.speedtest_download

          hours_to_show: '${vars[0]}'

          refresh_interval: 0

          historygraph

          The same approach can be used with any other lovelace entity card type to have a dynamic parameter, eg I use the same on a map card to hours_to_show on a map:

          type: 'custom:config-template-card'

          variables:

            - 'states[''input_number.hours_of_history''].state'

          entities:

            - input_number.hours_of_history

          card:

            type: map

            entities:

              - entity: person.name

            hours_to_show: '${vars[0]}'

            default_zoom: 10

    • Car overview showing state of car windows and locks and showing mileage and remaining gas.
      • It can also show a map with car track in recent period and family members presence
      • As the Audi connected services allows to get status of many different windows and locks, I added a bullet on the car picture to easily see which door/window/lock would be on. More or less similar integration is possible with other car vendors such as BMW etc.:
      • audi

      • Home central heating screen with clear overview of room temperature and humidity. This screen will allow me to quickly change temperature, hot water heating, control bathroom lights and ventilation, see upcoming weather conditions, etc.
        • It can also show some graphs showing heating and temperature activity
        • Again the ventilation is not smart, but by just connecting it to a wifi relay, some control to turn on and off is possible even with cheap devices.
        • Media screen with overview of media playing and allow to quickly start YouTube, Spotify or plex media to stream to hub or ChromeCast devices. Also allows to control main devices by infra red to easily change volume, channel, etc.
          • Full floorplan image of all house levels showing all lights and plugs and electricity connections linked to the fuses within the electricity box: Every light or plug is linked to a fuse wiring which enables to quickly see the fuse linked to a specific light or plug. Whenever I’d need to turn off a fuse, I would immediately see which other plugs or lights would be impacted. Any fuse can be enabled/disabled to see which plugs/lights are linked, as well as any light or plug can be enabled/disabled to easily see to which fuse it’s linked.
              floorplan
          • In order to allow easier configuration, I use many input helpers variables which are easier to change later on in order to tweak specific needs, instead of searching through all automations and scripts. With some templates this can be achieved, for example:
            • Condition on sun elevation with input_number variables:
              • {{state_attr('sun.sun', 'elevation') < states.input_number.sunset_elevation.state|float }}
            • Conditions based on humidity sensor with input_number variables:
              • {{states.sensor.room_humidity.state|float >= states.input_number.max_room_humidity.state|float}}
            • If for example a delay is required, it can be based on a variable input_number:
              • delay: '{{states.input_number.xxx_timeout_min.state| multiply(60) | int}}'

          Integration with other applications

          While setting up Home Assistant, the need rose to have an own multi media management platform, mainly for music, but also for some movies. So I added another docker container to run Plex and scan a folder with media files. Different plugins exist to integrate Plex with Home Assistant and the Plex Assistant looks promising, but is not yet setup (currently still missing full Dutch music support).

          Below service is used to cast Plex to ChromeCast script in home assistant scripts.yaml, the input_select.media_player helper is used to allow a drop-down choice of the ChromeCast device to cast too. Within the helper variable input_select.PlexPlaylist all playlists defined in Plex are listed so you can choose which playlist should be started when casting, a random song will be selected from this playlist:

          play_plex_media_selection:
            alias: Play Plex media selection
            sequence:
            - service: media_player.play_media
              data_template:
                entity_id: media_player.{{ states('input_select.media_player') }}
                media_content_id: 'plex://{ "playlist_name": "{{states(''input_select.PlexPlaylist'')}}",
                  "shuffle": "1" }'
                media_content_type: PLAYLIST
            mode: single
            icon: mdi:play-network

          In order to easily download much from YouTube, I also added a docker container with YouTube-DL including API. This was integrated with Home Assistant via REST API in order to easily allow to download any YouTube music video to mp3.

          docker-compose.yaml used for this YouTube-DL:

          youtube-dl:
            container_name: youtube-dl
            network_mode: host
            #
          https://hub.docker.com/r/nbr23/youtube-dl-server
            image: "nbr23/youtube-dl-server:latest"
            volumes:
              - /home/myt/plex/media:/youtube-dl
              - ./config.yml:/usr/src/app/config.yml:ro
            restart: unless-stopped

          YouTube-DL config.yml used to make it download mp3 by default and store artist name and song title:

          ydl_server:
            port: 8080
            host: 0.0.0.0
            metadata_db_path: '/youtube-dl/.ydl-metadata.db'

          ydl_options:
            output: '/youtube-dl/%(artist)s %(title)s.%(ext)s'
            cache-dir: '/youtube-dl/.cache'
            #write-thumbnail: True
            extractaudio: true
            audio-format: 'mp3'
            verbose: true
            format: 'bestaudio'
            add-metadata: true
            no-mtime: true
            ignore-errors: true

          Rest command integration to allow REST http request from Home Assistant towards YouTube-DL within home assistant configuration.yml:

          rest_command:

            youtubedl:
                 url: '
          http://localhost:8080/api/downloads'
                method: POST
                payload: "format=audio/mp3&url={{ youtube_url }}"
                content_type: "application/x-www-form-urlencoded"

          Script to call YouTube-DL via REST API:

          download_youtube_mp3:
            alias: Download YouTube MP3
            sequence:
            - service: rest_command.youtubedl
              data:
                youtube_url: '{{ states(''input_text.youtube_url'') }}'

          search_download_youtube_mp3:
            alias: Search & Download YouTube MP3
            sequence:
            - service: rest_command.youtubedl
              data_template:
                youtube_url: ytsearch:{{ states('input_text.search_mp3_youtube') }}

          download_mp3_selection:
            alias: Download MP3 selection based on selected media player playing artist and song
            sequence:
            - service: rest_command.youtubedl
              data_template:
                youtube_url: ytsearch:{{state_attr(('media_player.' ~ states("input_select.media_player")), "media_artist")}} {{state_attr(('media_player.' ~ states("input_select.media_player")), "media_title")}}

          Once this is integrated, a webhook can also be defined within Home Assistant to allow a http call from other applications to pass through Home Assistant. Within Home Assistant automation.yaml. This allows for example to call your Home Assistant public dns with http query such as: https://homeassistant-public-dns/api/webhook/ytsearch?search=Faithless Insomina:

          - id: 'XXXX'
            alias: YouTube search and download webhook
            description: ''
            trigger:
            - platform: webhook
              webhook_id: ytsearch
            condition: []
            action:
            - service: input_text.set_value
              data:
                value: '{{ trigger.query.search }}'
              entity_id: input_text.search_mp3_youtube
            - service: script.search_download_youtube_mp3
              data: {}
            mode: single

          Update: 09/01/2022 taking into account some last HA updates such as much improved Tuya support and Material Design icons. 

          Monday, May 10, 2021

          On screen numeric touch keyboard – AutoHotKey

          OnScreenKeyboardNumeric

          Many new small 12” or 14” laptops come with a touch screen in combination with a small keyboard, missing the numeric input keys. When using azerty keyboards, the numeric keys need to hold shift key, which resulted in some annoyance when often needing to enter Windows credentials with Windows Hello PIN. Off course, Windows 10 allows you to open an on screen keyboard (OSK) and even has a nice keyboard shortcut for it: Win + Ctrl + O, but I preferred to use a small numeric touchscreen keypad entry so I created one using AutoHotKey.

          The AHK script can easily be converted into and .exe executable using Ahk2Exe that can be added into the Windows start menu to launch at Windows startup: just add a shortcut to this .exe into folder “%appdata%\Microsoft\Windows\Start Menu\Programs\Startup”.

          The script will monitor all windows and whenever a windows having the text “Windows Security “ would pop up, it will activate the on screen numeric keyboard on top and allow you to easily enter the numbers, press Enter or press backspace (<).



          Sunday, March 29, 2020

          Use Windows Spotlight logon backgrounds a desktop background slideshow

          Since I liked the Windows Spotlight automatically changing backgrounds, I wanted to use the same as desktop background. Windows allows to select a folder with pictures to use a slideshow background. Yet, it seemed the Windows Spotlight backgrounds aren’t stored as standard pictures.
          After some research, I found out the Windows login Spotlight backgrounds are saved in the folder: %LOCALAPPDATA%\Packages\Microsoft.Windows.ContentDeliveryManager_cw5n1h2txyewy\LocalState\Assets\. But each background is stored twice, one vertical and one horitzontal picture, I’d only need the horizontal for desktop backgrounds.
          I created the PowerShell script below to copy the horizontal pictures from the Spotlight folder and copy into a Spotlight folder within the users Picture folder. Next, the desktop wallpaper settings can be pointed to this %userprofile%\Pictures\Spotlight folder to use as filled slidedshow.


          Function Get-ImageDetails2
          
          {
               begin{        
                    [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") |Out-Null 
               } 
                process{
                     $fi=[System.IO.FileInfo]$_           
                     if( $fi.Exists){
                          $img = [System.Drawing.Image]::FromFile($_)
                          $img.Clone()
                          $img.Dispose()       
                     }else{
                          Write-Host "File not found: $_" -fore yellow       
                     }   
                }    
               end{}
          
          }
          
          
          Function DirectoryToCreate($DirectoryToCreate) {
               if (-not (Test-Path -LiteralPath $DirectoryToCreate)) {
               
                   try {
                       New-Item -Path $DirectoryToCreate -ItemType Directory -ErrorAction Stop | Out-Null #-Force
                   }
                   catch {
                       Write-Error -Message "Unable to create directory '$DirectoryToCreate'. Error was: $_" -ErrorAction Stop
                   }
                   "Successfully created directory '$DirectoryToCreate'."
          
          
              }
               else {
                   <#"Directory $DirectoryToCreate already existed"#>
               }
          
          }
          
          
          DirectoryToCreate("$env:USERPROFILE\Pictures\Spotlight\")
          
          
          Function Get-ImageDetails
          
          {
               begin{        
                    [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") |Out-Null 
               } 
                process{
                   $file=[System.IO.FileInfo]$_           
                   if( $file.Exists){
                       $fs = New-Object System.IO.FileStream ($file.FullName, [IO.FileMode]::Open, [IO.FileAccess]::Read, [IO.FileShare]::Read)
                       $img=[System.Drawing.Image]::FromStream($fs)
                       $fs.Dispose()
                       $img | Add-Member `
                                   -MemberType NoteProperty `
                                   -Name Filename `
                                   -Value $file.Fullname `
                                   -PassThru
                   }
                }    
               end{}
          
          }
          
          
          
          
          dir "$env:LOCALAPPDATA\Packages\Microsoft.Windows.ContentDeliveryManager_cw5n1h2txyewy\LocalState\Assets\" -Recurse | % {
          
          
              $image = $_
               $imagePath = $image.PSPath
               $imageName = $image.Name
               $imageDetails = $image| Get-ImageDetails
               
               $imHeight = $imageDetails.Height
               $imWidth = $imageDetails.Width
               if (($imHeight -le $imWidth ) -and ($imWidth -gt 1000)) {
                   copy-item -path "$imagePath" -destination "$env:USERPROFILE\Pictures\Spotlight\$imageName.jpg"
               }
          
          
          }
          Using a Windows Scheduled task, the script can be run after every login. In order to make sure no PowerShell command window is shown for each execution, I created the little .vbs script to run the PowerShell in a hidden mode.
          Set objShell = CreateObject("WScript.Shell")
          
          objShell.Run "CMD /C START /B " & objShell.ExpandEnvironmentStrings("%SystemRoot%") & "\System32\WindowsPowerShell\v1.0\powershell.exe -windowstyle hidden -file " & "spotlight.ps1", 0, False
          
          Set objShell = Nothing
          This .vbs file can be launched from the Windows Task Scheduler:





          Thursday, November 7, 2019

          Quoridor boardgame on paper

          If you like the boardgame Quoridor, you may use this PDF to print it and easily have the game in a very portable way: as in a single sheet of paper. One paper allows you to play the game twice (both sides printed) with 2 or 4 players.

          You can use the corners of the paper as the pawns. With a pen, draw the walls on the board and mark the used walls at the bottom of the board.

          The abstract strategy game Quoridor is surprisingly deep for its simple rules. The object of the game is to advance your pawn to the opposite edge of the board. On your turn you may either move your pawn or place a wall. You may hinder your opponent with wall placement, but not completely block him off. Meanwhile, he is trying to do the same to you. The first pawn to reach the opposite side wins.

          Download files from urls in Excel sheet

          If you’d need to download several files based on some concatenated url’s it can be useful to build the url’s with Excel.

          This little macro can then be used to download all the url’s found on the active sheet and store the files in a folder.

          The target folder where the files need to be stored will be requested upon running the macro.

          Based on some example VBA found online.

          Sub DownloadUrlsOnSheet()
                Dim iRow, iColumn As Long
          
                Dim FileNum As Long
          
                Dim FileData() As Byte
          
                 Dim MyFile As String
          
                Dim WHTTP As Object
          
                    On Error Resume Next
          
                    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
          
                    If Err.Number <> 0 Then
          
                        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
          
                    End If
          
                On Error GoTo 0
          
                    targetDir = BrowseForFolder
          
                If (targetDir = False) Then
          
                    Exit Sub
          
                End If
          
                    If Dir(targetDir, vbDirectory) = Empty Then MkDir targetDir
          
                    Set rngRange = Nothing
          
                Set rngRange = Worksheets(ActiveCell.Worksheet.Name).Cells.Find("*", Worksheets(ActiveCell.Worksheet.Name).Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious)
          
                If Not rngRange Is Nothing Then
          
                    'if found then assign last non-empty cell row and colum index to the variable
          
                    lngMaxColumIndex = rngRange.Column
          
                    lngMaxRowIndex = rngRange.Row
          
                Else
          
                    MsgBox ("Error clearing data, please check logs")
          
                    Exit Sub
          
                End If
          
                    For iRow = 1 To lngMaxRowIndex
          
                    For iColumn = 1 To lngMaxColumIndex
          
                        currValue = Trim(Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Text)
          
                         If CheckURL(currValue) Then
          
                            TempFile = Right(currValue, InStr(1, StrReverse(currValue), "/") - 1)
          
                             WHTTP.Open "GET", currValue, False
          
                            WHTTP.Send
          
                            FileData = WHTTP.ResponseBody
          
                             FileNum = FreeFile
          
                            Open targetDir & "\" & TempFile For Binary Access Write As #FileNum
          
                                 Put #FileNum, 1, FileData
          
                            Close #FileNum
          
                             FileNum = FreeFile
          
                            Open targetDir & "\LogFile.txt" For Append As #FileNum
          
                            Print #FileNum, currValue & " --- Dowmloaded ----"
          
                            Close #FileNum
          
                        Else
          
                            FileNum = FreeFile
          
                             Open targetDir & "\LogFile.txt" For Append As #FileNum
          
                            Print #FileNum, currValue & " !!! File Not Found !!!"
          
                            Close #FileNum
          
                        End If
          
                     Next
          
                Next
          
                Set WHTTP = Nothing
          
                MsgBox "Open the folder " & targetDir & " for the downloaded files..."
          
                Shell "C:\WINDOWS\explorer.exe """ & targetDir & "", vbNormalFocus
          
             End Sub   Function BrowseForFolder(Optional OpenAt As Variant) As Variant
          
                 'Function purpose:  To Browser for a user selected folder.
          
                 'If the "OpenAt" path is provided, open the browser at that directory
          
                 'NOTE:  If invalid, it will open at the Desktop level
          
                     Dim ShellApp As Object
          
                      'Create a file browser window at the default folder
          
                Set ShellApp = CreateObject("Shell.Application"). _
          
                BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
          
                 'Set the folder to that selected.  (On error in case cancelled)
          
                On Error Resume Next
          
                BrowseForFolder = ShellApp.self.Path
          
                On Error GoTo 0
          
                       'Destroy the Shell Application
          
                Set ShellApp = Nothing
          
                       'Check for invalid or non-entries and send to the Invalid error
          
                 'handler if found
          
                 'Valid selections can begin L: (where L is a letter) or
          
                 '\\ (as in \\servername\sharename.  All others are invalid
          
                Select Case Mid(BrowseForFolder, 2, 1)
          
                Case Is = ":"
          
                    If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
          
                Case Is = "\"
          
                    If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
          
                Case Else
          
                    GoTo Invalid
          
                End Select
          
                     Exit Function
          
                  Invalid:
          
                 'If it was determined that the selection was invalid, set to False
          
                BrowseForFolder = False
          
                  End Function
          
             '
          
             Function CheckURL(URL) As Boolean
          
                Dim W As Object
          
                On Error Resume Next
          
                    Set W = CreateObject("winhttp.winhttprequest.5")
          
                    If Err.Number <> 0 Then
          
                        Set W = CreateObject("winhttp.winhttprequest.5.1")
          
                    End If
          
                On Error GoTo 0
          
                    If (Len(URL) < 4 Or Left(URL, 4) <> "http") Then
          
                     CheckURL = False
          
                    Exit Function
          
                End If
          
                        On Error Resume Next
          
                W.Open "HEAD", URL, False
          
                W.Send
          
                If W.Status = 200 Then
          
                    CheckURL = True
          
                Else
          
                     CheckURL = False
          
                End If
          
             End Function

          Thursday, March 15, 2018

          Create and/or update Outlook contacts from Excel

          When you have a good overview of all your contact details in an Excel file, it can be useful to exportExcel_2007 these towards Outlook (eg to allow automatic synchronization towards your mobile). When including contact pictures, the look and feel of Outlook and your mobile increases a lot by immediately showing the related pictures when receiving a mail or call.

          Since the contact details can change over time, I created the Excel macro below to update existing contacts and create the non existing contact within Outlook, including all available details of the contact.

          Using a ‘Reference’ sheet, in which the columns are references using a named cell, it’s easy to retrieve the contact details from the right column, while still being able to easily change the data or order. The data is retrieved using the Cells.Range option, eg: Sheets(sheetName).Cells(i, Sheets("References").Range("columnname").Value)

          In order to make sure the same contact is not added multiple times, the contact is first retrieved from the available Outlook contacts using the olFolder.Item.Restrict(…filter…) option based on the email address of the contact.

          Since I load the contact details from a DB using a linked Query within Excel, I also refresh the query data before performing the migration from Excel to Outlook.

          All details can be found in the macro source below and the example Excel file (including the macro).

          'Developed by myT, http://myTselection.blogspot.com
          Option Explicit
          
          
          Public Sub RefreshQueries()
          
            Dim wks As Worksheet
            Dim qt As QueryTable
            Dim lo As ListObject
          
            For Each wks In Worksheets
              For Each qt In wks.QueryTables
                  qt.Refresh BackgroundQuery:=False
              Next qt
          
              For Each lo In wks.ListObjects
                  lo.QueryTable.Refresh BackgroundQuery:=False
              Next lo
          
            Next wks
          
            Set qt = Nothing
            Set wks = Nothing
          End Sub
          'http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=167:import-contacts-from-excel-to-outlook-automate-in-vba&catid=79&Itemid=475
          
          
          
          Sub ExcelWorksheetDataAddToOutlookContacts()
              'Automating Outlook from Excel: This example uses the Items.Add Method to export data from an Excel Worksheet to the default Contacts folder.
              'Automate Outlook from Excel, using Late Binding. You need not add a reference to the Outlook library in Excel (your host application), in this case you will not be able to use the Outlook's predefined constants and will need to replace them by their numerical values in your code.
              
              
              
              Dim oApplOutlook As Object
              Dim oNsOutlook As Object
              Dim oCFolder As Object
              Dim oDelFolder As Object
              Dim oCItem As Object
              'Dim olItems As Outlook.Items
              Dim olItems As Object
              'Dim olContactItem As contactItem
              Dim olContactItem As Object
              Dim oDelItems As Object
              Dim lLastRow As Long, i As Long, n As Long, c As Long
              Dim firstRowToProcess As Integer, emailColumn As Integer, pictureColumn As Integer, processedColumn As Integer, itemsFound As Integer
              Dim sheetName As String, fullFilePath As String
              Dim updateExistingContacts As Boolean
              
              
              RefreshQueries
              
              'Config:
              sheetName = "Contacts"
              firstRowToProcess = 2
              updateExistingContacts = False
              
              updateExistingContacts = MsgBox("Update and overwrite fields of existing contacts? Pictures will always be updated. (Existing notes will be appended, not overwritten)", vbYesNo, "Overwrite")
              
              
             Application.ScreenUpdating = False
              ' turns off screen updating
              Application.DisplayStatusBar = True
              ' makes sure that the statusbar is visible
              Application.StatusBar = "Preparing export contacts to Outlook"
              
              'determine last data row in the worksheet:
              lLastRow = Sheets(sheetName).Cells(Rows.Count, "A").End(xlUp).Row
              
              'Create a new instance of the Outlook application, if an existing Outlook object is not available.
              'Set the Application object as follows:
              On Error Resume Next
              Set oApplOutlook = GetObject(, "Outlook.Application")
              'if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
              If Err.Number <> 0 Then
                  Set oApplOutlook = CreateObject("Outlook.Application")
              End If
              'disable error handling:
              On Error GoTo 0
              
              'use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
              Set oNsOutlook = oApplOutlook.GetNamespace("MAPI")
              
              '----------------------------
              
              'Empty the Deleted Items folder in Outlook so that when you quit the Outlook application you bypass the prompt: Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?
              
              'set the default Deleted Items folder:
          '    'The numerical value of olFolderDeletedItems is 3. The following code has replaced the Outlook's built-in constant olFolderDeletedItems by its numerical value 3.
          '    Set oDelFolder = oNsOutlook.GetDefaultFolder(3)
          '    'set the items collection:
          '    Set oDelItems = oDelFolder.Items
          '
          '    'determine number of items in the collection:
          '    c = oDelItems.Count
          '    'start deleting from the last item:
          '    For n = c To 1 Step -1
          '        oDelItems(n).Delete
          '    Next n
          '
              '----------------------------
              
              'set reference to the default Contact Items folder:
              'The numerical value of olFolderContacts is 10. The following code has replaced the Outlook's built-in constant olFolderContacts by its numerical value 10.
              Set oCFolder = oNsOutlook.GetDefaultFolder(10)
          '    Set olItems = oCFolder.Items.Restrict("[MessageClass]='IPM.Contact'")
              
              'Find contact to update, if not found add a new contact item
              
              
              'post each row's data on a separate contact item form:
              For i = firstRowToProcess To lLastRow
              'restrict info: https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/items-restrict-method-outlook
              'folder items info: https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/folder-items-property-outlook
                  Set olItems = oCFolder.Items.Restrict("[MessageClass]='IPM.Contact' And [Email1Address] = '" & Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value) & "'")
                  
                  itemsFound = 0
                  For Each olContactItem In olItems
                      
                      Application.StatusBar = "Updating Outlook contact " + olContactItem.FullName & ", " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value)
                      'matching contact found in Outlook and Excel
          			'web pictures should be stored in a local folder
                      fullFilePath = Replace(Sheets(sheetName).Cells(i, Sheets("References").Range("pictureurl").Value), "http://someimageurl", "C:\Users\username\Documents\Pictures\profilephotos\")
                      If FileExists(fullFilePath) Then
                          olContactItem.AddPicture (fullFilePath)
                      Else
                          'MsgBox ("Missing picture: " + fullFilePath + ", for member: " + olContactItem.firstName + " " + olContactItem.lastname)
                          Debug.Print ("Missing picture: " & fullFilePath & ", for member: " & olContactItem.firstName & " " & olContactItem.lastName)
                      End If
                      If updateExistingContacts = True Then
                          With olContactItem
                              'update existing contact fields https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._contactitem_properties.aspx
                              .firstName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value)
                              .lastName = Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              If (Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value) <> "") Then
                                  .Birthday = DateValue(Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value))
                              End If
                              .BusinessAddressStreet = "BusinessStreet 100"
                              .BusinessAddressCity = "BusinessCity"
                              .BusinessAddressCountry = "BusinessCountry"
                              .BusinessAddressPostalCode = "BusinessPostal"
                              .BusinessHomePage = "http://www.company.com"
                              'do not remove or duplicate existing categories, set some desired category when not yet set
                              If (InStr(.Categories, "Business") = 0) Then
                                  .Categories = .Categories & ",Business"
                              End If
                              .CompanyName = "Company"
                              .Email1DisplayName = Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value)
                              .FullName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              .FileAs = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              '.gender = Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value)
          '                    If (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "M") Then
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olMale
          '                    ElseIf (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "F") Then
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olFemale
          '                    Else
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olUnspecified
          '                    End If
                              .HomeAddressCity = Sheets(sheetName).Cells(i, Sheets("References").Range("city").Value)
                              .HomeAddressCountry = "DefaultCountry"
                              .HomeAddressPostalCode = Sheets(sheetName).Cells(i, Sheets("References").Range("postcode").Value)
                              .HomeAddressStreet = Sheets(sheetName).Cells(i, Sheets("References").Range("street").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("street_number").Value)
                              .JobTitle = Sheets(sheetName).Cells(i, Sheets("References").Range("current_function").Value)
                              '.Language = Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value)
                              .MobileTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("mobile").Value)
                              .BusinessTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("phone").Value)
                              .BusinessFaxNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("fax").Value)
                              .Department = Sheets(sheetName).Cells(i, Sheets("References").Range("division").Value)
                              .ManagerName = Sheets(sheetName).Cells(i, Sheets("References").Range("manager").Value)
                              .WebPage = "http://www.company.com"
                              If (InStr(.body, "Staff number: ") = 0) Then
                                  .body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
                                  .body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
                                  .body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
                                  .body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
                                  .body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
          '                    Else
          ' keeps original body note, but duplicates all data if run mulitple times
          '                        originalBody = .body
          '                        .body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
          '                        .body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
          '                        .body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
          '                        .body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
          '                        .body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
          '                        .body = .body & vbCrLf & originalBody
                              End If
                          End With
                                                  
                      End If
                      Sheets(sheetName).Cells(i, Sheets("References").Range("processed").Value).Value = "OK"
                      olContactItem.Save
                      itemsFound = itemsFound + 1
                  Next
                  
                  If itemsFound = 0 Then
                      Application.StatusBar = "Adding Outlook contact " & Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value) & ", " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value)
                      'Using the Items.Add Method to create a new Outlook contact item in the default Contacts folder.
                      Set oCItem = oCFolder.Items.Add
                      'display the new contact item form:
                      'oCItem.Display
                      'set properties of the new contact item:
                      With oCItem
                              'update existing contact fields https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._contactitem_properties.aspx
                              .firstName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value)
                              .lastName = Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              If (Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value) <> "") Then
                                  .Birthday = DateValue(Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value))
                              End If
                              .BusinessAddressStreet = "BusinessStreet 100"
                              .BusinessAddressCity = "BusinessCity"
                              .BusinessAddressCountry = "BusinessCountry"
                              .BusinessAddressPostalCode = "BusinessPostal"
                              .BusinessHomePage = "http://www.company.com"
                              'do not remove or duplicate existing categories, set some desired category when not yet set
                              If (InStr(.Categories, "Business") = 0) Then
                                  .Categories = .Categories & ",Business"
                              End If
                              .CompanyName = "Company"
                              .Email1DisplayName = Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value)
                              .FullName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              .FileAs = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
                              '.gender = Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value)
          '                    If (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "M") Then
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olMale
          '                    ElseIf (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "F") Then
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olFemale
          '                    Else
          '                        .gender = Microsoft.Office.Interop.Outlook.OlGender.olUnspecified
          '                    End If
                              .HomeAddressCity = Sheets(sheetName).Cells(i, Sheets("References").Range("city").Value)
                              .HomeAddressCountry = "DefaultCountry"
                              .HomeAddressPostalCode = Sheets(sheetName).Cells(i, Sheets("References").Range("postcode").Value)
                              .HomeAddressStreet = Sheets(sheetName).Cells(i, Sheets("References").Range("street").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("street_number").Value)
                              .JobTitle = Sheets(sheetName).Cells(i, Sheets("References").Range("current_function").Value)
                              '.Language = Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value)
                              .MobileTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("mobile").Value)
                              .BusinessTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("phone").Value)
                              .BusinessFaxNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("fax").Value)
                              .Department = Sheets(sheetName).Cells(i, Sheets("References").Range("division").Value)
                              .ManagerName = Sheets(sheetName).Cells(i, Sheets("References").Range("manager").Value)
                              .WebPage = "http://www.company.com"
                              If (InStr(.body, "Staff number: ") = 0) Then
                                  .body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
                                  .body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
                                  .body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
                                  .body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
                                  .body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
                              End If
                      End With
                          
                          'mark as done
                          Sheets(sheetName).Cells(i, Sheets("References").Range("processed").Value).Value = "OK"
                      fullFilePath = Replace(Sheets(sheetName).Cells(i, Sheets("References").Range("pictureurl").Value), "http://someimageurl", "C:\Users\username\Documents\Pictures\profilephotos\")
                      If FileExists(fullFilePath) Then
                          oCItem.AddPicture (fullFilePath)
                      Else
                          'MsgBox ("Missing picture: " + fullFilePath + ", for member: " + oCItem.firstName + " " + oCItem.lastname)
                          Debug.Print ("Missing picture: " & fullFilePath + ", for member: " & oCItem.firstName & " " & oCItem.lastName)
                      End If
                      'close the new contact item form after saving:
                      'The numerical value of olSave is 0. The following code has replaced the Outlook's built-in constant olSave by its numerical value 0.
                      oCItem.Close 0
                  End If
              Next i
              
              'quit the Oulook application:
              oApplOutlook.Quit
              
              Application.StatusBar = "Outlook " & lLastRow & " contacts exported"
              Application.ScreenUpdating = True
              
              'clear the variables:
              Set oApplOutlook = Nothing
              Set oNsOutlook = Nothing
              Set oCFolder = Nothing
              Set oDelFolder = Nothing
              Set oCItem = Nothing
              Set oDelItems = Nothing
              Set olContactItem = Nothing
              Set olItems = Nothing
              
              MsgBox "Successfully Exported Worksheet Data to the Default Outlook Contacts Folder."
              
               
          
          End Sub
          
          Function FileExists(fullFileName As String) As Boolean
              FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
          End Function