Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-11-14
17
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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
 
LVL 39

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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