Shrapnel

I've begun to migrate with some of my clients to the 3 year old Excel.  I'm well past the whole Ribbon controversy - yet I still get a sense that the new ship is still a bit leaky.  Office has thrown a few curve balls at me, so here are some cheap and effective tidbits of advice I can pass along:

Don't... 

...create pivot tables in Excel 2007 and then save the file as a 2003 compatible .xls - it all goes to hell.


...try to draw a flow chart in a Word 2007 document using AutoShapes.  Visio is better if you have it.


...run untested Excel VBA code that references the MS Word object model without setting up error handler's first.


...insert a PDF object into a document that may end up being printed to PDF.


...dispair.  No matter how low the economy goes, businesses will always have spreadsheets.


Cheap and Effective

I've been working on a B2B application - a variation on the standard "request for quote" problem.  In this case, a request is assembled from data on the spreadsheet, dropped into an email, and sent to a slew of individuals somewhere who may not even have Excel (poor sods.) They hit reply, fill in some sections of the copied message, and send.  Another macro connects to Outlook, scans a particular in-box, and pulls the details from any messages found as they come back.

This is, like my advice above, cheap and effective.  It's a way to do email forms with no workgroup software or exchange programming.  It doesn't involve sending or sharing files, and is not limited to the corporate LAN.  Data can even be collected electronically from people who aren't sitting at their desk, working, like I am always.

Hey, isn't this what websites are for?  Um, yeah, but this approach bypasses the need for website programming - no offence to web programmers (since I used to be one,) but power macro artistes deal in turn-around times of hours, not weeks, and the data needs to get back to Excel in the end anyway, right?  
 
Excel talks quite nicely to outlook.  Here's some code that sends an email message - it's really dead easy as long as you set up the appropriate references.  Perhaps the next post can explore the response capture side of things.

Public Function SendAMessage(strSubject As String, _
         strBody As String, _
         strAddress As String, _
         Optional strAttachment As String = "") _
                As Boolean

 'strAttachment, if used, will be the full 
 'file path of a file
 'requires a reference to the Microsoft 
 'Outlook 12.0 Object Library
 'see Tools Menu - References
  
  Dim email As Outlook.MailItem
  
  On Error GoTo Fail
  SendAMessage = False

  Set email = Outlook.CreateItem(olMailItem)
     
 'make sure we've been passed sufficient info
  If Len(strAddress) = 0 Or Len(strSubject) = 0 _
  Or Len(strBody) = 0 Then
  
    'trigger an error
     Err.Raise 999, , "Missing Information"
     
  Else

    'assemble the message
     With email
        .Subject = strSubject
        .To = strAddress
        .Body = strBody
        
        'attachment if required
         If Len(strAttachment) > 0 Then
           .Attachments.Add strAttachment
         End If
        
        .Send
        
     End With
     
    'it worked
     SendAMessage = True
  
  End If

Cleanup:
  On Error Resume Next
  Set email = Nothing
  Exit Function

Fail:
 'it didn't work
  gGlobalError = Err.Description
  Debug.Print "SendAMessage Failed. " &_
               Err.Description
  Resume Cleanup
  
End Function