Forum Discussion
VBA to create new CSV file from the existing one with modified email address
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
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"
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 SubThank 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
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"
