More Automation

I’ve been working on a set of new projects – some of it pertains to gathering data and performing analysis and some of it requires a bit of automation. The analytical piece is not done and probably will be ongoing. The analysis is only at the beginning stage so there is not much to relay yet.

But there have been some automation activities and some of it is pretty cool.

But first, a bit of update on the original automation project that I did last year. The field managers produce these reports on Monday mornings, using data from a system from a third party vendor. Now this system has a very unfriendly Excel download where data do not come in a nice flat file format conducive to vlookups or pivot tables. Instead data come in floating somewhere in the download. Some reports have a particular data falling in a certain column but other kinds of data can fall there too. Other reports may have data falling in different columns from download to downloads or even fall into two different columns in the same download. The downloads are all over the map. It took me maybe five or six months to figure out how to pull the data and then to develop macros to do the job. We now have that report running smoothly for the most part.

Then, about two weeks ago the supplier changed the reports just enough to throw off my spreadsheet! One of the reports had a column shifted and the other report had two additional rows added. Not good.

The latest project I’m working on is pulling together StrengthsFinder data. First I had to get out emails to everybody, giving them the link to the assessment. There were going to be 200+ emails to send out and, knowing myself, if I did it manually, I was likely to miss someone. Getting everybody’s email addresses was not much of an issue: the company does have a rather consistent set of rules for email addresses so I was able to develop email addresses using formulas for the non-corporate folks. But I did do one thing: I created a macro that would pull email addresses from Outlook for the corporate folks, based upon the person’s name. The corporate folks were in Outlook; the non-corporate folks were not. It was really cool to be able to pull email addresses from Outlook using a macro.

 

[divider]Macro to pull email addresses from Outlook[/divider]

First, this macro came from Stack Overflow; I didn’t develop it. I only tweaked it to fit my situation. The other thing is you need to make sure your reference tools in the Developer tab has Microsoft Outlook 15.0 Object Library and/or Microsoft Office.Net Private Object Library checked. Macro won’t work unless one or both is checked.

Second, the list of names should be in column A (the “r” is the list of names). The names should be the same way you search for email addresses. So if you do last name, first name when you do a search, then column A must also be last name, first name.

Dim o, AddressList, AddressEntry
Dim c As Range, r As Range, AddressName As String
Dim exchangeUser As Outlook.exchangeUser

i = Cells(rows.Count, 1).End(xlUp).Row + 1

Set o = CreateObject(“Outlook.Application”)
Set AddressList = o.Session.AddressLists(“Global Address List”)
Set r = Range(Cells(1, 1), Cells(i, 1))

For Each c In r

AddressName = Trim(c.Value)
For Each AddressEntry In AddressList.AddressEntries

If AddressEntry.Name = AddressName Then

Set exchangeUser = AddressEntry.GetExchangeUser
c.Offset(0, 2).Value = exchangeUser.PrimarySmtpAddress
Set exchangeUser = Nothing
Exit For

End If

Next AddressEntry

Next c

MsgBox (“Program done”)

[divider]End of Macro section[/divider]

 

The next step was to send out the emails and Word has the perfect set up for this: the mail merge feature. The body of the message will be in Word. You then go into the mail merge feature and you pretty much work your way from left to right, starting from “Start Mail Merge” where you select email addresses. You need to have your Excel with names already set up. One of the columns needs to have a field header called “E-mail address”. If you want to insert names after your greetings (such as hello), then you also need to have a column of names and a header that you will reference. You will pick the Excel file using the “Select Recipients” in Word. Once you have selected the file with the list of email addresses, you are ready to insert any fields you may have, using the “Insert Merge Field”. Make sure your cursor is placed where you want the new field to be inserted. To preview how your email will look, choose “Preview Results”. Once everything is as you want it, then finish with “Finish and Merge”. It will be in the last step where you will enter the subject title. It is so easy and quite the time saver.

I’ll stop here and the next post will be about some macros I developed for the analytical portion of StrengthsFinder.

By the way, my top 5 themes are: Maximizer, Ideation, Strategic, Learner, Intellection.