Several sourcers and recruiters contacted me after my last post in this series in frustration about having tried some of what I showed to grab email addresses off web pages and then got stuck at various points. Since many got the code running fine who had no previous programming experience (kudos: we may tap you to present in the Programming track at a future SourceCon conference!), I know the rest of you can, too.
This is because of two abilities that sourcers have in spades: 1) how to quickly find relevant information online and 2) comfortable networking to find people who have desired knowledge/skills. I am not a classically-trained programmer (barely a novice coder), but number one and number two are the main reasons I can quickly program useful solutions to practical problems for my recruiting colleagues. For example, when I was trying to apply one of Steven Jiang’s methods to extract phone numbers from a web page to a folder of Outlook email messages, I simply googled Excel VBA how to find all numeric characters in a string, and the results included numerous code snippets that were easily adaptable to my need. If that hadn’t worked, I would have networked with sourcing colleagues who are better programmers.
Why was Python not in my Google query? Jiang is rightly using Python as the language for his free Sourcers Who Code programming course running this summer (I also use Python and appreciate its value and flexibility), but when I am sharing a solution that other non-technical team members will need to run themselves, I often code it instead in VBA, Microsoft’s native programming language, because my solution can be built into a standard Excel file, no need for my colleagues to install anything to use it. It’s not a hard language to learn because it’s widely used and been around so long (VBA was added to Excel in 1993!) that many examples and free tutorials exist online, beyond Microsoft’s own documentation — and if you’re using an older version of Excel, under the “Viewed Most” boldface subsection, click the “Excel 2010” hyperlink). You can google Excel VBA Tutorial (not surprisingly, many results are on YouTube) but you’ll probably prefer ones that combine step-by-step text with visuals, such as Anthony Sun, Excel Easy, Excel Functions and particularly AutomateTheWeb for web scraping (videos and step-by-step, screenshot-annotated written how-to’s), among others I’ve found useful.
Unfortunately, the issue with web scrapers created in Excel VBA (even by that last URL) is that some websites cannot be parsed by it. That is because those sites are using something dynamic behind-the-scenes to generate the page content whereby the content is not visible if you were to do a simple View/Page Source in your browser. This is why a coding solution saved to a Google Chrome extension is often a better choice: you can utilize any programming language (and thus avoid the limitations of VBA), and the solution is easily accessible by most users (all they need is the Chrome browser).
However, if your sourcing/recruiting business problem relates to something within Microsoft Office (e.g., you have some massive find, filter, copy and paste, etc., job that bounces between one or more apps — Word, Outlook, PowerPoint, Excel, Access, etc.), now you have something Excel VBA is perfect to solve.
Tips to Run Things Successfully in Excel VBA
Let me share a few one-time steps that address the most common initial frustrations so you can run (or edit) code by me or others in VBA from your computer:
Where Excel VBA Shines Bright Like a Diamond
In this next useful example, I’ve created something that: 1) lets you select any Outlook folder/subfolder in your mailbox (if you have access to multiple shared mailboxes, it lets you pick one of those folders instead); and automatically 2) grabs all the email addresses in the body of those messages (including the senders of the messages); 3) plops them into an Excel file; 4) de-duplicates and sorts the results; and 5) inserts references after each email address telling you the subject line, sent date and sender of the message where that email was found, to make it convenient if you want to go back to the message source for context/reference. If you’re on a corporate network with Outlook Exchange, it works just as well on message folders there.
This is useful for situations such as when you run variations of a particular email campaign over time to a certain subset of talent, or perhaps a concentrated campaign over a holiday when a lot of people have out-of-office messages, and you get a zillion auto-replies that contain email addresses of their colleagues. You can move all those messages (or a desired subset of them, e.g., sort by date or filter by subject line keywords) to a new Outlook subfolder, and then run this macro on those.
You can view and download the code, extensively commented to help you follow along, at https://github.com/gutmach/SourceConExtras/blob/master/OutlookEmailsToExcelUsingVBA but remember to have the Regular Expressions library loaded in Excel already for you or whichever colleagues will use it, as explained in tips number one through three in the previous section, as RegEx is required to run this script.
In future posts, we’ll focus more on Chrome extensions, JavaScript and Python, but don’t forget the convenience of Excel VBA when your solution deals with something native to Microsoft Office or requires that colleagues need to run the code themselves, and particularly if personal login-level access to Chrome (required to install an extension) is blocked on your work computer (those of you working in super-secure companies may know what I’m talking about) — in which case your workaround is to run it from a personal computer instead and send the results back to your work computer.
Disclaimer: While my code has been tested to work successfully, I cannot guarantee everything works properly on every system, so I cannot be held responsible for any adverse impact on anything you may try related to the above. If you have questions, please ask them in the Sourcers Who Code group on Facebook, on Codecademy.com’s Forums, or on StackOverflow.com. Or grab one of the presenters in the Programmers track at SourceCon Austin.
