Wednesday, February 29, 2012

Adjusting our Word VBA Macros for Office 2010 (64 bit)


We've had it easy these last few years. You could just upgrade Microsoft Office and not have to worry about file compatibility or about your macros not working.  I was just beginning to relax.


Unfortunately Microsoft never relaxes and the latest "innovations" extend past the annoying ribbon interface (I've been using it for over a year and I still consider it annoying) to clobber both file formats and macros.

The File Format Problem
The file format problem is easy to fix. First of all, I've changed our defaults to save in Office 97-2003 format.   There's no sense in our company posting files for public consumption in a format that they can't read. In case you're wondering where to change this, click File, then Options and click on SAVE.


If your fleet is still using Office 2003, you can download a compatibility pack which will enable it to read the new office formats.

http://www.microsoft.com/download/en/details.aspx?id=3


64 Bit API Declarations
This next bit is a little frustrating.  We rely on INI files in our Macros as they're much simpler than the registry and we can easily get our Notes/Domino apps to write updates into the files. It's all good for automation.  Unfortunately the 32 bit API calls won't work any more.

To get around the problem, replace this;


Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lsString As String, ByVal lplFilename As String) As Long


with this


Declare PtrSafe Function WriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal lpszSection As String, ByVal lpszKeyName As String, ByVal lpszString As String) As Long

Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

The remainder of your INI file reading routine should run without changes.


Unavailable Objects and those Nasty String Function Errors
This one is a nasty one.  Sometimes you'll try to open a macro that contains an unusual object, such as a calendar control.  Word 2003 displays a dialog box which says;  "Could not load an object because it is not available on this machine".  This was helpful.  

Unfortunately, Word 2010 doesn't mention the offending object but instead assumes that all internal string functions, such as LCASE$, UCASE$, LEFT$, RIGHT$, MIDS$ and TRIM$ are external functions in the missing component.



It highlights the first string function and says "Can't find project or library" which promptly sets you off on a wild goose chase.

To fix this problem, 
  1. Stop your macro
  2. Click Tools, then References (if this is greyed out, you didn't stop the macro).
  3. Find the problem Reference in the list and deselect it 
  4. Click Ok.

You'll also have to replace your missing element with a similar control, such as a text box and adjust any code which refers to it.

After that though, you should have a working, if a little disabled, macro.  Now I just need to find a 64 bit calendar control.

Wednesday, February 22, 2012

How to Create an Auto-Response Mail Message in Lotus Notes 8.5.3+


Why would you do this?
Suppose that you have an externally accessible generic email address for your company; support@mycompany.com or info@mycompany.com.

You might expose this to the web and allow people to send messages to you.

Setting up an auto-response email will tell the senders that their message reached its destination and that it will be dealt with accordingly.  It's also good practice to include links to FAQs or other useful information.

Why 8.5.3
The techniques we'll be using here work in older versions of Notes but some of the options seem to have moved around in 8.5.3.  I figured it was a good time to show you where they've moved to.

The Procedure


Start Domino Designer and open the Mail file to be modified.  A really quick way to do this is to right-click on the application tab and choose "Open in Designer".


In the Left hand panel of designer, expand Code and then double-click Agents.  A new window should appear.

Click the action button marked New Agent (at the top of the screen).

Give the Agent a Name, Alias and Comment;

Name: AutoResponder
Alias: AutoResponder
Comment: Sends an automatic response to any inbound emails sent to this address

Change the type to Simple Actions
Leave the Application field as is and click Ok.


In the left panel, click Document Selection.
At the bottom of the screen, you'll see an Add Condition button.
Click it.
Choose In Folder and then Select the Inbox.
Click Add



In the left panel, click Action
At the bottom of the screen, you'll see an Add Action button.
Click it.
Choose Reply to Sender
Make sure that it says Send Reply to Sender Only.
Tick Reply only once per person
Type your message
Click Add



So far so good.  Now to get this to occur for all new mail.

This is where I got stuck.
It makes perfect sense to have these agent properties at the bottom of the page, like they are for lotusscript agents but for some reason, they're not there in 8.5.3. A big thanks to Tony G for finding the missing menu option.

Right mouse click anywhere in the right hand panel and choose Agent Properties.


In the trigger section, choose On Event and Select After New Mail has Arrived.
You can now save and close your agent.


Note: In our tests it took 5-10 minutes for the agent to actually start working so don't despair if it looks like it doesn't appear to be working initially.   To test, send emails from an outside account, like Gmail and wait for the reply message.