We help IT Professionals succeed at work.
Get Started

Script that generates email signature does not work properly in Outlook 2007.

lgropper
lgropper asked
on
434 Views
Last Modified: 2008-11-11
I have created the following script to pull data from active directory to create email signatures for a company. It works perfectly in Outlook 2003 but for some reason there are formatting discrepancies when the signature is used in Outlook 2007. The left margins do not line up and the two pictures show up complete squished. When you close down Outlook 2007 and reopen it the pictures become fixed but the left margin on a few lines remains misaligned. Here is the VB script followed by the xls file that I used. Thanks in advance for the help.

VB SCRIPT:

Const adPersistXML = 1
Const ForWriting = 2

Const REGSIGKEY = "HKCU\Software\Microsoft\Office\10.0\Common\MailSettings\NewSignature"

Dim FSO, sFile1, sFile2, sFile3, sFile4
Dim Fol, F, WshNet
Dim sDir
Dim XML,XSL
Dim WshShell, rsAD, Com, ConAD

sFile1 = "C:\Program Files\Microsoft Office\Office\Outlook.exe"
sFile2 = "C:\Program Files\Microsoft Office\Office11\Outlook.exe"
sFile3 = "C:\Program Files\Microsoft Office\Office12\Outlook.exe"
sFile4 = "C:\Program Files\Microsoft Office\Office10\Outlook.exe"

Set FSO = Createobject("Scripting.FileSystemobject")
if FSO.FileExists(sFile1) or FSO.FileExists(sFile2) or FSO.FileExists(sFile3) or FSO.FileExists(sFile4) then
      Set WshNet = WScript.CreateObject("WScript.Network")
      sUsername = WshNet.UserName

      sDir = "C:\Documents and Settings\" + sUsername + "\Application Data\Microsoft\Signatures"

      if FSO.FolderExists(sdir) then
            Set Fol = FSO.GetFolder("C:\Documents and Settings\" + sUsername + "\Application Data\Microsoft\Signatures")
            For Each F In Fol.Files
                  F.Delete
            Next
            
      else
            FSO.CreateFolder("C:\Documents and Settings\" + sUsername + "\Application Data\Microsoft\Signatures")
      end if
      
      'Wscript.Network knows the Netbios Domain, but we might not know our DNS domainname
      sPrefix = "LDAP://"
      Set cont = GetObject(sPrefix & "rootdse")
      sDN = cont.get("defaultnamingcontext")

      'Alright - we are now prep'd to do a little search to get our adspath...
      'We will open an ADO connection to AD
      Set conAD = WScript.CreateObject( "ADODB.Connection" )
      Set com = WScript.CreateObject( "ADODB.Command" )

      'set the provider
      conAD.Provider = "ADSDSOObject"
      ' Open a connection object
      conAD.Open "Active Directory Provider"
      Set Com.ActiveConnection = conAD
            
      'what do we want to find - well our details...
      sFilter = "(samaccountname=" & sUsername & ")"
      'what do we need to know?
      sAttributes = "givenname,sn,title,department,telephoneNumber"

      'build the command string
      Com.CommandText = "<" & sPrefix & sDN & ">;" & sFilter & ";" & sAttributes

      ' Set some preferences for search
      Com.Properties( "Page Size" ) = 512
      Com.Properties( "TimeOut" ) = 30 ' seconds

      'Execute the query to get our objects from Active Directory.
      Set rsAD = CreateObject("ADODB.Recordset")
      Set rsAD = Com.Execute

      If (Err.Number <> 0) Then
                WScript.Echo Err.Number, "on Execute"
      End If
      WScript.Echo "LDAP user objects:" & rsAD.RecordCount
      Set xml = CreateObject("Microsoft.XMLDOM")
      rsAD.Save xml, adPersistXML
      XML.async = False

      Set xsl = CreateObject("Microsoft.XMLDOM")
      XSL.async = False
      'Load the XSL file. We keep it in the same path as the script, but it could be stored anyway. e.g. Webserver
      XSL.load "P:\Signatures\signatureUBCB.xsl"

      XSL.preserveWhiteSpace = True
      'Lets Save the signature file to disk to the profile - we are aiming for the Outlook signature path
      Set WshShell = CreateObject("Wscript.Shell")
      sUserProfile = WshShell.ExpandEnvironmentStrings("%UserProfile%")
      sSignaturePath= sUserProfile & "\Application Data\Microsoft\Signatures\"

      sOutputfile = sSignaturePath & "AD_" & sUsername & ".htm"
      Set F = FSO.OpenTextFile(sOutputFile,ForWriting,True)
      F.Write xml.transformNode(xsl)

      On Error Resume Next
      sRegKey = "" & WshShell.RegRead(REGSIGKEY)
      If sRegkey ="" Then
               WshShell.RegWrite REGSIGKEY, "AD_" & sUsername, "REG_SZ"
      End If
      On Error GoTo 0

      Set F = Nothing
      Set Fol = nothing
      Set FSO = nothing
end if

XLS FILE:

<?xml version='1.0' encoding='utf-8'?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" exclude-result-prefixes="rs z">
<xsl:output
method = "html"
doctype-public = "-//W3C//DTD HTML 4.01 Transitional//EN"
encoding = "UTF-8"
indent = "yes"/>

<xsl:template match="/">
    <html><head></head>
        <body>
      <p style="font-size: 8pt; font-family: arial; color: Gray; margin: 0in; margin-bottom: 0in">
      --
      </p>
            <p style="margin-top: 0in; margin-left: 0.2in">
            <span style="font-size: 11pt; font-weight: bold; font-family: arial; color: Gray">
                    <xsl:value-of select="xml/rs:data/z:row/@givenname"/><![CDATA[ ]]>
                    <xsl:value-of select="xml/rs:data/z:row/@sn"/>
                </span>
                <span style="font-size: 9pt; font-family: arial; color: Gray">
                    <br/>
                    <xsl:if test="xml/rs:data/z:row/@title">
                        <xsl:value-of select="xml/rs:data/z:row/@title"/><![CDATA[ ]]>-<![CDATA[ ]]>
                  <xsl:value-of select="xml/rs:data/z:row/@department"/>
                        <br/>
                    </xsl:if>
                          <br/>
                  <table>
                  <tr>
                  <td><img src="Http://www.site1.ca/pic1.gif"></img></td>
                  <td><img src="Http://www.site2.ca/pic2.gif"></img></td>
                  </tr>
                  </table>
                  <br/>
                </span>
            <span style="font-size: 9pt; font-weight: bold; font-family: arial; color: Gray">
                  Company ABC<br/>
            </span>
            <span style="font-size: 9pt; font-family: arial; color: Gray">
                  123 Rd.,<br/>
                  Toronto, Canada<br/>
                  999 999<br/>
            </span>
            <br/>

                <span style="font-size: 9pt; font-family: arial; color: Gray">
                    <xsl:choose>
                        <xsl:when test="xml/rs:data/z:row/@telephoneNumber">
                            T<![CDATA[ ]]>:<![CDATA[ ]]><xsl:value-of select="xml/rs:data/z:row/@telephoneNumber"/><br/>
                        F<![CDATA[ ]]>:<![CDATA[ ]]>416.782.0031
                        <br/>
                        </xsl:when>
                        <xsl:otherwise>
                            T<![CDATA[ ]]>:<![CDATA[ ]]>416.999.9999<br/>
                       F<![CDATA[ ]]>:<![CDATA[ ]]>416.999.9999<br/>
                        </xsl:otherwise>
                    </xsl:choose>
                <xsl:text>   </xsl:text>
                   
                </span>
            <br/>
            <span style="font-size: 9pt; font-family: arial; color: Gray">
                  Site 1:<![CDATA[ ]]><a href="http://www.site1.com">www.site1.com</a><br/>
                  Site 2:<![CDATA[ ]]><a href="http://www.site2.ca">www.site2.ca</a><br/>
            </span>
            <br/>
            <span style="font-size: 9pt; font-family: arial; color: Gray">
                  ...............................................................................................................................<br/>
                  Confidentiality Notice<![CDATA[ ]]>:<br/>
                  This message may contain privileged or confidential information. If you<br/>
                  are not the intended recipient or received this communication by error,<br/>
                  please destroy this message without copying or disclosing its content.<br/>
                  ................................................................................................................................
            </span>

           </p>
        </body>
    </html>
</xsl:template>
</xsl:stylesheet>
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE