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:)
No comments:
Post a Comment