Solved

Getting a "Variable Not Defined" Error when attempting to compile MS Accesss mdb file

Posted on 2016-11-14
17
36 Views
Last Modified: 2016-11-21
Warning - this may be more complicated than the subject implies.

I used Access 2016 to update an Access 2000 application.

While using Access 2016 I went into the Referernces section and checked the box for Microsoft Outlook 16 Object Library.  Next I created a routine that uses

Set oEmail = oApp.CreateItem(olMailItem)
oEmail.BodyFormat = olFormatHTML

This code runs perfectly from Access 2016 and Win 10 Pro without ever converting to code for a later version of Access, however, when I tried to compile the code using Access 2000 I get the subject error message.

As you would expect, when I view the References the word MISSING: appear next to the line for the Microsoft Outlook 16 Object library.

I have tried to check the Outlook 9 Object Library and receive the same error.

I have also tried copy MSOUTL.OLB to the Access 2000 machine and used BROWSE to select the file but nothing seemed to happen and I get the same error message.

My goal is to run the new code on the existing Access 2000 workstations.  Any ideas?

Thanks
0
Comment
Question by:pcalabria
  • 7
  • 6
  • 4
17 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41886949
you need t o use Late Binding, what you are using now is Early Binding
with Late Binding, you don't need to add the outlook reference, instead you have to substitute the constants values

like this

Dim oApp As Object
Dim oEmail As Object

Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(0)

With oEmail
    .BodyFormat = 2
End With
1
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886955
0
 

Author Comment

by:pcalabria
ID: 41887012
Rey, I've changed the code but now get this message:

Object doesn't support this property or method (Error 438)

When strFormat="HTML the offending line of my code is:

If strFormat = "HTML" Then oEmail.BodyFormat = 2
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41887079
where are the rest of your codes?
0
 

Author Comment

by:pcalabria
ID: 41887448
Rey, Here is my routine.

After unsuccessfully trying to get the code working, I modified if from the code you supplied to its present form.
The modification was based upon another article I read, and includes the line: Const olMailItem As Long =

I hope this makes sense!
Module1.txt
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41887910
I see that you have declared this twice

Dim oEmail As Object


do you have these two lines on the top of your code window

Option Compare Database
Option Explicit

if not add them then do a Debug >Compile and correct any error/s raised
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888291
ALWAYS develop in the lowest version of Access you need to run with.  That is the only way to keep from using expressions that are not supported in earlier versions.  And then there is the references issue which you also have to get past.
0
 

Author Comment

by:pcalabria
ID: 41888440
Rey,

Yes, I have the two lines:

Option Compare Database
Option Explicit

At the very top of the code.  Also, oEmail is only defined once.  I suspect the line that caught your attention was the REM'd line of original code.

When I compile I do not get any errors.  It is only when I actually try to send an email that the error is displayed.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
ID: 41888446
Are you testing using A2K?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41888463
try this
do  a compact and repair
do  a decompile  

create a shortcut with the following
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "d:\My Documents\access\mayapp.mdb" /decompile

then do a Debug > Compile

see how it goes
0
 

Author Comment

by:pcalabria
ID: 41888894
Pat-- Yes, A2K.  We have been using A2K for about 15 years and are now starting to develop in A2016.  The code I'm writing now is to replace code in the A2K module that is not compatible with Win 7 and later.  The reason I'm writing this code now is that we need to speed up our routines which scrub the database, and want to do this on a really fast machine.

Rey--Still no luck.  Compact - Decompile -Compact
Is it possible this code just doesn't run on A2K?  I'll try to test the code on another machine.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41889099
The code I'm writing now is to replace code in the A2K module that is not compatible with Win 7 and later.
Why would you expect this to run in A2K?

Does the code compile using A2K?
0
 

Author Comment

by:pcalabria
ID: 41889344
Pat - "Why would you expect this to run in A2K?"

Here is a recap:
My problem boils down to the email routine that I created using A2K on XP machines does not work when I load the A2K code with  machines running Win7 or later, thereby preventing us from upgrading to faster machines.   I loaded the A2K MDB with a machine using Win10 and A2016 and then modified the code by replacing the email routine with code that uses Outlook and late binding, and now I'm trying to get the new code to work on the machines that are still running A2K on XP.  

When the new code (saved as A2K format from the Win10 A2016 machine) is loaded using A2K on the older (XP) machines the code causes the error: Object doesn't support this property or method (Error 438)

I have decompiled and compact/repair'd the code however the error continues.

MY CODE:
Dim oApp As Object
Dim oEmail As Object

Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(0)

With oEmail
    .BodyFormat = 2
End With

The line causing the error is .BodyFormat=2
Error: Object doesn't support this property of method; Error 438
Remember, Outlook 2K is on this machine.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41889798
hmm.. it looks like the BodyFormat property is not available in O2000

in the machine with A2K, O2K
create a blank db
open the vba code and add to your references Microsoft Outlook 2000 object library
hit F2
type in the second box  htmlbody and click on the Find button see if there is result

do the same thing with olFormatHTML


or type this codes in A2K, see if you will get the same selection in the intellisense drop box

x
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41889821
If you want to continue running the code using A2K, you should develop using A2K.  It is simply too old.  Code you write using A2016 may not necessarily work when running under A2k.  That is the point.  Just because the database was created by A2k doesn't mean that A2016 won't let you write code that doesn't work in earlier versions.  There is some compatibility checking but it is not complete and that is why you should ALWAYS develop in the earliest version of Access that you need to run under.

You should convert the databases to .accdb format or at least AXP.  Microsoft eliminated support for A97 so newer versions of Access cannot even read those older databases.  A2K cannot be far behind.  Then distribute the A2016 runtime so you don't have to buy new versions of Access for all users.  Only developers need full retail versions.

This conversion will require some changes.  For example, if you are using a calendar ActiveX control, you will need to remove all references to it.  A2007 and newer versions include a calendar automatically for all controls defined as date/time.  You will probably also have to remove all the other ActiveX controls as well.  I believe A2K was the abnormal version of Access where ADO was the default.  This will also cause issues if your code is sloppy and doesn't disambiguate DAO and ADO objects.
0
 

Author Comment

by:pcalabria
ID: 41895469
Once again, thanks Rey and Pat for your help.

I was able to create and sent HTML email using late binding in A2K as suggested by Rey, however, I removed all reference to .bodyformat.  As shown by the test above that Rey asked me to performed, .bodyformat does not existing in A2K.  

To send HTML emails in A2K I used only the following line:

oEmail.HTMLBody = "My HTML text and hyperlinks"

The HTML emails send were displayed properly using the mail programs I tried (AOL and Outlook 2016) although when Outlook 2K was used, the HTML looked fine buy hyperlinks did not work.

Thanks again.

I'm going to award the points to Rey because his solution resulting in my solving the problem, but Pat, thank you very much for your comments.

Calabria
0
 

Author Closing Comment

by:pcalabria
ID: 41895474
This solution works on Access 2K if .bodyformat is not used.

Instead, I used oEmail.HTMLBody = "My HTML code"

Also note that the same code, when run from A2016, also seems to work well without using .bodyformat

Thanks!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now