Create array of e-mail address from array of names

[horizonflame]

Hi everyone

I have a list of names such as "John Smith" in A1:A20. I need to get these names into an array of people's e-mail addresses . The format output in the array would be "John.Smith@company.com".

Many thanks for your help guys.

 

[Michael M]

CAn you post a small sample of the data and what you expect as a result ?

 

[horizonflame]

Hi @Michael M

Thank you, please see an example of input array and my desired outcome:

Input Array("John Smith", "Ben Smith", "Chris Smith", "Mark Smith", "Paul Smith")

Output Array("John.Smith@company.com", "Ben.Smith@company.com", "Chris.Smith@company.com", "Mark.Smith@company.com", "Paul.Smith@company.com")

 

[Michael M]

Where are the @company names stored ??
IS this what you are trrying to

Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    Cells(r, 1) = Cells(r, 1) & "@company.com"
Next r
End Sub

 

[horizonflame]

Hi @MichaelM

The string "@company.com" is the same for all recipients. There is a decimal point between the first and second name which I couldn't see in your code.

I would like to create an array of the e-mail addresses as I will pass this array to my code to send the emails.

Thanks again

 

[Michael M]

Sub MM1()
Dim myArray As Variant, r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    Cells(r, 1) = Replace(Cells(r, 1), " ", ".")
    Cells(r, 1) = Cells(r, 1) & "@company.com"
Next r
myArray = [A2:A20]
End Sub

 

[Peter_SSs]

Something like this?
(I have assumed no blank cells in the column A range)

Sub Email_Addr_Array()
  Dim OutAry As Variant
  Const dom As String = "@company.com"
  OutAry = Split(Replace(Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value), dom & "|") & dom, " ", "."), "|")
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[horizonflame]

Many thanks @Peter_SSs and @Michael M for both solutions which worked ?

 

[Peter_SSs]

You're welcome. Glad we could help.

.. and thanks for updating your profile.

 

[TotallyConfused]

Hello HorizonFlame

When I ran Michael's code (post #6) I found that it did not change the person's name that was stored in cell A1. Also, it destroyed the original names, which may or may not be okay.

In view of this, I'd like to offer a slightly modified version. This code loads all the names into an array first, which should speed up the processing slightly.

Sub ReformatNames()
    Dim TotalRows As Long
    Dim RowNum As Long
    Dim Ary As Variant

    RowNum = 1
    TotalRows = Rows(Rows.Count).End(xlUp).Row
    Ary = Range(Cells(1, 1), Cells(TotalRows, 1)).Value

For RowNum = 1 To TotalRows
    Ary(RowNum, 1) = Replace(Cells(RowNum, 1), " ", ".")
    Ary(RowNum, 1) = Ary(RowNum, 1) & "@company.com"
Next RowNum
End Sub

TotallyConfused