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
- Who This Will Work For – Prerequisites and Required Tools
- Video Walkthrough
- Quick Reference Checklist for Use
- Caveat: Step 1 Changes for Over 1,000 Domains
- Why I Made This Tool
- Five Macros and Their Purpose
- You Don’t Have to Use All Steps – And Can Change Them to Suit Your Needs
- Download – And Set Up Before Use
- Selecting the BEST Emails
- Editing Which Emails and Job Positions Get Deleted
- Editing Best Email Selection Algo – Sorry, Not This Time!
- How My Sheet Chooses Best Email – So You Know What You’re Getting
- I’m Done!
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.
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.
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:
- Download the provided spreadsheet further on in this article.
- Make sure you follow instructions further down in this article to enter your Hunter API key into the macro.
- 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.)
- Click ‘Emails from Hunter’ menu in the downloaded spreadsheet, to have access the macros used in this tool.
- 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.
- 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.)
- 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.)
- 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.)
- 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’
- 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
First of all, download the macro enabled worksheet from here:
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’:
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:
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:
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], fin[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]
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.
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!