VBA Script to extract and assemble email address

[jeffkahn1]

I have a 500 row Excel table that I need to figure out a script for.

Column 1: First Name
Column 2: Last Name
Column 3: Employer
Column 4: Email (I want this to autofill based on a VBA script)

Assuming a known given email syntax for "COMPANY" - firstname.lastname@company.com, and my columns are:

Column 1: Joe
Column 2: Smith
Column 3: Company

Does anyone have an example of a script that can autopopulate Column 4 based on a set of known email syntax?

Thanks in advance - Jeff

 

[Biz]

Welcome to Mr Excel Forum.

Try script below

Sub Test()
 
    With Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Formula = "=LOWER(A2&"".""&B2&""@""&C2&"".com"")"
        .Value = .Value
    End With
 
End Sub

Biz

 

[jeffkahn1]

Thanks for the reply, and I think I understand your formula.

That said, my text in Column 3 (employer) might read "GE Water & Sensors", and from experience I know that their emails syntax is firstname.lastname@ge-ws.com. With your example, the email string would be firstname.lastname@GE Water & Sensors.com.

Instead, I need the VBA code to check the text in Column 3 for known strings (i.e. "GE Water"), and if it finds a known string with a known complimentary email syntax (I imagine lots of "IF" statements), to autofill a proper email into Column 4.

Thanks - Jeff

 

[Biz]

Can you give all different company name combination and anything out of ordinary?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

For Example,

Air India Airlines
Hindustan Unilever
Nokia Ltd

Another alternative set-up a table with Company name and short name you will use in e-mail. Utilise vlookup to get short name for email address.

Biz

 

[jeffkahn1]

Biz,

I've pulled back from trying to get this to work for right now. I'm going to play around with the code over the holidays, will report back if I still can't get it to work... Thanks.

 

[Biz]

Biz,

I've pulled back from trying to get this to work for right now. I'm going to play around with the code over the holidays, will report back if I still can't get it to work... Thanks.

Kwel. I am going on vacation soon so I may be I can answer your question next year.

Biz