SOLVED

VBA to create new CSV file from the existing one with modified email address

Copper Contributor

Hello,

 

I need a VBA which will automatically create a new CSV file from the old one (attached) based on this criteria:

Update column email with domain @abc.com Email format: first letter from name and full surname, lowercase. Equal emails should contain location_id before the @.

 

Example:

Now:       8,6,Bart Charlow,Executive Director,,
               9,7,Bart Charlow,Executive Director,,

 

Need to be: image attached

4 Replies

@Guram_Bregadze 

Here is a macro:

Sub CreateCSV()
    Dim sFile As Variant
    Dim csv As Workbook
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim sMail As String
    sFile = Application.GetOpenFilename(FileFilter:="CSV files (*.csv), *.csv")
    If sFile = False Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set csv = Workbooks.Open(Filename:=sFile)
    Set wsh = csv.Worksheets(1)
    m = wsh.Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        sMail = wsh.Range("B" & r).Value & Left(wsh.Range("C" & r).Value, 1) & _
            Split(wsh.Range("C" & r).Value)(1) & "@abc.com"
        wsh.Range("E" & r).Value = LCase(sMail)
    Next r
    csv.SaveAs Filename:=Replace(sFile, ".csv", "_new.csv"), FileFormat:=xlCSV
    csv.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

@HansVogelaar 

Thank you, Hans!

The problem with this code is that it displays the department_id before the name. I need it to be shown before the @ symbol

best response confirmed by Guram_Bregadze (Copper Contributor)
Solution

@Guram_Bregadze 

Change

        sMail = wsh.Range("B" & r).Value & Left(wsh.Range("C" & r).Value, 1) & _
            Split(wsh.Range("C" & r).Value)(1) & "@abc.com"

to

        sMail = Left(wsh.Range("C" & r).Value, 1) & Split(wsh.Range("C" & r).Value)(1) & _
            wsh.Range("B" & r).Value & "@abc.com"
1 best response

Accepted Solutions
best response confirmed by Guram_Bregadze (Copper Contributor)
Solution

@Guram_Bregadze 

Change

        sMail = wsh.Range("B" & r).Value & Left(wsh.Range("C" & r).Value, 1) & _
            Split(wsh.Range("C" & r).Value)(1) & "@abc.com"

to

        sMail = Left(wsh.Range("C" & r).Value, 1) & Split(wsh.Range("C" & r).Value)(1) & _
            wsh.Range("B" & r).Value & "@abc.com"

View solution in original post