Batch Add Users and/or Aliases from CSV file

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-05-23 21:49

EDIT: VERSION 7 of this script containing mods and fixes is posted at the foot of this thread here viewtopic.php?p=197910#p197910.

This script adds Users and/or Aliases from a CSV file to your hMailServer installation.

This seems to be a frequent requirement so I have copied and updated an old hms 4 script from original
topic http://www.hmailserver.com/forum/viewto ... 13#p149913

The modifications include authorisation for hMailServer 5.x.x and also error trapping so that script will continue processing input file when duplicates are found. A popup summary of duplicates (already in HMS) and counts is produced. Otherwise it's same as original.

** N.B. 1 ** You need to set your password into the Authentication step on line 20 of following code
** N.B. 2 ** You need to set your CSV filename on line 22 of following code with correct path of where it is.

Code: Select all

Option Explicit
On Error resume next

Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim scrreport
Dim failed
Dim added
failed = 0
added = 0

Const ForReading = 1
 
Set obBaseApp = CreateObject("hMailServer.Application") 


Call obBaseApp.Authenticate("Administrator","password_here") '*** N.B. 1. set your administrator password in this line 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("AddAccounts.txt", ForReading) 'N.B. 2. Set your CSV path/filename in this line
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case strNewAlias(0)
       Case "User"
          AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3)
       Case "Alias"
          AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
    End Select
    If err.Number <> 0 Then    'error handling:
     scrreport = scrreport & "Failed add (probably duplicate) : " & strNewAlias(0) & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3) & VBNewLine
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If

    
    i = i + 1
Loop

scrreport = scrreport & " " & VBNewLine
scrreport = scrreport & "Added = " & added & VBNewLine
scrreport = scrreport & "Failed or Duplicates = " & failed & VBNewLine
wscript.echo scrreport

Sub AddAlias(strAlias,strEmailAddress,strDomain)
   Dim obDomain 
   Dim obAliases 
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add() 
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account

   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing   
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain)
   Dim obDomain 
   Dim obAccounts 
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add() 
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
   
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
  
End Sub

The CSV file needs to be in the following format:
EntryType,Field1,Field2,Field3
The EntryType can be one of two options, User or Alias. If the EntryType is User, then:

EntryType = User
Field1 = Uername
Field2 = Password
Field3 = DqmainName

If the EntryType is Alias, then:

EntryType = Alias
Field1 = AliasName
Field2 = ForwardingEmail
Field3 = DomainName

So, for example, if you had the following CSV file:
User,tjones,mypassword,jones.com
Alias,tommy,tjones@jones.com,jones.com
This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.

Good Luck !

User avatar
mattg
Moderator
Moderator
Posts: 18884
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Batch Add Users and/or Aliases from CSV file

Post by mattg » 2013-05-24 01:01

Thanks Percepts - good work
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Version 2 : Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-05-24 18:38

** Version 2 just has minor change to write duplicates to hmailserver_events.log **

This script adds Users and/or Aliases from a CSV file to your hMailServer installation.

This seems to be a frequent requirement so I have copied and updated an old hms 4 script from original
topic http://www.hmailserver.com/forum/viewto ... 13#p149913

The modifications include authorisation for hMailServer 5.x.x and also error trapping so that script will continue processing input file when duplicates are found. A popup summary of duplicates (already in HMS) and counts is produced. Otherwise it's same as original.

** N.B. 1 ** You need to set your password into the Authentication step on line 20 of following code
** N.B. 2 ** You need to set your CSV filename on line 22 of following code with correct path of where it is.

Code: Select all

Option Explicit


Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
failed = 0
added = 0

Const ForReading = 1
 
Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application") 

Call obBaseApp.Authenticate("Administrator","password_here") '*** N.B. 1. set your administrator password in this line 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("AddAccounts.txt", ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case strNewAlias(0)
       Case "User"
          AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3)
       Case "Alias"
          AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
    End Select
    If err.Number <> 0 Then    'error handling:
     Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If
Loop
On Error goto 0
Elog.Write("Added Sucessfully    = " & added)
Elog.Write("Failed or Duplicates = " & failed)
Wscript.Echo ("Added Sucessfully    = " & added & VbNewLine & "Failed or Duplicates = " & failed & VbNewLine & VbNewLine & "See hmailserver_events.log for list of duplicates(if any)")

Sub AddAlias(strAlias,strEmailAddress,strDomain)
   Dim obDomain 
   Dim obAliases 
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add() 
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account

   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing   
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain)
   Dim obDomain 
   Dim obAccounts 
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add() 
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
   
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
  
End Sub

The CSV file needs to be in the following format:
EntryType,Field1,Field2,Field3
The EntryType can be one of two options, User or Alias. If the EntryType is User, then:

EntryType = User
Field1 = Uername
Field2 = Password
Field3 = DqmainName

If the EntryType is Alias, then:

EntryType = Alias
Field1 = AliasName
Field2 = ForwardingEmail
Field3 = DomainName

So, for example, if you had the following CSV file:
User,tjones,mypassword,jones.com
Alias,tommy,tjones@jones.com,jones.com
This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.

Good Luck !

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-06-28 19:46

thanks for script, I can add 285 account in one click, great work. one questions, after I add all 285 account how can I verify account in administration panel? I can not see any account created there. but I get message "successful 285 fail 0 "

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-06-28 21:22

restart hmail service or you can just wait for the cache to be reloaded. Unfotunately the script doesn't do this for you.

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-07-01 17:59

is there any script for add domain also, because adding user does not add domain also. I need to resolve that issue. so I can add all this users.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Version 3 : Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-01 19:54

** Version 3 auto adds domains if they don't exist **

N.B. hmailServer defaults for domain are used when a domain is added. If you don't want defaults you must edit domain options in Admin panel after running script or modify script to set options you require on add.

This script adds Users and/or Aliases from a CSV file to your hMailServer installation. If the domain for an account doesn't exist, then it is added before adding the account which uses it.

This seems to be a frequent requirement so I have copied and updated an old hms 4 script from original
topic http://www.hmailserver.com/forum/viewto ... 13#p149913

The modifications include authorisation for hMailServer 5.x.x and also error trapping so that script will continue processing input file when duplicates are found. Domains are added if they don't exist. A popup summary of duplicates (already in HMS) and counts is produced.

** N.B. 1 ** You need to set your password into the Authentication step on approx line 20 of following code
** N.B. 2 ** You need to set your CSV filename on approx line 22 of following code with correct path of where it is.

Code: Select all

Option Explicit
Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
Dim domainsAdded
failed = 0
added = 0
domainsAdded = 0

Const ForReading = 1
 
Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application") 

Call obBaseApp.Authenticate("Administrator","mypassword") '*** N.B. 1. set your administrator password in this line 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("AddAccounts.txt", ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case strNewAlias(0)
       Case "User"
          AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3)
       Case "Alias"
          AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
    End Select
    If err.Number <> 0 Then    'error handling:
     Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) _
     & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If
Loop
On Error goto 0
Elog.Write("Domains Added Sucessfully   = " & domainsAdded)
Elog.Write("Accounts Added Sucessfully   = " & added)
Elog.Write("Failed or Duplicate Accounts  = " & failed)
Wscript.Echo ("Domains Added Sucessfully    = " & domainsAdded & VbNewLine _
                  & "Accounts Added Sucessfully   = " & added & VbNewLine _
                  & "Failed or Duplicate Accounts  = " & failed & VbNewLine & VbNewLine _
                  & "See hmailserver_events.log for list of duplicates (if any)")

Sub AddAlias(strAlias,strEmailAddress,strDomain)
   Dim obDomain 
   Dim obAliases 
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add() 
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account
   
   'obBaseApp.Aliases.Refresh()
  
   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing   
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain)
   
   AddDomain strDomain
   
   Dim obDomain 
   Dim obAccounts 
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add() 
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
  
   'obBaseApp.Accounts.Refresh()
   
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
  
End Sub

Sub AddDomain(strDomain)
 Dim obNewDomain
 Set obNewDomain = obBaseApp.Domains.Add()
 obNewDomain.Name = strDomain
 obNewDomain.Active = True

 On Error resume next
 obNewDomain.Save()
 If err.Number = 0 Then
  domainsAdded = domainsAdded + 1
 End If 
 err.Clear
 
 obBaseApp.Domains.Refresh()
End Sub

The CSV file needs to be in the following format:
EntryType,Field1,Field2,Field3
The EntryType can be one of two options, User or Alias. If the EntryType is User, then:

EntryType = User
Field1 = Uername
Field2 = Password
Field3 = DqmainName

If the EntryType is Alias, then:

EntryType = Alias
Field1 = AliasName
Field2 = ForwardingEmail
Field3 = DomainName

So, for example, if you had the following CSV file:
User,tjones,mypassword,jones.com
Alias,tommy,tjones@jones.com,jones.com
This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.

Good Luck !

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-07-01 20:19

perfect, thanks, question? do I need to use txt file or I can use csv file?
according to line : "Set objTextFile = objFSO.OpenTextFile("AddAccounts.txt", ForReading) 'N.B. 2. Set your CSV path/filename in this line" I need txt file, do I need to format it in any order to add domain?

thank, again, sorry, I am asking stupide questions, but I know null about scripting.
sorry, one more time, if I am asking in wrong post.

wow, it works, I didn't receive any acknowledgment message but, I can see all accounts. I use csv file.
you guys are rock.

can I add field 4 for account full name and field 5 for email forward address
Last edited by mukeshb on 2013-07-01 20:45, edited 1 time in total.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-01 20:39

The format of the input file hasn't changed.

you can put rows in the file in any sequence you like.

The filename extension can be txt or csv or anything else. It just needs to be a plain text (ascii) file.
However, the fields must be comma separated and not some other character.
And the file isn't treated like a csv file with text qualifiers. If you put text qualifiers such as " then they will go into the database as part of account name which may cause failure so don't use text qualifiers in csv file.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-01 20:43

mukeshb wrote: wow it works, I didn't receive any acknowledgment message but, I can see all accounts. I use csv file.
you guys are rock.
you should have seen a popup with domain and account add counts.

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-07-01 20:49

sorry, I spoke too soon, I did receive pop up,

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-07-01 21:03

to add mail forward for some account can I add filed 4 for it, or I need to use alias section of this script? if I need to use alias section from this script do I need to change any thing in my csv file?

mukeshb
New user
New user
Posts: 11
Joined: 2013-06-25 21:41

Re: Batch Add Users and/or Aliases from CSV file

Post by mukeshb » 2013-07-01 21:13

can we make EntryType,Field1,Field2,Field3, Field 4, field 5,

Field 1= username, filed 2= password, field 3= domain name, field 4 = user full name, field 5 = email forwarding address,
I know I am asking too much, but it makes lot easier to migrate from another server.

Thanks.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-01 21:32

yes you can add more fields(columns) to the input file if you like. You should also look at http://www.hmailserver.com/documentatio ... ct_account which gives you all the property names you can add in the adduser sub of the script.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-01 22:32

note that in hmail persons name is held as two properties, personfirstname and personlastname and not fullname as you are suggesting. I would suggest splitting before input to this script.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Version 3 : Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-07-16 14:03

** Version 4 - mod to create domain for an alias being added to missing domain**

This seems to be a frequent requirement so I have copied and updated an old hms 4 script from original
topic http://www.hmailserver.com/forum/viewto ... 13#p149913

This script adds Users and/or Aliases from a CSV file to your hMailServer installation. If the domain for an account/alias doesn't exist, then it is added before adding the account which uses it.

N.B. when a domain is added the hmailserver defaults are used for the domain. If you require anything other than the dafaults you must either edit the domain settings after this script has run or modify the script to use your own defaults.

The modifications include authorisation for hMailServer 5.x.x and also error trapping so that script will continue processing input file when duplicates are found. Domains are added if they don't exist. A popup summary of duplicates (already in HMS) and counts is produced. Any duplicates found are written to hmailserver_events.log with the summary count.

** N.B. 1 ** You need to set your password into the Authentication step on approx line 20 of following code
** N.B. 2 ** You need to set your CSV filename on approx line 22 of following code with correct path of where it is.

Code: Select all

Option Explicit
Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
Dim domainsAdded
failed = 0
added = 0
domainsAdded = 0

Const ForReading = 1
 
Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application") 

Call obBaseApp.Authenticate("Administrator","password") '*** N.B. 1. set your administrator password in this line 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("AddAccounts.txt", ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case strNewAlias(0)
       Case "User"
          AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3)
       Case "Alias"
          AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
    End Select
    If err.Number <> 0 Then    'error handling:
     Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) _
     & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If
Loop
On Error goto 0
Elog.Write("Domains Added Sucessfully    = " & domainsAdded)
Elog.Write("Accounts Added Sucessfully   = " & added)
Elog.Write("Failed or Duplicate Accounts = " & failed)
Wscript.Echo ("Domains Added Sucessfully    = " & domainsAdded & VbNewLine _
            & "Accounts Added Sucessfully   = " & added & VbNewLine _
            & "Failed or Duplicate Accounts  = " & failed & VbNewLine & VbNewLine _
            & "See hmailserver_events.log for list of duplicates (if any)")

Sub AddAlias(strAlias,strEmailAddress,strDomain)

   AddDomain strDomain

   Dim obDomain 
   Dim obAliases 
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add() 
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account
   
   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing   
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain)
   
   AddDomain strDomain
   
   Dim obDomain 
   Dim obAccounts 
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add() 
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
  
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
  
End Sub

Sub AddDomain(strDomain)
   Dim obNewDomain
   Set obNewDomain = obBaseApp.Domains.Add()
   obNewDomain.Name = strDomain
   obNewDomain.Active = True

   On Error resume next
   obNewDomain.Save()
   If err.Number = 0 Then
    domainsAdded = domainsAdded + 1
   End If 
   err.Clear
 
   obBaseApp.Domains.Refresh()
End Sub

The CSV file needs to be in the following format:
EntryType,Field1,Field2,Field3
The EntryType can be one of two options, User or Alias. If the EntryType is User, then:

EntryType = User
Field1 = Uername
Field2 = Password
Field3 = DqmainName

If the EntryType is Alias, then:

EntryType = Alias
Field1 = AliasName
Field2 = ForwardingEmail
Field3 = DomainName

So, for example, if you had the following CSV file:
User,tjones,mypassword,jones.com
Alias,tommy,tjones@jones.com,jones.com
This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.

Good Luck !

iuliandr
New user
New user
Posts: 1
Joined: 2013-08-27 17:26

Re: Batch Add Users and/or Aliases from CSV file

Post by iuliandr » 2013-08-27 17:32

Hello and thank you for this script. Is there a possibility to also link users to Active Directory from within the script or the txt file? For example I have a single domain.local and all the added users would have the same login as the AD log. Could you please (if possible) guide me in the right direction?

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2013-08-27 18:10

Assumption is you know how to program in VBScript and understand the Com API and the DB structure. If you don't then too bad.

See http://www.hmailserver.com/documentatio ... ct_account

You need to add three values in the adduser section of script and obviously modify input file structure and handling of its values in script. Should be obvious to anyone with a modicum of programming knowledge.

IsAD
ADDomain
ADUsername

benipelednet
New user
New user
Posts: 1
Joined: 2014-10-22 10:46

Re: Batch Add Users and/or Aliases from CSV file

Post by benipelednet » 2014-10-22 11:28

Good script - Thank you.
i wont to add "first name" & "last name" to hmailserver.
i was edit the script (below) and i get error "Failed or duplicate accounts"
Does anyone have an idea?
Thank you.



#####the script#####
Option Explicit
Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
Dim domainsAdded
failed = 0
added = 0
domainsAdded = 0

Const ForReading = 1

Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application")

Call obBaseApp.Authenticate("Administrator","MKnet123") '*** N.B. 1. set your administrator password in this line
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("user.csv", ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
strNewAlias = split(objTextFile.Readline, ",")

Select Case strNewAlias(0)
Case "User"
AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3)
Case "Alias"
AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
End Select
If err.Number <> 0 Then 'error handling:
Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) _
& "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
failed = failed + 1
err.Clear
Else
added = added + 1
End If
Loop
On Error goto 0
Elog.Write("Domains Added Sucessfully = " & domainsAdded)
Elog.Write("Accounts Added Sucessfully = " & added)
Elog.Write("Failed or Duplicate Accounts = " & failed)
Wscript.Echo ("Domains Added Sucessfully = " & domainsAdded & VbNewLine _
& "Accounts Added Sucessfully = " & added & VbNewLine _
& "Failed or Duplicate Accounts = " & failed & VbNewLine & VbNewLine _
& "See hmailserver_events.log for list of duplicates (if any)")

Sub AddAlias(strAlias,strEmailAddress,strDomain)

AddDomain strDomain

Dim obDomain
Dim obAliases
Dim obNewAlias

Set obDomain = obBaseApp.Domains.ItemByName(strDomain)
Set obAliases = obDomain.Aliases
Set obNewAlias = obAliases.Add()

obNewAlias.Name = strAlias & "@" & strDomain 'username
obNewAlias.Value = strEmailAddress 'password
obNewAlias.Active = 1 'activates user
obNewAlias.Save() 'saves account

Set obNewAlias = Nothing
Set obAliases = Nothing
Set obDomain = Nothing

End Sub

Sub AddUser(strUsername, strPassword, strDomain, strPersonFirstName, strPersonLastName)

AddDomain strDomain

Dim obDomain
Dim obAccounts
Dim obNewAccount

Set obDomain = obBaseApp.Domains.ItemByName(strDomain)
Set obAccounts = obDomain.Accounts
Set obNewAccount = obAccounts.Add()

obNewAccount.Address = strUsername & "@" & strDomain 'username
obNewAccount.Password = strPassword 'password
obNewAccount.Active = 1 'activates user
obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
obNewAccount.Save() 'saves account
obNewAccount.PersonFirstName = PersonFirstName 'First name
obNewAccount.PersonLastName = PersonLastName 'Last name

Set obNewAccount = Nothing
Set obDomain = Nothing
Set obAccounts = Nothing

End Sub

Sub AddDomain(strDomain)
Dim obNewDomain
Set obNewDomain = obBaseApp.Domains.Add()
obNewDomain.Name = strDomain
obNewDomain.Active = True

On Error resume next
obNewDomain.Save()
If err.Number = 0 Then
domainsAdded = domainsAdded + 1
End If
err.Clear

obBaseApp.Domains.Refresh()
End Sub

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Batch Add Users and/or Aliases from CSV file

Post by percepts » 2014-10-22 15:02

two things I can see from a basic look at your code are that you have not included your new variables in the call to adduser and you have placed the move of inputs to user after the user account save so they will never be added.

You must do your own debugging of your own code.

jr3151006
Normal user
Normal user
Posts: 31
Joined: 2011-02-03 14:57

Re: Batch Add Users and/or Aliases from CSV file

Post by jr3151006 » 2014-12-09 22:32

If you allows me, here is the script v6 with the option to specify FirstName/LastName

Code: Select all

'#####the script#####
'# The EntryType can be one of two options, User or Alias. If the EntryType is User, then:
'#
'# EntryType = User
'# Field1 = Uername
'# Field2 = Password
'# Field3 = DqmainName
'#
'# If the EntryType is Alias, then:
'# EntryType = Alias
'# Field1 = AliasName
'# Field2 = ForwardingEmail
'# Field3 = DomainName
'#
'# To add USERS, for example, your CSV file should have the structured information (type, strUsername, strPassword, strDomain, strPersonFirstName, strPersonLastName), like below:
'# User,tjones,mypassword,jones.com,Tommy,Jones
'#
'# To add ALIAS, for example, your CSV file should have the structured information (type, strAlias, strAliasUsername, strDomain), like below:
'# Alias,tommy,tjones@jones.com,jones.com
'#
'# This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.
'####################

Option Explicit
Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
Dim domainsAdded

failed = 0
added = 0
domainsAdded = 0

'#####################################################################
'# Custom Variables regarding HMAIL admin password and file to be read
'#####################################################################
Dim hAdminpwd
Dim CSVFile
hAdminpwd = "#hMail@2015"
CSVFile = "bulk.csv"

Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application") 

Call obBaseApp.Authenticate("Administrator",hAdminpwd) '*** N.B. 1. set your administrator password in this line 

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(CSVFile, ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case strNewAlias(0)
       Case "User"
          AddUser strNewAlias(1), strNewAlias(2), strNewAlias(3),strNewAlias(4),strNewAlias(5)
       Case "Alias"
          AddAlias strNewAlias(1), strNewAlias(2), strNewAlias(3)
    End Select
    If err.Number <> 0 Then    'error handling:
     Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) _
     & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If
Loop
On Error goto 0
Elog.Write("Domains Added Sucessfully    = " & domainsAdded)
Elog.Write("Accounts Added Sucessfully   = " & added)
Elog.Write("Failed or Duplicate Accounts = " & failed)
Wscript.Echo ("Domains Added Sucessfully    = " & domainsAdded & VbNewLine _
            & "Accounts Added Sucessfully   = " & added & VbNewLine _
            & "Failed or Duplicate Accounts  = " & failed & VbNewLine & VbNewLine _
            & "See hmailserver_events.log for list of duplicates (if any)")

'###################
'# Usefull functions
'###################
Sub AddAlias(strAlias,strEmailAddress,strDomain)

   AddDomain strDomain

   Dim obDomain 
   Dim obAliases 
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add() 
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account
   
   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain, strPersonFirstName, strPersonLastName)
   
   AddDomain strDomain
   
   Dim obDomain 
   Dim obAccounts 
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain) 
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add() 
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.PersonFirstName = strPersonFirstName
   obNewAccount.PersonLastName = strPersonLastName
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
  
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
  
End Sub

Sub AddDomain(strDomain)
   Dim obNewDomain
   Set obNewDomain = obBaseApp.Domains.Add()
   obNewDomain.Name = strDomain
   obNewDomain.Active = True

   On Error resume next
   obNewDomain.Save()
   If err.Number = 0 Then
    domainsAdded = domainsAdded + 1
   End If 
   err.Clear
 
   obBaseApp.Domains.Refresh()
End Sub

User avatar
jimimaseye
Moderator
Moderator
Posts: 7420
Joined: 2011-09-08 17:48

Re: Batch Add Users and/or Aliases from CSV file

Post by jimimaseye » 2017-08-10 12:25

Version 7.

Includes the fix for adding first name and last name. Also has modifications to make the 1st field ("alias or "user") case insensitive and ignores any leading and trailing spaces against each input field

Code: Select all

'#####the script#####
'# The EntryType can be one of two options, User or Alias. If the EntryType is User, then:
'#
'# EntryType = User
'# Field1 = Uername
'# Field2 = Password
'# Field3 = DqmainName
'#
'# If the EntryType is Alias, then:
'# EntryType = Alias
'# Field1 = AliasName
'# Field2 = ForwardingEmail
'# Field3 = DomainName
'#
'# To add USERS, for example, your CSV file should have the structured information (type, strUsername, strPassword, strDomain, strPersonFirstName, strPersonLastName), like below:
'# User,tjones,mypassword,jones.com,Tommy,Jones
'#
'# To add ALIAS, for example, your CSV file should have the structured information (type, strAlias, strAliasUsername, strDomain), like below:
'# Alias,tommy,tjones@jones.com,jones.com
'#
'# This would create a user names tjones@jones.com in the jones.com domain and an alias tommy@jones.com which will forward all e-mail to tjones@jones.com in the jones.com domain.
'####################

Option Explicit
Dim Elog
Dim obBaseApp
Dim objFSO
Dim objTextFile
Dim strNewAlias,i

Dim failed
Dim added
Dim domainsAdded

failed = 0
added = 0
domainsAdded = 0

'#####################################################################
'# Custom Variables regarding HMAIL admin password and file to be read
'#####################################################################
Dim hAdminpwd
Dim CSVFile
hAdminpwd = "#hMail@2015"
CSVFile = "bulk.csv"

Set Elog = CreateObject("hMailServer.EventLog")
Set obBaseApp = CreateObject("hMailServer.Application")

Call obBaseApp.Authenticate("Administrator",hAdminpwd) '*** N.B. 1. set your administrator password in this line

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(CSVFile, ForReading) 'N.B. 2. Set your CSV path/filename in this line
On Error resume next
Do While objTextFile.AtEndOfStream <> True
    strNewAlias = split(objTextFile.Readline, ",")

    Select Case lcase(trim(strNewAlias(0)))
       Case "user"
          AddUser trim(strNewAlias(1)), trim(strNewAlias(2)), trim(strNewAlias(3)),trim(strNewAlias(4)),trim(strNewAlias(5))
       Case "alias"
          AddAlias trim(strNewAlias(1)), trim(strNewAlias(2)), trim(strNewAlias(3))
    End Select
    If err.Number <> 0 Then    'error handling:
     Elog.Write("Failed add (probably duplicate)" & Chr(34) & vbTab & Chr(34) & strNewAlias(0) _
     & "," & strNewAlias(1) & "," & strNewAlias(2) & "," & strNewAlias(3))
     failed = failed + 1
     err.Clear
    Else
     added = added + 1
    End If
Loop
On Error goto 0
Elog.Write("Domains Added Sucessfully    = " & domainsAdded)
Elog.Write("Accounts Added Sucessfully   = " & added)
Elog.Write("Failed or Duplicate Accounts = " & failed)
Wscript.Echo ("Domains Added Sucessfully    = " & domainsAdded & VbNewLine _
            & "Accounts Added Sucessfully   = " & added & VbNewLine _
            & "Failed or Duplicate Accounts  = " & failed & VbNewLine & VbNewLine _
            & "See hmailserver_events.log for list of duplicates (if any)")

'###################
'# Usefull functions
'###################
Sub AddAlias(strAlias,strEmailAddress,strDomain)

   AddDomain strDomain

   Dim obDomain
   Dim obAliases
   Dim obNewAlias

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain)
   Set obAliases = obDomain.Aliases
   Set obNewAlias = obAliases.Add()
   
   obNewAlias.Name = strAlias & "@" & strDomain 'username
   obNewAlias.Value = strEmailAddress 'password
   obNewAlias.Active = 1 'activates user
   obNewAlias.Save() 'saves account
   
   Set obNewAlias = Nothing
   Set obAliases = Nothing
   Set obDomain = Nothing
   
End Sub

Sub AddUser(strUsername, strPassword, strDomain, strPersonFirstName, strPersonLastName)
   
   AddDomain strDomain
   
   Dim obDomain
   Dim obAccounts
   Dim obNewAccount

   Set obDomain = obBaseApp.Domains.ItemByName(strDomain)
   Set obAccounts = obDomain.Accounts
   Set obNewAccount = obAccounts.Add()
   
   obNewAccount.Address = strUsername & "@" & strDomain 'username
   obNewAccount.Password = strPassword 'password
   obNewAccount.PersonFirstName = strPersonFirstName
   obNewAccount.PersonLastName = strPersonLastName
   obNewAccount.Active = 1 'activates user
   obNewAccount.Maxsize = 0 'sets mailbox size, 0=unlimited
   obNewAccount.Save() 'saves account
 
   Set obNewAccount = Nothing
   Set obDomain = Nothing   
   Set obAccounts = Nothing
 
End Sub

Sub AddDomain(strDomain)
   Dim obNewDomain
   Set obNewDomain = obBaseApp.Domains.Add()
   obNewDomain.Name = strDomain
   obNewDomain.Active = True

   On Error resume next
   obNewDomain.Save()
   If err.Number = 0 Then
    domainsAdded = domainsAdded + 1
   End If
   err.Clear
 
   obBaseApp.Domains.Refresh()
End Sub
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

iorx
New user
New user
Posts: 7
Joined: 2015-08-11 02:39

Re: Batch Add Users and/or Aliases from CSV file

Post by iorx » 2017-11-20 02:51

You just became the hero of the day for me! :D
I had to setup a temporary e-mailserver for about 40 users. This script made my day (night more of). Thanks a bunch!
A quick structured CSV and all boxes was up and running, including First and Last name. Sad that not IMAP client can pick this up from the server though.
Made some minor modifications: Separator from "," to ";" and maxsize for the mailboxes to 8192MB.

I'll buy you a beverage of choice if you pass through Stockholm, Sweden anytime. Or, you got a paypal or similar I can contribute to?

User avatar
mattg
Moderator
Moderator
Posts: 18884
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Batch Add Users and/or Aliases from CSV file

Post by mattg » 2017-11-20 03:08

iorx wrote:Sad that not IMAP client can pick this up from the server though.
viewtopic.php?f=21&t=31549

Is that what you mean?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

iorx
New user
New user
Posts: 7
Joined: 2015-08-11 02:39

Re: Batch Add Users and/or Aliases from CSV file

Post by iorx » 2017-11-20 04:21

uhu? Nice, had no idea! My google-fu is getting weak i presume.
Primary its iPhönes, mail and Android, Gmail, built in mail clients who's going to access the boxes. But I'll definitely do a little research finding this out.
Brgs,

ilpanos
New user
New user
Posts: 3
Joined: 2017-12-12 14:52

Re: Batch Add Users and/or Aliases from CSV file

Post by ilpanos » 2018-01-12 13:56

Is there a way to also add external accounts?

I am asking this because I see at the documentation of com_object_account that FetchAccounts is listed as read-only.

User avatar
jimimaseye
Moderator
Moderator
Posts: 7420
Joined: 2011-09-08 17:48

Re: Batch Add Users and/or Aliases from CSV file

Post by jimimaseye » 2018-01-12 15:13

The collection has an ADD option https://www.hmailserver.com/documentati ... chaccounts

And fetchaccount Save() option to write any new/amended entries.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Post Reply