Skip to content

Prevent Outlook Fax Addressing

Outlook has long had a bizarre insistence on including fax numbers in the list of possible addresses when resolving target email recipients.

For example, if you have a contact record for GIll Bates, with both an email and a fax address, you might do the following to send an email to Gill:

address1

…then press Ctrl-K (or Alt-K) to resolve addresses, and Outlook gives you:

address2

You’d be forgiven for thinking that perhaps you were back in 1998.  Microsoft’s suggestion is to just move the fax into a different field, for example “Other”.  By exporting the entire contact list, and reimporting it.  Good grief.

Another common workaround is to put “FAX:” on the front of the fax number because (supposedly) if the fax number starts with a letter then Outlook will ignore it.  In other words, you’d change the fax number from “+1(212)…” to “FAX: +1(212)…”.

This worked for a long time, but at some point that stopped working – apparently the “FAX:” is now a valid fax qualifier recognised by Outlook.   This post talks about removing the colon, i.e. changing the fax from “FAX: +1(212)…” to “FAX +1(212)…”, and that seems like the best compromise to me.

In my case, I had hundreds of contacts, some of them in public folders, some of them in my local contacts folder, and it seemed that a script was in order.  Such a script is below; first it prompts for a folder, then operates on that folder to replace the “FAX: ” prefix with “Fax”, and add it if it is missing.

    Public Sub FaxNumFix()

        Dim currContact As Outlook.ContactItem
        Dim item As Object
        Dim contacts As Outlook.Items
        Dim contactsFolder As Outlook.MAPIFolder
        Dim fax As String
        Dim i As Integer

        contactsFolder = Session.PickFolder()
        If contactsFolder Is Nothing Then Exit Sub
        contacts = contactsFolder.Items
        i = 0

        For Each item In contacts
            If TypeName(item) = "ContactItem" Then
                currContact = item
                fax = currContact.BusinessFaxNumber
                If Len(Trim(fax)) > 0 Then
                    fax = RemoveFront("FAX", fax)
                    fax = RemoveFront(":", fax)

                    fax = "Fax " + fax

                    Debug.Print "[" & Format(Round((i / contacts.Count) * 100), "00") & "%] Changing " & currContact.FileAs
                    currContact.BusinessFaxNumber = fax
                    currContact.Save()
                End If
                i = i + 1
            End If
        Next
        Debug.Print "Done."
    End Sub

    Public Function RemoveFront(aPattern As String, aBody As String) As String
        'if aBody starts with aPattern (non-case-sensitive) then aPattern is removed, and the new body is L-trimmed and returned
        Dim body As String
        Dim pattern As String
        body = LTrim(aBody)
        pattern = Trim(aPattern)
        If Left(UCase(body), Len(pattern)) = UCase(pattern) Then body = LTrim(Right(body, Len(body) - Len(pattern)))
        RemoveFront = body
    End Function

SSIS / BIDS Tip: Variable Window

If you’re like me and obsess over small details, there’s a good chance you’ve argued with yourself over the best piece of screen real estate to use to hold the ‘Variables’ window in BIDS.

It really needs a wide enough window to show the variable names, types and values – but as the list grows, it suits a vertical space better.  A wide vertical space, that is.

For me, the answer turns out to be making use of that second monitor (you know, where your Stack Overflow client web browser sits).  If you drag the variable window, you can position on a second monitor, and BIDS will quite happily remember the position and reopen it in the same place next time.  If you start Visual Studio with the second monitor unplugged, it  handles that gracefully too, and just gathers the variables window onto the main monitor.

I only tested this in Visual Studio 2008, but it should work in earlier versions.

Fixing the Foscam Port Forwarding Blues

I recently started experimenting with home automation (using a ).
One part of this was adding a Foscam FI8910W, which (in theory) I could use to monitor my home while I’m travelling, as well as using it to provide alarm triggers to the Vera itself.

The simplest part of the setup should have been the router port forwarding. I’m not going to spell out the procedure here, since there are a ton of sites doing that already. What I *did* find is that the router was for some reason blocking internet traffic from reaching the camera. This was obvious from an open port check using the tool, which reported ‘closed’ for the port I was trying to forward.

My router is a Linksys E3200, and in the camera’s web interface I had set the camera up with a static IP (192.168.2.94), outside my normal DHCP range (which starts at .105).

I checked all the obvious things:

  • I tried port forwarding using both “Single Port Forwarding” and “Port Range Forwarding”.
  • I tried putting the camera into the DMZ
  • I changed the port to a low port (less than 1024); I also tried port 80.
  • I tried moving the camera to an IP inside the DHCP range, i.e. 192.168.2.196

Nothing made any difference; pretty aggravating! I did a lot of searching, and there were a lot of people with other issues which seemed to mainly revolve around dynamic DNS setup, or loopback problems inside the LAN.

What finally solved it was removing the camera from the router’s “Clients Already Reserved” DHCP reservation table.

This is accessed from the “Basic Setup” tab:
router1

Then, at the bottom of the pop-up window, remove the entry for the camera:
router2

I had added the camera in here as an early step in the setup, to try to force it to get the correct IP address; after manually entering the IP address into the camera’s web interface I never bothered removing it from here. As soon as I removed it from this table, everything started working as it should.

Change Outlook’s Proofing Language on the Fly

I’m working for a company that does business in the U.S. and several other countries, and I usually attempt to ensure that my spelling is in line with who I’m communicating with. As a result, email communication often ends up looking like this:

For years I just did my best to grit my teeth and ignore it, but finally I got around to writing a macro that allows a single button-click to change the language of the email you’re working on:

Option Explicit

Private Const wdEnglishUS = 1033
Private Const wdEnglishUK = 2057

Public Sub SetLangEngUS()
	SetDocLang wdEnglishUS
End Sub

Public Sub SetLangEngUK()
	SetDocLang wdEnglishUK
End Sub

Sub SetDocLang(ALangID As Integer)
	Dim wordEditor As Object

	If Inspectors.Count = 0 Then Exit Sub
	If ActiveInspector.CurrentItem.Class <> olMail Then Exit Sub
	If Not ActiveInspector.IsWordMail Then Exit Sub
	If ActiveInspector.EditorType <> olEditorWord Then Exit Sub
	Set wordEditor = ActiveInspector.wordEditor

	On Error Resume Next
	ActiveInspector.wordEditor.Styles("Normal").LanguageID = ALangID
	ActiveInspector.wordEditor.SpellingChecked = False
End Sub

Then, just add two icons to the Quick Access Toolbar, each pointing to one of the SetLangEngUS and SetLangEngUK calls.

Counting Null Values in a Row

On StackOverflow today someone asked how to get the number of null values in a table’s row; i.e. if there are 10 columns, and 4 of the columns contain data, the answer would be 6.

The requirement was that it would work in sqlite, which I don’t have a generic solution for, but it did prompt me to play around with this in SQL Server.  The result is fairly tidy:

with xmltab(xmlcol) as
    (select cast(
        (select
            *
        from
            myTable
        where
            myIdCol = 1
        for xml path('MYTABLE'), elements xsinil
    ) as xml) as xmlcol
)
select cast(cast(xmlcol.query('count(//*[@xsi:nil="true"])') as varchar) as int) from xmltab

What is Visual Studio Really Saying?

Image

Using XSLT to Extract SSIS Variables From a Package

Over my last few SSIS projects I’ve tried to build a standard logging system into each, with one of the side effects being I’ve ended up with variables in each package that need to be documented in an external file.

Rather than go down the more extreme route of using the .NET API (which is appealing for the future, but overkill for what I need at the moment), it’s a lot easier to build a small XSL transform script to pull out the user variables into a CSV file.  The structure of the .dtsx package file is:

<?xmlversion="1.0"?>
<DTS:Executable>
    <!-- bunches of stuff -->
    <DTS:Variable>
        <DTS:Property DTS:Name="xxx"/>
        <DTS:VariableValue>123</DTS:VariableValue>
    </DTS:Variable>
</DTS:Executable>

Note that the ‘Property’ entries are repeated several times, with the ‘xxx’ containing various values for the various properties we’re interested in; the only thing that doesn’t follow that rule is the variable value, which has its own node.  Here’s one sample variable:

<DTS:Variable>
    <DTS:Property DTS:Name="Expression"/>
    <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
    <DTS:Property DTS:Name="Namespace">User</DTS:Property>
    <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
    <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
    <DTS:VariableValue DTS:DataType="3">30010</DTS:VariableValue>
    <DTS:Property DTS:Name="ObjectName">EventCustInsert</DTS:Property>
    <DTS:Property DTS:Name="DTSID">{3057CEC1-68C8-4651-AED6-E75558C9A15E}</DTS:Property>
    <DTS:Property DTS:Name="Description"/>
    <DTS:Property DTS:Name="CreationName"/>
</DTS:Variable>

All I needed was the name, description and value, so a small XSLT script (which I just run in Notepad++) pulls these out very easily:

<?xmlversion="1.0"encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:DTS="www.microsoft.com/SqlServer/Dts" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" encoding="utf-8" />
    <xsl:template match="/">
    <xsl:text>Name,Description,Value</xsl:text>
    <xsl:text>
</xsl:text>
    <xsl:for-each select="//DTS:Variable[DTS:Property[@DTS:Name='Namespace']='User']">
      <xsl:value-of select="DTS:Property[@DTS:Name='ObjectName']"/>
      <xsl:text>,</xsl:text>
      <xsl:value-of select="DTS:Property[@DTS:Name='Description']"/>
      <xsl:text>,</xsl:text>
      <xsl:value-of select="DTS:VariableValue"/>
      <xsl:text>
</xsl:text>
    </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Extending this to include other properties should be trivial – just add the field names to the fifth line, and extend the current <xsl:value-of> series to include other fields as needed.

The result is a nice CSV file that’s easy to load into, say, Excel or Word for documentation purposes.

SSIS 2005 Custom Task… From VS 2010

I just started a new project that needs to be run under a SQL Server 2005 environment, but I already had a custom logging task written for SSIS 2008 that I wanted to use. The component wouldn’t appear in the 2005 toolbox, however, despite all my best efforts.

To compound it, I didn’t have a VS 2008 dev environment available (I’ve changed laptops), and installing it didn’t seem to make sense, given that it seemed to be the wrong version anyway – especially when I had something else JUST as wrong already installed in the form of VS 2010.

So up upgraded the project and compiled the custom task in VS 2010, and tried everything to try to make it happy about being used in SSIS 2005′s toolbox:

  • I changed the target .NET framework version to 2.0
  • I changed the post-build script to store the resulting .DLL in the appropriate directory in both Program Files and Program Files (x86) for good measure (it already registered into the GAC):
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" -u $(TargetName)
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles%\Microsoft SQL Server\90\DTS\Tasks\"
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\Tasks\"
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\" 
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" -if "$(TargetDir)$(TargetName).DLL"
  • I made sure the assembly was signed

After a half day of screwing around with it (how much time do you really save by reusing components?),  the answer turned out to be a simple thing: the version of the main DTS referenced assembly needed to be changed.  It’s not obvious from the solution explorer:

But when you examine the properties, it is:

All that’s needed to solve it is to remove that assembly from the References, and re-add it from the correct place.  In my case this turned out to be:

C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

Finally, the assembly appeared in the toolbox.  For reference, this shows in the ‘SSIS Control Flow Items’ section of the ‘Choose Toolbox Items’ dialog.

Scaling Textile Image Links

This is something that has been driving me crazy; plenty of sites (including Basecamp) use Textile still, and I often want to link an image in without formally downloading it, resizing it and re-uploading it.

All the Textile examples I found all show how to display an image inline via a URL, but none of them show how to resize the image, at least not in a fashion I could make work.  Some digging finally turned this up, and I figured I’d post this here for when I forget next time, and in case it’s useful for someone else.

A plain image is linked in as follows:

!http://farm6.static.flickr.com/5306/5627310027_eb64230826_t.jpg!

Resulting in: 

To force a specific size:

!{width: 50px; height: 33px;}http://farm6.static.flickr.com/5306/5627310027_eb64230826_t.jpg!

Resulting in: 

Whoosh!

Feet wet with the Arduino: Debouncing

My lovely family got me for Xmas this year an Arduino Uno, and some associated bits like a breadboard.  I’ve always been interested in how things work, but intimidated by component-level electronics, so I figured this would be a good place to get over it and learn something at the same time.

I’d already done a bunch of reading, including the Getting Started guide (thanks, Douggie, Hannah!), so next came a quick trip to RadioShack, where I rifled through their parts bins looking for anything interesting to hook up.  Maybe not the cheapest solution, but it was a neat experience to be able to dig through and find stuff that might be fun to hook up, but not be sure what it’s useful for yet.  In a world where everything you buy has an exact purpose, and generally only one way of hooking it up, it was an eyeopener!

Button and two LEDs

Small beginnings

Among other things I ended up with a couple of plain old green LEDs, and a momentary switch (275-646).  I had to crimp the switch’s legs with a pair of pliers to get them to fit into my breadboard, since in my RadioShack fugue I wasn’t savvy enough to imagine this might be something I’d want to do.

I’ve done some programming, and am comfortable digging and researching until I understand something, so I was happy enough with the coding side of it – which in fact is very similar to c#, given its c / c++ heritage.  Something that fascinates me is that in coding, everything is very deterministic most of the time, but in hardware, there was a whole side I’d never guessed at; contacts can ‘bounce‘, or a potentiometer can flick back and forth between two values, or any number of other mysterious and distressing behaviours can manifest.  I decided to combine the LEDs and a button, and explore the whole thing to get a better handle on it.

Every Arduino LED tutorial starts off with a LED (or two), and indicates you needed a resistor in line with each LED.  A bit of digging turned up the underlying reason: the LEDs just don’t handle the current that results from a 5V source. That’s probably not exactly worded correctly, and of course there’s more to it than that, but the end result is that I needed 100 ohm resistors in series with each LED.  The resistance calculation was pretty straightforward using a handy online calculator; doing it manually is a bit more work, but definitely useful.  It took me a while to find a site that actually explains the calculation, which works out as follows:

The Arduino’s digital pins provide 5V, and my LEDs have a forward voltage (voltage level needed to make them light) of 2.1 volts.  So we need to reduce the 5V DOWN to 2.1V, i.e. reduce it by 2.9V – this is the amount the resistor has to drop the voltage by.  The LEDs have a forward current (how much current we want to drive through the LED) of 30 mA.

So for the resistor, since V=IR, i.e. 2.9 = (0.030 * R), then R = 2.9 ÷ 0.030 = 96.6667.  The next ‘size up’ for resistors is 100Ω, so we select that. Whew!

The only other thing that I found non-intuitive is that I needed a resistor between the switch and ground.  After some research to understand it, I found it helps to think of it this way: When the switch is open, the digital pin ‘sees’ the ground normally, through the resistor.  In this case, the resistor is redundant; there’s only one path, so the digital pin is ‘low’.  When the switch is closed, however, the pin can see two paths; one to ground, and one to the 5V source.  The fact that one path (the ground path) has a resistor causes the pin to have a ‘preferred path’ the other way, through to the 5V source – i.e. the resistor on the ‘ground path’ makes it a less ‘desirable’ path.  I’m sure that’s not technically accurate, but it makes it easier for me to remember!

So, the circuit looks like this in Fritzing:

 

The tidy version - thanks Fritzing!

The tidy version - thanks Fritzing!

And less attractive, but keepin’ it real:

The Board

All good!

The Arduino code to control all this is as follows:

const int led0Pin = 11;            // LED1 is connected to pin 11
const int led1Pin = 12;            // LED2 is connected to pin 12
const int btnPin = 2;              // Momentary button is connected to pin 2
const int minTime = 30;            // The minimum time the button must be pressed (in ms) before we consider it a valid press
int lastReadBtnState;              // Used to store the last state that we read from the button (this could flop all over the place)
int acceptedBtnState;              // Used to store the last ACCEPTED state that we read from the button (this should be very stable)
int ledLit = 0;                    // Which LED is lit; 0 = led0, 1 = led1
unsigned long lastChangeTime;      // Used to store the last time that we observed a change in the button's state
boolean inTransition = false;      // If this is true, we've observed a button change, and are waiting to see if it is stable

void setup()
{
Serial.begin(9600);                      // Initialise the serial output so that we can write out debug messages
pinMode(led0Pin, OUTPUT);                // Set the LED0 pin as output
pinMode(led1Pin, OUTPUT);                // Set the LED1 pin as output
pinMode(btnPin, INPUT);                  // Set the button pin as input
acceptedBtnState = digitalRead(btnPin);  // Initialise the stable button state to whatever state it actually is
lastReadBtnState = acceptedBtnState;     // Indicate that we're in a stable state
}

void loop()
{
lastReadBtnState = digitalRead(btnPin);
if (!inTransition &amp;&amp; (lastReadBtnState != acceptedBtnState))
// Bam, we registered a button state change
{
lastChangeTime = millis();  // Mark the time we observed the change
inTransition = true;        // Note that we're now in a transition state
Serial.println("Button state changed from " + StateToStr(acceptedBtnState) + " to " + StateToStr(lastReadBtnState) + ".  Going into transition");
}
else if (inTransition &amp;&amp; ((millis() - lastChangeTime) &gt; minTime))
// The button state hasn't changed for at least minTime milliseconds, so assume we're stable now
{
Serial.println("Met minimum time requirement: going from " + StateToStr(acceptedBtnState) + " to " + StateToStr(lastReadBtnState));
if (lastReadBtnState == LOW)
// Only toggle the LED if the button is in a LOW state
{
Serial.println("New state is LOW; toggling LED");
ledLit = 1 - ledLit;
}
// Move the last-read value into the accepted state variable
acceptedBtnState = lastReadBtnState;
// Note that we're no longer in transition
inTransition = false;
}

// Turn on the appropriate LED
if (ledLit == 0)
{
digitalWrite(led0Pin, HIGH);
digitalWrite(led1Pin, LOW);
}
else
{
digitalWrite(led0Pin, LOW);
digitalWrite(led1Pin, HIGH);
}
}

String StateToStr(int AState)
// Given a pin state variable, returns a text equivalent of its value
{
return (AState == HIGH ? "HIGH" : "LOW");
}


The LEDs alternate smoothly when the button is pressed, so mission accomplished.  Back to the sack of goodies to see what's next!

Follow

Get every new post delivered to your Inbox.