Andy Lacey
andy at minstersystems.co.uk
Thu Feb 5 13:17:39 CST 2015
Thanks Lambert, that's really useful. As well as me converting to A2K the
company is moving from Lotus Notes to Outlook. I have Outlook code, having
converted the other way about 5 years ago, but this will be a big help going
from A97/Notes to A2K/Outlook.
Cheers
Andy
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: 05 February 2015 18:35
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Conversion A97 to A2k
The only problem I had switching Apps from Access 2003 to 2010 was to do
with emailing using Outlook. I have a bunch of canned routines to handle all
my mailing needs, which all reduce to a few calls to a function which
verifies the email addresses of the recipients, and then a final call to a
'SendMessage' routine.
Typically I create an Outlook object and then immediately create a Mail
item...
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Then for each email address I call a routine to check the address before
adding it to an Outlook.Recipient object.
When I converted to Office 2010 the verify email address code hit problems.
The code just creates some temporary Outlook objects and used the .Resolve
method to check if the email address looked good. This always just works
(from Access 97 onwards), but in A2010 if Outlook was NOT ALREADY RUNNING an
error resulted.
Here's the code, with the problem line indicated...
Function bVerifyEmailAddress(CM_Email As String) As Boolean
' Verify if email address is valid
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
On Error GoTo bVerifyEmailAddress_Error
If Not IsBlank(CM_Email) Then
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create a message object
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient to the message.
'++++++++++++++++++++++++++++++++++++++++++
Set objOutlookRecip = .Recipients.Add(CM_Email)
'+++++++ above line causes an error 287++++++++++
' Resolve Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve = True Then
objOutlookRecip.Delete
bVerifyEmailAddress = False
Else
bVerifyEmailAddress = True
End If
Next
End With
Set objOutlookRecip = Nothing
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Else
bVerifyEmailAddress = False
End If
bVerifyEmailAddress_Exit:
On Error GoTo 0
Exit Function
...
The solution was to trap error 287 and then launch Outlook. To do that I had
to write a little routine to go find Outlook...
bVerifyEmailAddress_Error:
Dim strOlPath As String
Select Case Err.Number
Case 0 ' No Error
DoEvents
Case 287 ' Application-defined or object-defined error
' In Access 2007/2010 the call to .Recipients.Add generates this error
if Outlook is not already running
' So here we will locate Outlook and then run it
strOlPath = FindOutlook()
If strOlPath > "" Then
Shell strOlPath, vbMinimizedNoFocus
Resume
Else
MsgBox "Error sending Email message. Please start Outlook then click
the 'OK' button and we will try again.", vbOKOnly Or vbExclamation
Resume
End If
...
And this is how I locate Outlook...
Function FindOutlook() As String
Dim strOlPath As String
Dim strFile As String
Dim strTemp As String
Dim n As Long
' try the folder that Access is installed in first
FindOutlook = "" ' default
strOlPath = SysCmd(acSysCmdAccessDir)
strFile = Dir(strOlPath & "Outlook.exe")
If strFile & "" > "" Then
FindOutlook = strOlPath & strFile
Else
' not found. Search the Program Files folder
strTemp = Split(strOlPath, "\")(0) & "\" & Split(strOlPath, "\")(1)
With Application.FileSearch
.LookIn = strTemp
.SearchSubFolders = True
.fileName = "Outlook.exe"
.Execute
If .FoundFiles.count > 0 Then
For n = 1 To .FoundFiles.count
If GetFileName(.FoundFiles(n)) = "Outlook.Exe" Then
' got it.
FindOutlook = .FoundFiles(n)
Exit For
End If
Next n
End If
End With
End If
End Function
' End Code
HTH
Lambert
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Thursday, February 05, 2015 11:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Conversion A97 to A2k
Changing the Subject to be more relevant.
Well actually, this may be misplaced confidence but having done the
conversion a couple of weeks ago and then driven myself crazy with ribbons,
commandbars etc this is the first crash I've had. I've run most of the
biggest routines and they seem to be standing up ok, largely untouched. I
have one heavy user who has had it for a couple of days and it was he who
had the crash that started this thread, but that's the only one he's had and
he's on the system all day every day. I don't doubt there'll be some
problems down the line but for now......(touches large piece of wood)
Can anyone think of any banana skins they've skidded on during such a
conversion?
Andy
> On 05 February 2015 at 16:38 Gustav Brock <gustav at cactus.dk> wrote:
>
>
> Hi Andy
>
> I see. So that's how it is.
> It will be a massive amount of work. Couldn't you team up with some?
> Sounds like a job that could kill anyone.
>
> Another option is to run it off a terminal server. You know about
RemoteApp?
> This smart feature will run the application in a frame on the client
> desktop appearing as any other desktop application. The normal user
> won't notice the difference.
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
> Sendt: 5. februar 2015 17:14
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Change in the evaluation of IIF, or something else?
>
> Yea :-(
>
> I wouldn't even be getting into this if it weren't that it's getting
> harder and harder to shoe-horn A97 onto new pc's.
>
>
> > On 05 February 2015 at 16:05 Gustav Brock <gustav at cactus.dk> wrote:
> >
> >
> > Hi Andy
> >
> > Uh. That will keep you busy for a while.
> >
> > /gustav
> >
> > -----Oprindelig meddelelse-----
> > Fra: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
> > Sendt: 5. februar 2015 17:00
> > Til: Access Developers discussion and problem solving
> > Emne: Re: [AccessD] Change in the evaluation of IIF, or something else?
> >
> > Hi Rocky
> >
> > A run of a little routine tells me:
> >
> > 692 Forms of which 492 have modules
> > 354 Reports of which 120 have modules
> > 79 actual modules
> >
> > 200,000 lines of code in total
> >
> > The potential for more banana skins is.......significant, shall we say?
> >
> > Andy
>