Email automation, C# and PowerShell

Some time ago I did an Email automation application in my Data Warehouse. In April this year I got a

request from “the business”, ‘can you help me develop a macro that creates PDFs’? ‘Uh?’ I replied.

The colleague had a very large Excel workbook with +hundred sheets each of them should be separate PDF files. I’m really bad at Excel I have problems with auto sums, never had written a macro I only done some visual basic scripts. But I promised to look at it and then I forgot all about it. Imay my colleague asked if I had looked at the Excel, I promised to look at it which I did. I wondered why my college wanted PDFs is that not more to it? And then I forgot about it. In June my colleague asked again if I had had time to look at her Excel, now I felt i had to do something, so I asked if the PDFs was all she wanted, can you explain what the source for this Excel is and what you are going to do with the PDFs.. I got a long story about compile material for invoices and create this multi-sheet Excel,create PDFs and send them to up to six recipients of which she only had the names not email addresses. As always when you have an old manual process inherited from person to person

explained to you it sounds complex. I did not understand much about it, but I understood she had a lot of mails to send to a lot of names/recipients.

This was more than I could do with an Excel macro.

I had a large multi-sheet  Excel, each sheet should be sent to a company, and the recipients  

in another Excel Sheet with six columns and one row per receiving company..

First I created a PowerShell script splitting the multi-sheet Excel into separate Excels. I asked if I could attach

an excel to each mail instead of cut and paste a PDF into the mail body as they did. This was one of the odd  tasks in this manual process, this was just done as it was the way they always had done this. I was told this was actually much better with Excel attachments, since these invoices were no legal invoices, just notifications to subsidiaries within the Enterprise.

Next I took care to the translations of recipients names into emailaddresses. I created a C# program

listing the Global Address List, with it I should be able to translate any Enterprise Employee name into

an Email address, of course I was wrong, I missed about 30% of the recipients, it turned out not all subsidiaries was incorporated into the Enterprise mailsystem. I then listed all users in the Active Directory, this time I used PS scripting, after the C# adventure I decided to go for PS scripts, it is much faster to develop and fast enough for this kind of automation. Unfortunately I still missed about 10% of the recipients. For those I created a list manually and also accepted email addresses directly in the recipients list. I had to extract data from two ‘got-it-all’ sources and still had to add a manual source and allow for name and email address specifications of recipients. If I had one source for all recipients I would have saved quite some time, only the C# program extracting GAL took me the better part of a Sunday.

I was given a static mail body to be included in the mails. So the last thing I did was a PS script for the mail

automation. PS scripting is actually very nice for Email automation, just for the hell of it I added variable substitution for the mail template so I could personalize the mails.

Doing this Email automation exercise led me to some conclusions:

PowerShell scripting is very good for automation of manual tasks

Good H/R master data is essential for automation of tasks where people/employees are  involved.

Automation is fun. having done the output part of a manual process, I’m looking forward to take a stab at the input part.

In the next post I show some code:)

Recruitment process out of control

The recruitment process has gone haywire or rather been kidnapped by HR. These days when you apply for a new job you do not only send in the CV and then eventually be selected for interviews by HR and recruiting manager and maybe a subject matter test of some kind. No these days you first have to pass a HR test over the Internet most like created by a hired recruitment company often evaluated by a so called robot. These tests are supposed to measure your IQ and ability to solve complicated problems. I have come across a few such test and they look more like bullshit than anything else in my eyes. You have very little time for each question e.g. the instruction take longer time to read (for a dyslectic) than to solve/reply the question. That is a test for speed reading not for what I think the question is intended to answer. And what does the ability to fill in the missing picture in a Sudoku matrix has to do with the ability to solve complex problems? One day of training on these tests improves your score a lot. Do that day increased you IQ and your ability to solve complex problems? I doubt. 
I am confident this new sifting test process is more harmful than helpful.
I recently heard this true story about a very distinguished science professor who had founded a  research company and let a recruitment company manage the recruitment process. To his disappointment his best PhD student did not apply, so he called him up and asked 'why didn't you apply?'. 'I didn't pass the test' was the reply. The professor called up the recruitment company and told them 'you have turned down a world leading expert' and requested to see the tests and all result. He then took over the recruitment process and hired the PhD who failed the test.     

In memoriam

In some posts I have written about colleagues who have left for new challenges outside the company. This post is different, last week Carina passed away after a short period of severe illness. We did not work together, but we were colleagues, when I was an IT manager she was employed as a secretary. Later when I moved to the HQ she worked as an IT admin and the last years a communicator for the corporate IT  in the same office as I work. I who used to came in first in the morning and leave last in the evening was second when Carina joined us. She was dedicated and hard working like few, but that is not what she is remembered for, it is her positive attitude always cheerful and friendly, we always had a a small chat before the other came in. When she occasionally left before me, I used to say "Working part time now, are we?" And we laughed a bit about that. There is no more "Good morning Lars!" when I arrive at the office. It is funny I did not realized how much that actually meant to me until she become ill just a few months ago. There is a big empty space in the office, farewell Carina my thoughts are with you and your family.