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 Name
Post Office Box or Address 1
Address 1 or Address 2
City, State, Postal Code
Country (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 off
if \{%1\}==\{\} @Echo Syntax: Mailing Filename.tab&goto :EOF
set file=%1
set 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 0
set prev=NONE
for /f "Tokens=*" %%a in ('%qry1% %qry2%^|SHELLESC') do (
 set line=%%a
 call :build
call :pre
call :sort
call :output
del /q %wrk2%
cscript //nologo %wrk4%
del /q %wrk3%
del /q %wrk4%
type %file%
goto :EOF
if /i "%line:~0,15%" EQU "sAMAccountName:" goto pre
if /i "%line:~0,12%" EQU "displayName:" set dn=%line:~13%&goto :EOF
if /i "%line:~0,14%" EQU "postOfficeBox:" set po=%line:~15%&goto :EOF
if /i "%line:~0,2%" EQU "l:" set city=%line:~3%&goto :EOF
if /i "%line:~0,3%" EQU "st:" set st=%line:~4%&goto :EOF
if /i "%line:~0,11%" EQU "postalCode:" set pc=%line:~12%&goto :EOF
if /i "%line:~0,2%" EQU "c:" set co=%line:~3%&goto :EOF
if /i "%line:~0,14%" EQU "streetAddress:" set s1=%line:~15%&goto :EOF
if "%s2%" EQU "" set s2=%line%&goto :EOF
if "%s3%" EQU "" set s3=%line%
goto :EOF
if "%prev%" EQU "NONE" set prev=YES&goto :prex
if "%city%" EQU "" goto prex
@echo "%co%"»"%pc%"»"%st%"»"%dn%"»"%po%"»"%city%"»"%s1%"»"%s2%"»"%s3%">>%wrk1%
set dn=
set po=
set city=
set st=
set pc=
set co=
set s1=
set s2=
set s3=
goto :EOF
sort %wrk1% /O %wrk2%
del /q %wrk1%
goto :EOF
for /f "Tokens=1-9 Delims=»" %%a in ('type %wrk2%^|SHELLESC') do (
 call :output1 %%a %%b %%c %%d %%e %%f %%g %%h %%i
goto :EOF
set co=%1
set pc=%2
set st=%3
set dn=%4
set po=%5
set city=%6
set s1=%7
set s2=%8
set s3=%9
set 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
if %1 EQU "" goto :EOF
if %a1% EQU "" set a1=%1&goto :EOF
if %a2% EQU "" set a2=%1&goto :EOF

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.