In some cases, you have a list of employees who are working in a company (with Internet address on example.com) and cannot find their email addresses on their company website. In other words, employees do not have a specific page for themselves or there is no staff page on example.com. In this situation, we face an important question: How we can convert our list of employees on a company into a valid email database?
In order to achieve this, you need to follow some simple steps.
1. Create a list of email addresses based on staff names
At the first step, copy/paste your first and last names into an excel file in two separate columns. Then, you need to create some email addresses from all the data you have. Let's assume the first row of your excel file is John Doe. The possible company email for such employee could be [email protected] OR [email protected] OR [email protected] We need to create 3 possible email addresses for each potential staff. To do so, follow the below steps.
1.1. Creating [email protected] Sample emails
These type of emails are made up full first name followed by a dot and followed by full last name. To create such a combination, follow these steps:
1.1.1. Start by selecting the cell in the column next to the cell containing the last name (C2):
1.1.2. The function we will call to help us with this is “CONCATENATE”. The formula we will use in this example is =CONCATENATE(A2,".",B2,"@example.com"). Be sure to include the apostrophes and commas - the formula won't work otherwise. The first result looks like this:
1.1.3. Now the easiest way to fill in the rest of the cells without having to type all that information in again is to use the Fill Handle (my personal favorite). The fill handle is at the bottom right corner of the selected cell:
You may have never noticed it before, but once you get the hang of it, I know you will find it to be very useful. To use it, hover your cursor over the fill handle until your cursor changes to a +. Then, click and drag it down as far as you want to copy the formula to other cells and then release the mouse. Whether it’s 5 cells, 500 cells or 5,000 cells the fill handle will make them all match. The result will be:
1.2. Creating [email protected] Sample emails
These type of emails are made up the first letter of the first name followed by a dot and followed by full last name. To create such a combination, follow these steps:
1.2.1. Start by selecting the cell D2. Since we want the last name (located in cell B2) in its unaltered entirety, we will just concatenate the last name to the result of the LEFT function’s result which give us first letter of the first name.
=LEFT(A2,1)&"."&B2&"@example.com"
1.2.2. Just like step 1.1.3, copy the formula for next rows in the current column. The result will be:
1.3. Creating [email protected] Sample emails
This emails are just like the output of the step 1.2. All you need is to follow previous section guidelines and use bellow formula to create email addresses:
=LEFT(A2,1)&B2&"@example.com"
The result will be:
2. Validating generated emails
After creating emails associated with names, you need to put all of them .txt files. Copy and paste each email column on a separate .txt file, So you do not mix up them. Login to your DeBounce account and upload the lists. Then start validating them. After validation completed, you need to analyze the results ans select proper email types. Since you are not sure about the validity of your emails at the first stage, it is recommended to only select Deliverable type and do not choose Accept-all and Unknown types. This is the best practice to reduce the number of bounces.
- Email Types to Select: Deliverable.
- Email Types NOT to Select: Accept-All, Unknown, Disposable, Spam-traps, Syntax.
If you have two or more employees with the same last name and the same first initial of the first name, this will not create unique email addresses for these individuals. It would be possible to create a much more complex formula to detect such occurrences and assign a unique number to each duplicate (i.e. jsmith1, jsmith2, etc…), but for this tutorial, a simpler approach would be to apply Conditional Formatting / Duplicate Values… on the column of email addresses to flag any email addresses that occur more than once. The good news is that DeBounce will remove duplicated email addresses by default. So, you do not need to be worry about that.
That's all. You have a full list of valid email addresses for the company Example. You can add validated emails to your original files and combine names and emails together, so you can send more personalized emails to the recipients.