Automated Gathering and Best Email Selection Via Hunter.io API & Excel Macros

If you use Hunter.io to retrieve email addresses connected to a list of domains as part of your prospecting process…this tool should be of interest to you.

Using a mix of Hunter.io API, Neverbounce email validation and some excel macro shenanigans, this tool retrieves, filters and then selects the best email addresses to use in link building outreach for a list of domains.

I’ve tried to make this as simple as possible to follow, but a small amount of tech knowledge is required. However, if you can use a computer and follow instructions, you should be fine.

What This Tool Does

From a list of root domains in column A of an excel spreadsheet, at the click of a few buttons, this tool:

  • Uses the Hunter.io API to retrieve up to 10 emails per root domain.
  • Deletes generic email addresses and job titles that we do not want to email (CEO, COO, president, [email protected], [email protected], [email protected] – to name just a few.)
  • Quickly deletes from the remaining list any email addresses not verified by a 3rd party service, (I use Neverbounce.)
  • Uses a macro to prioritise and then select the best email address we should send to, when there is more than one email per domain found via Hunter.io, (editors, authors, writers are preferred over marketing, sales, customer service, etc.)

Who This Will Work For – Prerequisites and Required Tools

In order to use this tool, you need:

  • Microsoft Windows Excel 365 – The latest version. This will not work in excel 2016 or before, neither will it work for Excel on MAC OS. Sorry.
  • A paid up Hunter.io subscription, and to enter your API key into the macro in order for it to work.
  • OPTIONAL: Neverbounce, or another 3rd party email verification service. Not essential, but strongly recommended.

Video Walkthrough

Before providing the spreadsheet (downloadable further on), I will show it’s used first. So…

This is the workflow for using this tool, when used as given and downloaded, without any editing on your part – except for adding your unique Hunter.io API key which we covered in the last section.

UPDATE: In the video, I say you MUST have root domains in column A. It actually works just fine with any URLs in column A, no need to strip it to root domain at all. But I’m not gonna re-do the video, just keep in mind this change.

Quick Reference Checklist for Use

As a quick reminder for those who may go through the video but forget a step, here is a short summary checklist of how to use the tool:

  1. Download the provided spreadsheet further on in this article.
  2. Make sure you follow instructions further down in this article to enter your Hunter API key into the macro.
  3. Make sure there is a list of URLs or root domains in column A (it works with either, so I am wrong in the video saying root domains only.)
  4. Click ‘Emails from Hunter’ menu in the downloaded spreadsheet, to have access the macros used in this tool.
  5. Run: Macro 1 – ‘Hunter from Sheet’ – And wait. You will then have all emails from hunter pasted into a new sheet called ‘HunterSheet’, retrieved via API.
  6. Run: Macro 2 – ‘Delete Unwanted’ – To delete a load of blacklisted and unwanted job titles and generic emails we do not want to email. (You can skip this step if you like.)
  7. There will now be a new file dumped on your desktop called: ‘Emails_To_Verify.csv’. This is the file you drop into Neverbounce, or another tool of your choice to validate the emails. (you can skip this step if you like.)
  8. After validating your emails, copy and paste the valid email addresses into the sheet named ‘Valid_Emails’ (You can skip this step if you like.)
  9. Run: Macro 3: – ‘Delete Unverified’ – To delete all emails that do not appear in the list of valid emails you pasted into sheet ;Valid_Emails’
  10. Run macro 4 – ‘Select Best Emails’ – And it will create a new spreadsheet called ‘BestEmails’ containing the best emails selected, one per domain, ready for you to use in outreach.

Note: At this point, you can safely delete the spreadsheets: ‘HunterSheet’, ‘Valid_Emails’ and ‘HunterSheet2’, I just leave them in for troubleshooting purposes and for info if needed.

Caveat: Step 1 Changes for Over 1,000 Domains

If you wish to select emails for over 1,000 domains, I suggest NOT to use macro 1. You can, but it will take like 15 minutes+ (15 minutes for every 1k emails in fact.)

In this case, to use the tool to only ‘delete unwanted emails‘, followed by ‘deleting unverified emails‘ and finally ‘selecting the best ones‘ – all without using the Hunter API, you must:

  • Use the Hunter.io website
  • Export ALL emails
  • Paste the export into your spreadsheet
  • Start the process with macro 1a, to format the export to how I need it to be.

The process for Using the sheet with 1k+ domains is outlined in the video below:

Why I Made This Tool

I made this tool because using Hunter.io to find the BEST email addresses for outreach, is a time consuming and error prone process.

If you choose only one email address per domain in Hunters settings, it will often supply the wrong one to who you are after if your goal is to reach someone who will link to you (for example, an editor, rather than a company director.)

However, if you export 10 emails per domain from Hunter, then you need a good and reliable way to find the BEST 1 from 10 to email, that will result in the highest conversion rate of emails to links, by emailing the person most likely to link to you.

The process can be long, slow and inefficient. That is, unless you hard code it with baked in, solid rules to complete the process semi-automatically.

So that is the tool I created.

Five Macros and Their Purpose

In total, there are 5 macros in this tool. Here is what each of them does.

1. Hunter From Sheet

At the click of a button, this macro takes a list of domains in column A in the active worksheet, and using the Hunter.io API, retrieves from 0 to 10 emails for the domain, depending on what Hunter has found and is in their database.

This saves time over having to upload emails into Hunter, export them, import them back into your sheet, and so on.

I would say this method is good for up to 1,000 root domains MAXIMUM. Otherwise it’s just too slow. It can be used for more, but I have never bothered to.

1a. Format Hunter Export

As discussed: If you have a large number of root domains you are trying to find emails for, macro 1 isn’t going to cut it. It’s just too slow.

So for in excess of 1,000 root domains, you should instead:

  • Upload the domains in Hunter.io, requesting 10 emails per domain.
  • Export ALL (regardless of confidence score!)
  • Copy and paste them into the spreadsheet.
  • Run Macro 2 ‘Format Hunter Export’, to format the data into the correct columns and layout, in order to use the rest of the macros in this tool effectively.

2. Delete Unwanted

Macro 3 ‘Delete Unwanted’ deletes a list of ‘generic email addresses’ and job positions that we do not wish to email when outreaching for link building (high level management, board members, complaints departments etc. that do not run or touch their websites.)

I have a long list I decided on, with many positions and generic email addresses that I delete, which I will detail later along with my logic.

I will also show how you can edit this list, in order to decide on your own which, if any, generic emails and job titles YOU would like to delete and not email. But as a default, my way should do.

If, however, you wish to maximise the number of email addresses you have in the final result, regardless of job position or ‘generic email’, simply do not run macro 2 at all. Skip it.

Not running macro 2, will leave in email addresses such as [email protected], [email protected], [email protected], [email protected] etc. as well as job positions such as CEO, CTO, COO, president, chair and more.

Maybe you want to email these people if nothing is better is found / if it’s the only email address found for a domain?

This is up to you!

3. Delete Unverified

I use Neverbounce in order to verify email addresses, and delete non-existent fake ones, spam traps and so on.

To keep deliverability high, you DO NOT want to email all and any email address you find. This will result in a bad email sender reputation VERY quickly.

So before moving onto step 4 and finding the best email addresses, I verify the ones found first.

I verify them BEFORE picking the best ones, because you want to pick the best emails from a list of already verified emails to get the maximum amount in your final list.

If you did it the other way around, if you picked one best email per domain and THEN verified, you would end up with fewer prospects when some of the single emails chosen turn out be fake or a spam trap, then get deleted.

4. Select Best Emails

Hunter returns up to 10 emails per root domain you enter into the tool. Even after deleting many generic email addresses and job titles, and deleting non-validated emails, you can still have anything up to 10 email addresses for one domain. So…

You need a way to select the BEST one from the many, that logically makes the most sense to email, from all that remain.

When outreaching for links, ideally you want to contact editors, authors, writers…and not customer support, sales or technical.

The final macro takes the list of all emails along with additional hunter data, and uses it to select only one email per domain according to an algorithm I planned out, aiming to maximise conversion rates by emailing the right people.

I will explain how this algorithm works, and how you can change it to suit your own particular ideas, later in this guide.

You Don’t Have to Use All Steps – And Can Change Them to Suit Your Needs

Although all the macros in this tool have been designed and written together to get the best batch of verified emails possible with link building outreach in mind, you can use them independently…and tweak them for your own purposes and goals.

Macro 1 can be used to get email addresses from Hunter.io for anything from a single root domain up to 1,000, and puts them into a new spreadsheet for you. You can stop there if you wish, and then use your own process to filter them down.

Macro 2 can be edited to delete job roles and emails that YOU decide on quite easily. You do not have to accept the list of exclusions I have programmed in. I will explain later how you can do this.

Macro 3 is basically ‘compare and delete.’ You paste a list of validated emails into the ‘Valid_Emails’ sheet, run macro 3, and if an email in what Hunter found is NOT in the ‘Valid_Emails’ sheet, it gets deleted. NON-valid emails gone. However, you can skip this step entirely if you wish, just ignore macro 3 and the ‘Valid_Emails’ sheet.

Macro 4 can be used to select the best emails without first running macros 2 or 3 first. If you do not wish to delete any generic emails, job titles, or validate emails, you can simply run macro 4 to select the best after running macros 1 or 1a.

Macro 4 can also be changed by you to prioritise and select different emails to what I think and suggest are best. There’s no right or wrong! Well, there is, but your right may be more right in your mind, so you can tweak it and I will show you how.

Download – And Set Up Before Use

Download

First of all, download the macro enabled worksheet from here:

<<Spreadsheet Download Link>>

That’s the easy bit out of the way!

Retrieve Your Hunter.io API Key

In order for the spreadsheet to retrieve emails from Hunter.io, you need to enter your API credentials.

Go to Hunter.io, click the account menu top right, and then ‘</> API’:

Screenshot of Hunter.io account menu

Next, you need to click the eye icon to reveal your API, and then copy it for pasting into a macro in the next step:

Screenshot of Hunter.io API settings menu

Enter Your Hunter.io API into the Macros

First of all, let’s ensure you have the ‘Developer menu’ enabled in Excel. Click ‘File’ top left corner, then ‘Options’ down at the bottom.

In the screen that pops up, go co ‘Customize Ribbon’, then make sure ‘Developer’ is ticked, then click ‘OK.’

Next, you need to click the ‘Developer’ tab in your excel menus, and then click ‘Visual Basic’ on the far left:

You will then be presented with the VBA editor where all the code resides.

On this screen, open up the ‘Modules’ tree, and double click the ‘Macros’ branch, so you see the ‘Macros’ code in the main editor on the right.

Once here, you need to enter your Hunter.io API key that you copied earlier, between the quotation marks, replacing the sentence as intructed, so that the line will look as follows:

Call Z_Hunter_From_Sheet("8476297698ab809cd067e98123fa78f9683abe34")

See the screenshot below for where to enter it:

Now hit the save button, (floppy disk icon) at the top of the code editor screen, and you are ready to use the tool.

Selecting the BEST Emails

A lot of the ‘power’ of this process, is the way the tool selects the best email out of many, that should in theory result in the best people being contacted, who have the power to change a link on a website.

The first step in that process is deleting email addresses and job positions of people who will likely never link to you.

I Based This on ‘Reasonable’ Data

When designing this system, I asked a ton of other SEOs to send me Hunter.io exports, then combined them with all of my own, so I could extract all the different types of ‘generic’ email addresses and job positions that Hunter.io classifies.

I then asked some to let me know which emails they’d sent to that resulted in links placed, and did not delete any of these (with the exception of some outliers.)

From the remainder of the generic emails, I just used logic and intuition to decide the people I really shouldn’t be emailing, as it likely leads to a dead end.

You do not Have to Delete any Emails – The Choice is Yours

Macro 2 – ‘Delete Unwanted’ is the macro that deletes potentially a ton of emails.

You do not have to run this if you would rather leave these in, and hence have more chance of having at least SOMEONE to email at a domain, regardless of their job position.

Personally, I’d rather not email people who are very unlikely to link to me. I believe it’s less spammy, you get less hate back in your inbox, and fewer spam complaints resulting in better future deliverability.

You Can Edit and Choose What Emails and Job Roles Get Deleted

You may have different ideas on which emails and job roles you’d like to delete. After all, we all have different ideas and processes.

So in the next section I will explain how you can change what gets deleted to suit your own systems.

Editing Which Emails and Job Positions Get Deleted

Hunter.io returns up to 10 email addresses per domain, and can include directors, CEOs, abuse emails, job application email addresses and more.

To place a link, we want to be contacting editors, authors, webmasters and the like. The people who actually have the power and job responsibility to go in and edit links.

Therefore, before my process selects the best email from many at the end of the process, I first delete a ton of emails for people who I believe will either:

  • Not have the ability to change links, as it’s not in their job role.
  • Might get pissed at receiving such emails, auto-delete, or worse jam the SPAM button and hurt your future deliverability.

The first step in my process for this is to delete the following email addresses:

[email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected],
 [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected],
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], s[email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], 
[email protected]

Any email address that Hunter.io finds that is in the list above, gets deleted when macro 2 is run.

The next step in the process is deleting a few ‘positions’ that Hunter.io identifies, job positions that I believe we do not want to email. The (almost) full and short list is this:

CEO, CFO, CPA, CMO, CCO, CDO, CIO, CPO, CAO, 
executive, board, counsel, realtor, account, 
president, chair

However, there are two more that are deleted in a smaller section of code, because they did not fit into a ‘partial string deletion’ process, and they are the CTO and COO.

If you wish to keep the email addresses, or any of the job positions listed above in your potential list of prospects, then you need to edit some code as shown in the following video.

Also, if there are generic email addresses and job positions that you do not wish to email that I haven’t included in those lists, the video shows you how to add these too.

Don’t worry, it is VERY simple to do:

Editing Best Email Selection Algo – Sorry, Not This Time!

I have a system for finding the best emails that is…a little bit complicated shall we say?

I have a scoring system that works on whether an email address is personal or generic, the job role or type of generic email address, then take the highest scoring e-mail possible.

I’ve struggled to find a way to explain it in simple terms, along with the use of a second spreadsheet and macro I use, to be able to edit this yourselves. It’s a bit too damn complicated 🙁

Therefore, for now, I’ve decided to post the tool as is, using my scoring system, but I will circle back to this in time and re-write the code to do it in a simpler way, making it so you can use your own scoring system and selection process.

How My Sheet Chooses Best Email – So You Know What You’re Getting

I basically start with a scoring system, that gives a numerical value to emails based on whether they are ‘personal’ emails, or ‘generic’.

I then give another score depending on the job title they have, or the generic email they have.

I then total up these scores and use them as the first step to prioritise which emails to choose.

For your information, this is the complete dataset that gets scored as I use it, where:

  • Type = Personal email or generic
  • Position = Job title or generic email
  • Total score = How they are ranked, higher number gets chosen first.
Type	       Position	                      Total Score
personal        editor	                        1500
personal	senior editor	                1495
personal	editor in chief	                1490
personal	managing editor	                1485
personal	associate editor		1480
personal	executive editor		1475
personal	editorial director		1470
personal	deputy editor			1465
personal	assistant editor		1460
personal	contributing editor		1455
personal	web editor			1450
personal	writer editor			1445
personal	writer and editor		1440
personal	editor writer			1435
personal	digital editor			1430
personal	editorial assistant		1425
personal	online editor			1420
personal	content editor			1415
personal	digital content editor		1410
personal	content management		1405
personal	content manager			1400
personal	senior writer			1395
personal	content writer			1390
personal	staff writer			1385
personal	senior staff writer		1380
personal	writer				1375
personal	web content writer		1370
personal	contributing writer		1365
personal	freelance writer		1360
personal	publisher			1355
personal	associate publisher		1350
personal	blogger				1345
generic	        [email protected]				840
generic	        [email protected]			835
generic	        [email protected]			830
generic	        [email protected]				825
personal	community manager		700
personal	communications director		695
personal	director of communications	690
personal	director communications		685
personal	communications coordinator	680
personal	community relations		675
personal	communications and marketing	670
personal	head of communications		665
personal	owner				660
personal	webmaster			655
generic	        [email protected]				400
generic	        [email protected]			395
generic	        [email protected]			390
generic	        [email protected]				385
generic	        [email protected]				380
generic	        [email protected]				375
generic	        [email protected]				370
generic	        [email protected]			365
generic	        [email protected]			360
generic	        [email protected]			355
generic	        [email protected]			350
generic	        [email protected]			345
personal	director			300
personal	assistant director		100
personal	associate director		95

Once these scores have been entered into the spreadsheet, the following multi-variable sorting is applied:

  • Root Domain – A to Z
  • Total Score – Highest to lowest
  • Confidence score – Highest to lowest

This results in emails being chosen for domains with more than one email, in the following priority order:

  • Editors and writers with personal email addresses first (prioritised by who I think has most link editing power)
  • Editors and writers with generic email addresses second (prioritised by who I think has most link editing power)
  • [email protected]‘ email address.
  • Community managers / community relations types with personal email addresses.
  • ‘Owner’ or ‘webmaster’
  • Any remaining personal email address that does not appear in my table above (because they always score exactly 500.)
  • Generic email entries in my table that score between 200 and 500 (Because they are forced to that score below 500)
  • ‘Director’ at a score of 300 – because some vain website owners list themselves as ‘Director’, when they are a one man band with an affiliate site.
  • Any generic emails for a domain, sorted and picked by highest ‘Hunter confidence score’ first.
  • Assistant and associate directors, the 2 entries in my table above that score below 200 because their presence suggests there’s a true ‘Director’. Not really the sort of person we want to email, so are forced below ANY other email address and chosen as last resort.

I’m sure that’s clear as mud?

However, I devised that scoring system after looking at A LOT of data sent to me by other SEOs, and to ensure that editors, writers and the like get chosen over other job roles and those with ‘no known role’, followed by a slant throughout for personal emails, rather than generic.

This also ensures if there’s only one email for a domain, it gets chosen. If there’s more than one, they get prioritised and chosen by Hunter confidence score at the very least, but usually with way more thought having gone into it than that.

I’m Done!

That was hard work.

It’s 7PM on a Friday as I type this. I make that beer and BBQ time.

It’s been a pleasure!

Leave a Comment