Autofill email address via VBA

[ashani]

Hi,

I wonder if someone could please guide me.

I'm looking for a VBA syntax to autofill email address. For example :

Cell A : Dan
Cell B : Smith

I would like in cell C to appear with Dan.Smith@test.com

The domain is the same name, just need to have first name dot last name and @test.com. However sometime there's space in the last name but I don't want any space in the email address.

Please can someone help me.

Many thanks

 

[portews]

Do you Specifically need VBA? This formula might be faster. In C1 (assuming A&B are on row 1) put this and drag down:

=CONCAT(TRIM(A1),".",TRIM(B1),"@test.com")

 

[portews]

The line in VBA would be:

Cells(1,3) = Trim(Cells(1,1)) & "." & Trim(Cells(1,2)) & "@test.com"

But you'd have to put that in a sub and trigger it.

 

[Peter_SSs]

sometime there's space in the last name but I don't want any space in the email address

Try

=SUBSTITUTE(A1&"."&B1&"@test.com"," ","")

Or with vba

Sub FillEmail()
  With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=SUBSTITUTE(A1&"".""&B1&""@test.com"","" "","""")"
    .Value = .Value
  End With
End Sub