Sending Faxes from Excel, What Gives?

confused

Over at the Excel Unusual blog, there's a procedure for sending a fax from Excel.

It looks like it could be useful, and I see the link on Twitter every once in a while, but has anyone been able to actually use it? I won't reproduce the original code here, so I kindly refer you to the above blog post as I discuss the code.

Sub Procedures Don't Return Values

The first procedure is a Sub procedure, but it looks like the author intends for it to return a value, because the last line of the procedure is

SendAFax = True

But for once, Excel Help is right:

Like a Function procedure, a Sub procedure is a separate procedure that can take arguments, perform a series of statements, and change the value of its arguments. However, unlike a Function procedure, which returns a value, a Sub procedure can't be used in an expression.

In fact, I know the author expects the code to return a value because the main procedure, SendMails, checks the alleged return value of SendAFax by using it in an If statement. So I'm wondering if the code author actually tested this code, because there's no way it could have worked.

Note: In the example workbook provided, the procedure is declared as a Function. It also has some other corrections.

Undocumented Early Binding

The code uses some undocumented API to send faxes. By "undocumented" I mean that the blog post is nearly devoid of explanation of the API. I almost feel like I walked into the middle of a conversation and missed the part that would have given me the context I need to understand what is being said.

The code makes several early bound references without explaining what they are, why they are needed or how to reference each library. I would prefer to see late bound code, so I can just cut and paste it from the blog post and not have to manually reference libraries (which I could easily see myself forgetting to do).

If you need to "toggle" between late and early bound, use GetObject & CreateObject like this:

Early Bound

Dim fs As Scripting.FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")

Late Bound

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

When I use CreateObject, I only have to change Scripting.FileSystemObject (early bound) to Object (late bound) and I never have to touch the second line.

If I do it the way presented in the code:

Dim fs As Scripting.FileSystemObject
Set fs = New Scripting.FileSystemObject

I have to change both lines to switch the code between early and late bound.

Also note that, many times these lines won't be right next to each other. The Dim statement would be at the top of the procedure, while the instantiation could be dozens or hundreds of lines below. So it's in my best interest to only have to change one statement (the Dim statement, which would be at the top of the procedure with all the others).

I realize that these are just quirks of mine, but I believe they do have some basis in real world coding situations.

Missing Variable Declarations

The SendAFax sprinkles variable declaration throughout the code, which makes it harder to manage declarations. But the main variable, FaxServer isn't declared at all. In fact, there are seven variables but only three are declared. So, following our best practices, this code won't even compile.

What is SendMails doing?

The next procedure, SendMails is equally headscratching. My issues:

  • It's tightly coupled to a specifically formatted worksheet, requiring a lot of rewriting to make it work in almost any other situation.
  • There are references in the code to "offers" and "referral dates" and numbers of days passed before we can send a fax, with no explanation of what they mean or why they are there.

How many people are going to use a faxing procedure the way it is presented in the code? My guess is, not many. But the post title is catchy and should earn a lot of hits!

To me, this looks like an actual client workbook that has a few names changed, but is otherwise something actually used by someone. Otherwise, how can you explain what is supposed to be a sample workbook requiring such a specific workflow?

In fact, if you follow the author's link in the post, you can find the exact same spreadsheet used to create another example for another blog, a full eight months before the article on Excel Unusual.

If I want to do something simple like list some names and fax numbers and fire off some faxes, the code needs heavy editing to ignore the parts about offers and referral dates.

Wrapping it Up

The "Additional Configurations" section looks almost like an afterthought. There is a barebones explanation of the code and no link to the Microsoft Fax Service Extended COM Type Library so you can discover more properties or methods you can use.

In fact, there are more links to the author's personal social networking accounts than to anything useful that would help me understand the code better.

I posted a comment on the article asking for clarification, but the author (of either the post or the blog, my guess is both) is either ignoring me or is distancing himself from the code (my guess, again, is both). So I refer to these types of posts as code dumps and write about them here instead.

I suppose I should just be happy my comment made it on the site at all (I've left similar comments on other blogs that didn't get approved), but I still wonder why there are blogs with comments sections where the author proceeds to ignore someone who brings up a valid issue.

If anyone would like to offer any clarification on any of the above issues, I would be glad to hear it. But as it stands, it looks like "hit and run" code. Stay tuned because I will be writing more posts like this in the near future.

What I Would Do Differently

For starters, the code would be late bound.

All variables would be declared, with all of the Dim statements appearing together at the top of each procedure. It would have to compile without error using Option Explicit.

Instead of the way SendMails works now, I would write a simple procedure that reads a range into an array and calls SendAFax on each "row" in the array. (You'd still have to format your worksheet a certain way, but there's no way to avoid that.) We would either use Range("A1").CurrentRegion, or use a named range (which could be placed anywhere on the worksheet) to avoid the hardcoding issues found in the present code.

FWIW, I have my own code for sending faxes using VBA. It's limited to certain countries, however, and I failed to link to the source API page (I'll remedy that soon).

Did I miss anything here? Am I coming off as harsh?

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Sending Faxes from Excel, What Gives?:

  1. Hello JP,

    I read through your analysis of my article.

    And if you may, I would like to clarify some of my intentions:

    1. I did not mean to provide a comprehensive tutorial on Faxing with Excel. Only to show that it is possible and to provide a working sample.

    2. The sample is tightly coupled to a specific workbook, but as I stated in my earlier remark, it was not meant to be a full fledged tutorial.

    3. The missing variable declarations are inexcusable – I will be having a harsh conversion with myself about the matter.

    You asked … did I come off as too harsh?

    Well… You have to admit that the information in that article is useful and original (I did not copy it off anyone) and while not perfect, It can be of use. And I did put quite a bit of work into it, so classifying it as a 'hit in the dark' was a bit harsh.

    Other than that, your other remarks were pretty accurate. I would much rather get them in an email instead of in a public flogging sort of way, but they were accurate.

    But, this is your blog, and if you you can choose to criticize whomever you want in whatever way you want.That is a valid way to generate publicity.

    I just like my way better :-)

    By the way… Your blog is pretty cool. I've used your examples many times while working on projects.

    Yoav

    P.S.

    George – We must have our wires crossed. I haven't heard from you in quite a while and I assumed you were busy. I'll email you in a couple of minutes.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel