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:
- In Excel’s File menu, select Options (at bottom left). In left column menu, select Trust Center. Then click gray Trust Center Settings button. Under Macro Settings, select the “Disable all macros with notification” radio button and select the “Trust access to the VBA project model” checkbox. Click OK to close.
- Make sure you have Developer mode added to your Excel main menu: If not, right click anywhere in Excel’s main upper horizontal menu and select Customize the Ribbon and select the Developer checkbox as indicated in steps 1-3 of the screenshots at http://www.excel-easy.com/vba/create-a-macro.html covering this one-time setup.
- You will also want some common references pre-loaded in Excel: Click Developer tab (you just added in the previous step), then click Visual Basic (leftmost button under that). In the Tools menu, select References. The first several checkboxes should be selected (if they aren’t already), and particularly add the checkboxes for “Microsoft VBScript Regular Expressions 5.5” and “Microsoft Visual Basic for Applications Extensibility” (and if you do web scraping in VBA, the web-browser interaction ones as described here). Unfortunately, you need to make sure other users of this macro also have done these three steps, but at least it’s a one-time procedure that only takes a minute (see the top-voted answer here to learn more).
- When you actually open an Excel file containing a macro (typically filetype .xlsm) you may see a ribbon just under the top ribbon with a yellow Security Warning that macros have been disabled and a button to Enable Macros. You need to click the button to run the macro(s), assuming you know it’s a safe macro.
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.
