Automatically generate unique email addresses

Task -

·	Create usernames (common name, UPN and SAM account name) from first letter in first name, initials and first two letters in last name

·	Compare usernames with existing common names on main03.d29.acme.com and add number if name already exists (this can take a while if there are many accounts)

·	Create email addresses of format FirstName.Initial.LastName

·	Compare email addresses with existing addresses on main03.d29.acme.com and add number if address already exists (this can take a while if there are many accounts)

·	Combine first name, initials & last name to full name

·	Fix incorrect character case in names

·	Create accounts in domain d29.acme.com and mailboxes on Exchange server \\main03

Script -

[Settings_Begin]
BatchSettings.Delimiter=TAB

SET FirstName=%col1%
SET Initials=%col2%
SET LastName=%col3%
SET FullName=%col1% %col2% %col3%   // create full name from col 1+2+3
SET AccountName=%col4%    // %AccountName% instead of %col4% below = easier to change column
SET Password=%col5%
SET SMTPEmail=%col6%
BatchSettings.MarkerCol=7

SET EmailSuffix=acme.com

SET ADDomainPath=dc=com/dc=acme/dc=d29
SET ADOU=MailOU
SET ADOUPath=%ADDomainPath%/ou=%ADOU%
[Settings_End]

[Machines_Begin]
\\MAIN03      NT PDC    
[Machines_End]

[Init_Batch_Begin]
// Create column 4 from column 1, 2 & 3   
Data.Loop   
 Data.Write 4,lowercase(Copy(%col1%,1,1)%col2%Copy(%col3%,1,2))   
Data.Endloop   
// Compare with existing common names, add number if same   
Data.Column.DupeCheck 4,CN,main03.d29.acme.com/dc=com/dc=acme/dc=d29,AddNumber   
// Create column 6 from column 1, 2 & 3   
Data.Loop   
 IF %col2% <> THEN   
  Data.Write 6,lowercase(%col1%.%col2%.%col3%)   
 ELSE   
  Data.Write 6,lowercase(%col1%.%col3%)   
 ENDIF   
Data.EndLoop   
// Compare with existing mail addresses, add number if same   
Data.Column.DupeCheck 6,Mail,main03.d29.acme.com/dc=com/dc=acme/dc=d29,AddNumber   
[Init_Batch_End]

[Batch_Begin]
Data.Loop   
 AD.Account.Create %ADOUPath%,%AccountName%,%AccountName%,%Password%   
 AD.Account.SetProperty %ADOUPath%/cn=%AccountName%,FirstName,ProperCase(%FirstName%)   
 AD.Account.SetProperty %ADOUPath%/cn=%AccountName%,Initials,UpperCase(%Initials%)   
 AD.Account.SetProperty %ADOUPath%/cn=%AccountName%,LastName,ProperCase(%LastName%)   
 AD.Account.SetProperty %ADOUPath%/cn=%AccountName%,FullName,ProperCaseName(%FullName%)   
 AD.Account.SetProperty %ADOUPath%/cn=%AccountName%,AccountDisabled,No   
 Exchange.2k.Mailbox.Create %servername2%, MyOrg, First Administrative Group, First Storage Group, Mailbox Store (main03), %servername2%, %ADDomainPath%, %ADOUPath%/cn=%AccountName%   
 Exchange.2k.Mailbox.SetProperty %ADOUPath%/cn=%AccountName%,SMTPEmail,%SMTPEmail%@%EmailSuffix%   
 // Etc   
Data.Endloop   
[Batch_End]

[Data_Begin]
anNa      Johnson      pass1
ben      adler      pass2
roger   r   sMith      pass3
cynthiA   L   barnes      pass4
[Data_End]

Result from the [Data] row "cynthiA L barnes" -