Sending an e-mail from Excel using the server mailfile

[petergms]

I have some Excel VBA code (see fragment below) which tests the sending of an e-mail via Lotus Notes. This works fine when using a local replica of the mailfile, but not with the server mailfile:

Set oNotesSession = CreateObject("Notes.NotesSession")
sMailFile = oNotesSession.GetEnvironmentString("MailFile", True)
sMailServer = oNotesSession.GetEnvironmentString("MailServer", True)
'sTest is simply a string which selects the type of test I need to run
sTest = "Server, subdir not hardcoded"

If sTest = "Local, named" Then
    'Open a local replica of the mailfile using the mailfile name
    '*** This works ***
    Set oNotesDatabase = oNotesSession.GetDataBase("", sMailFile, False)
ElseIf sTest = "Local, unnamed" Then
    'Open a local replica of the mailfile without using the mailfile name
    '*** This works ***
    Set oNotesDatabase = oNotesSession.GetDataBase("", "", False)
ElseIf sTest = "Server, subdir not hardcoded" Then
    'Open the server mailfile using the server name and the mailfile name
    '*** This fails ***
    Set oNotesDatabase = oNotesSession.GetDataBase(sMailServer, _
        sMailFile, False)
ElseIf sTest = "Server, subdir hardcoded" Then
    'Open the server mailfile using the server name and the mailfile name
    'with the subdirectory hardcoded
    '*** This works ***
    Set oNotesDatabase = oNotesSession.GetDataBase(sMailServer, _
        "mail3\" & sMailFile, False)
End If

If oNotesDatabase.IsOpen = False Then oNotesDatabase.OPENMAIL

The problem is that my server mailfile is in a subdirectory on the server (mail3\petergms.nsf) - but I don't know how to programmatically extract the full path to get "mail3". In my code below, sMailFile = oNotesSession.GetEnvironmentString("MailFile", True) yields "petergms.nsf". When (and only when) I assign sTest to "Server, subdir not hardcoded", Set oNotesDatabase = oNotesSession.GetDataBase(sMailServer, sMailFile, False) yields Nothing, and the subsequent oNotesDatabase.IsOpen condition fails.

Hardcoding the subdirectory is not an option, as different users have their mailfiles in different subdirectories, and it is particularly a problem where users don't have local replicas. Is there any way of programmatically extracting the full path, or of bypassing this problem? (Forcing users to create local replicas won't be acceptable.)

-Peter