JSI Tip 9212. How can I create mailing labels from Active Directory?

Using DSQUERY and SHELLESC.EXE, I have scripted Mailing.bat to generate mailing labels from Active directory users.

Each label contains:

Display NamePost Office Box or Address 1Address 1 or Address 2City, State, Postal CodeCountry (if NOT US)

The syntax for using Mailing.bat is:

Mailing Filename.tab

Where Filename.tab is the name of the file that will contain the label information, in Tab Separated Value format, suitable for using with the Microsoft Word Mail Merge Wizard.

Mailing.bat contains:

@echo offif {%1}=={} @Echo Syntax: Mailing Filename.tab&goto :EOFsetlocalset file=%1set file="%file:"=%"set wrk=%TEMP%Mailing_%RANDOM%set wrk1="%wrk%.TM1"set wrk2="%wrk%.TM2"set wrk3="%wrk%.TM3"set wrk4="%wrk%.vbs"if exist %wrk1% del /q %wrk1%:: VB Script to replace with tabs@echo dim fso, iFile, oFile,rFile, wFile, iString, oString>%wrk4%@echo iFile=%wrk3%>>%wrk4%@echo oFile=%file%>>%wrk4%@echo set fso = CreateObject("Scripting.FileSystemObject")>>%wrk4%@echo set rfile = fso.OpenTextFile(iFile, 1, false)>>%wrk4%@echo set wFile = fso.CreateTextFile(oFile, 2)>>%wrk4%@echo Do until rFile.AtEndOfStream = True>>%wrk4%@echo. iString = rFile.ReadLine>>%wrk4%@echo. oString = Replace(iString, ", vbtab)>>%wrk4%@echo. wFile.writeLine oString>>%wrk4%@echo loop>>%wrk4%@echo rFile.close>>%wrk4%@echo wFile.close>>%wrk4%@echo "Name"Address 1"Address 2"City"State"Postal Code"Country">%wrk3%set qry1=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User))"set qry2=-attr sAMAccountName displayName postOfficeBox l st postalCode c streetAddress -L -limit 0set prev=NONEfor /f "Tokens=*" %%a in ('%qry1% %qry2%^|SHELLESC') do ( set line=%%a call :build)call :precall :sortcall :outputdel /q %wrk2%cscript //nologo %wrk4%del /q %wrk3%del /q %wrk4%type %file%endlocalgoto :EOF:buildif /i "%line:~0,15%" EQU "sAMAccountName:" goto preif /i "%line:~0,12%" EQU "displayName:" set dn=%line:~13%&goto :EOFif /i "%line:~0,14%" EQU "postOfficeBox:" set po=%line:~15%&goto :EOFif /i "%line:~0,2%" EQU "l:" set city=%line:~3%&goto :EOFif /i "%line:~0,3%" EQU "st:" set st=%line:~4%&goto :EOFif /i "%line:~0,11%" EQU "postalCode:" set pc=%line:~12%&goto :EOFif /i "%line:~0,2%" EQU "c:" set co=%line:~3%&goto :EOFif /i "%line:~0,14%" EQU "streetAddress:" set s1=%line:~15%&goto :EOFif "%s2%" EQU "" set s2=%line%&goto :EOFif "%s3%" EQU "" set s3=%line%goto :EOF:preif "%prev%" EQU "NONE" set prev=YES&goto :prexif "%city%" EQU "" goto prex@echo "%co%"%pc%"%st%"%dn%"%po%"%city%"%s1%"%s2%"%s3%">>%wrk1%:prexset dn=set po=set city=set st=set pc=set co=set s1=set s2=set s3=goto :EOF:sortsort %wrk1% /O %wrk2%del /q %wrk1%goto :EOF:outputfor /f "Tokens=1-9 Delims= %%a in ('type %wrk2%^|SHELLESC') do ( call :output1 %%a %%b %%c %%d %%e %%f %%g %%h %%i)goto :EOF:output1set co=%1set pc=%2set st=%3set dn=%4set po=%5set city=%6set s1=%7set s2=%8set s3=%9set a1=""set a2=""if %po% NEQ "" set a1=%po%if %s1% NEQ "" call :address %s1%if %s2% NEQ "" call :address %s2%if %s3% NEQ "" call :address %s3%@echo %dn%a1%a2%city%st%pc%co%>>%wrk3%goto :EOF:addressif %1 EQU "" goto :EOFif %a1% EQU "" set a1=%1&goto :EOFif %a2% EQU "" set a2=%1&goto :EOF