send xls file as attachment from access vba

[Guest]

I want to send an Excel (xls) file as an attachment to an email (Outlook)
from within Access, using vba code. It is not an Access object.
Thanks for any help.
Jim

 

[Guest]

Hi Jim,

Here are a couple of KB articles that should be helpful:

How to use a recordset to send Outlook e-mail to multiple recipients in
Microsoft Access
http://support.microsoft.com/?id=318881

How to use Automation to send a Microsoft Outlook message
http://support.microsoft.com/?id=209948

While both articles are written for Access 2000, I believe the same code
will work just fine in Outlook 2002 or 2003. You'll need to set a reference
to the correct version of the Outlook library [Microsoft Outlook {version}
Object Library] if you use these examples as written (early bound code).

Tom
______________________________________

:

I want to send an Excel (xls) file as an attachment to an email (Outlook)
from within Access, using vba code. It is not an Access object.
Thanks for any help.
Jim

 

[Guest]

I followed the sample in the KB article (209948), loaded the "Mirosoft
Outlook 10 Object Library (Office XP), and attempted to run the code (VBA).
In Northwind, I received the "Run-time error '48'; Error in loading DLL."

Running the same VBA code in my own mdb module, I redeived a "Compile error:
Sub or function not defined."

I do not know what to do now. I need some "hand-holding."

Jim

 

[Guest]

I followed the sample in the KB article (209948), loaded the "Mirosoft
Outlook 10 Object Library (Office XP), and attempted to run the code (VBA).
In Northwind, I received the "Run-time error '48'; Error in loading DLL."

I installed and ran the code described in Article ID : 161088 and also
received the
"Error loading DLL" message at the " Set objOutlook =
CreateObject("Outlook.Application")" also noted in the code sample below.

I do not know what to do now. I need some "hand-holding."

Jim



Private Sub cmd_SendMail_Click()
On Error GoTo Err_cmd_SendMail_Click

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.

 

[Guest]

Hi Jim,

I just tried the 209948 sample, using my copy of Northwind, and it works
fine on my PC. I suspect that you have some type of DLL problem on your PC,
since you reported the same Error 48 when attempting to use either procedure.
Here is a KB article that discusses many trappable errors, including Error 48:

An Explanation of Trappable Errors in Visual Basic for Apps
http://support.microsoft.com/?id=142138

I hope you didn't attempt to type in the code, as indicated in step 6 of
209948. You should use the copy and paste technique. Did you first save the
module and then attempt to compile the code before trying to run it (Debug >
Compile DatabaseName)? If you receive any compile errors, then these would
definately need to be corrected first.

Which version of MSOUTL.OLB do you have on your PC? My version is
10.0.2607.0. Have you tried running either procedure from another PC, taking
into account that you may need to change the reference to match the version
of Outlook on another machine? I suspect that you will find that the code
works okay on another PC.

I do have a couple of comments regarding the code in both samples. Neither
one of them includes an error handler. This really sucks in my opinion. Here
is an example of including an error handler:

Sub SendMessage(Optional AttachmentPath)
On Error GoTo ProcError

<the rest of the code goes here, except that I moved the two lines of code
that set objects equal to nothing to the ExitProc: section, after On Error
Resume Next>

.Send

End With

ExitProc:
On Error Resume Next
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure SendMessage..."
Resume ExitProc
End Sub

___________________________

Also, 209948 was obviously written for Win. 98 / Win. ME operating systems,
since most current Windows operating systems do not include a My Documents
folder hanging off the root drive (ie. C:\My Documents). KB 318881 makes the
assumption, in step 1, that a user will have the correct privleges to
actually save the text file as "TestAttachment.txt" in the root of drive C.
These are pretty poor assumptions in my estimation!

See if you can get the code to work on another PC first. Also, correct an
compile errors that you may be receiving, before attempting to run the
procedure.


Tom
__________________________________________

:

I followed the sample in the KB article (209948), loaded the "Mirosoft
Outlook 10 Object Library (Office XP), and attempted to run the code (VBA).
In Northwind, I received the "Run-time error '48'; Error in loading DLL."

Running the same VBA code in my own mdb module, I redeived a "Compile error:
Sub or function not defined."

I do not know what to do now. I need some "hand-holding."

Jim

 

[Guest]

Hi Tom,
I know why some ask questions and others provide answers. Thank You! Yes,
it does work on my desktop PC! I have agonized over this for quite awhile.
I still have not found the source of my missing/corrupt dll file on my
notebook PC.. I have have the same msoltl.olb files on both computers.......
loaded from my Office XP Pro CD. I have installed SP3 on both machines.
msoutl.olb version (10.0.2607).
I will try to run repair on MS-Office to see if the dll problem corrects
itself. I don't know which dll file is at fault.

"I may be dumb .... but I'm not stupid." Yes, I did copy and paste the vba
into the module. I also ran the "compile" routine in Debug and it processed
without errors.
I also have the same "References" on both PCs.

I put the error handling code you suggested into the routine.

Thanks again for your help. You really "saved the day!"

Jim

 

[Guest]

Hi Jim,

I'm glad to hear that you got it working on another PC. I wish I could offer
more help on how to fix this issue on your notebook PC. One thing that you
might try doing on the notebook PC is to eliminate any unnecessary references
that might be checked. For example, in Access 2000 +, all new databases
include by default references to OLE Automation and the Microsoft ActiveX
Data Objects {version} Library (2.1 for Access 2000 & 2002, but I'm not sure
which version for Access 2003).

Are you using any ADO code in your database? If not, remove the reference to
the Microsoft ActiveX Data Objects Library. You can usually always remove the
reference to OLE Automation. The first two references listed, Visual Basic
for Applications and Microsoft Access {version} Object Library are required
references for all Access apps. These cannot be removed. Anyway, remove all
references that you can, and then re-compile the code. Do you still get the
failure on the Notebook PC? If not, then you've isolated the problem to
loading a reference that wasn't even required anyway. (The MDAC Component
Checker utility is good for helping to troubleshoot issues related to the
ActiveX Data Objects Libraries--more on that later, if you find that this is
implicated in the problem).

I think I'd try renaming your copy of msoutl.olb, and then go throught the
Detect and Repair... routine. Maybe that will help solve this problem.

Tom
_______________________________________

:

Hi Tom,
I know why some ask questions and others provide answers. Thank You! Yes,
it does work on my desktop PC! I have agonized over this for quite awhile.
I still have not found the source of my missing/corrupt dll file on my
notebook PC.. I have have the same msoltl.olb files on both computers.......
loaded from my Office XP Pro CD. I have installed SP3 on both machines.
msoutl.olb version (10.0.2607).
I will try to run repair on MS-Office to see if the dll problem corrects
itself. I don't know which dll file is at fault.

"I may be dumb .... but I'm not stupid." Yes, I did copy and paste the vba
into the module. I also ran the "compile" routine in Debug and it processed
without errors.
I also have the same "References" on both PCs.

I put the error handling code you suggested into the routine.

Thanks again for your help. You really "saved the day!"

Jim

 

[Guest]

Hi Tom,
Well, I eliminated the OLE and renamed MSOUTL.OLB, repaired Office XP and I
still get the "error 48" when I run the module. I may have mentioned before
that the error occurs in the following statement:
Set objOutlook = CreateObject("Outlook.Application")
which is part of:
On Error GoTo ProcError
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
...........and so on

I really thank you for all of you help and good counsel. I'm wondering if
there is some kind of firewall or virus blocker. I turned off Norton and
still get the same error message.

Jim

 

[Guest]

Hi Jim,

Did you try the same experiment after eliminating the reference to the
Microsoft ActiveX Data Objects Library? You may need to create a brand new
bare bones database, eliminate the two extra references OLE Automation and
the Microsoft ActiveX Data Objects 10 Library, and set a reference to the
Outlook 10 Object Library. Then just import enough from a source database to
test this code. That should help nail down whether it is actually this
library at fault.

If you have other ADO code in the same module, then delete it for this test.
When you run code in a module, the entire code is read into memory, even if
you don't test a different procedure (subroutine or function) that is in the
same module. So, I think it would be good to rule out any possibility of the
ActiveX Data Objects library having a problem loading, which just happens to
show up at the place that you indicated.

We can look around on the Microsoft KB site for an article that helps one
completely remove Outlook 2002 from a computer, or if we cannot find that
article then to completely remove Office XP. That would be my next plan of
attack, if you rule out any possible involvement of the ActiveX Data Objects
Library.

Tom
_________________________________________

:

Hi Tom,
Well, I eliminated the OLE and renamed MSOUTL.OLB, repaired Office XP and I
still get the "error 48" when I run the module. I may have mentioned before
that the error occurs in the following statement:
Set objOutlook = CreateObject("Outlook.Application")
which is part of:
On Error GoTo ProcError
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
...........and so on

I really thank you for all of you help and good counsel. I'm wondering if
there is some kind of firewall or virus blocker. I turned off Norton and
still get the same error message.

Jim

 

[Guest]

Hi again .... and thanks for keeping on with this marathon session.

I created a new db2.mdb with a simple "tblMailingList" and the "Sub
SendMessages(Optional AttachmentPath)" subroutine. I commented (') out all
lines following the listing below,except for the "End Sub"
I have the following References (VBA):
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Outlook 10.0 Object Library
..... nothing else.

When I run "SendMessages" from the "Immediate" window, I receive the
following error:
Run-time error '48':
Error in loading DLL

I am stumped

Jim



Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
'Dim objOutlookMsg As Outlook.MailItem
'Dim objOutlookRecip As Outlook.Recipient
'Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

'Do Until MyRS.EOF
' Create the e-mail message.
'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
'TheAddress = MyRS![EmailAddress]

 

[Guest]

Hi Jim,

Okay, let's first try eliminating the DAO Object Library as a potential
source of error, since this library is being loaded in the cut-down version.
Search your hard drive for the file named DAO360.dll. What version do you
have? I have version 3.60.8618.0 for Office XP. Ignore any copies that are in
folders that include ServicePackFiles or dllcache in the folder name. For
example, my copy is found in the following folder:

C:\Program Files\Common Files\Microsoft Shared\DAO

Use the instructions shown in step 5 of this KB article to re-register the
DAO file:
http://support.microsoft.com/?id=303769

For step #5, please note that it is important to include a set of quotes
around the full path to the DAO360.dll file. For example, on my PC the
following path is valid, however, this command will fail:

regsvr32 C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

with a LoadLibrary error. You need to include the quotes, like this, for
this command to be successful:

regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

Use the path that is valid for your PC. An easy way to do this is to try
searching for each .dll file that you need to re-register. When the search is
complete, reduce the size of the find files dialog enough so that you can
display both the search results and the Start > Run dialog on the same
screen. Enter the command: Regsvr32 into the Start > Run dialog. Then use
the drag-and-drop technique to drag the dao360.dll file to the Start > Run
dialog. When you drop it in place, the complete path including the
double-quote marks should be filled in automatically.

If you still have no joy after this, then it's time to look into a complete
removal and re-installation of either Outlook 2002 or Office XP. Here is a KB
article that I found for removing Outlook 2000. I don't see a similar article
available for Outlook 2002, but the steps are likely similar:

OL2000: How to Uninstall Outlook from Office 2000
http://support.microsoft.com/?id=257667

Note that this article includes the following at the bottom:
"This process does not remove all of the Outlook 2000 program files from you
hard disk. The registry entries and the icons for Outlook are either disabled
or removed."

You really do want to remove as much as possible, so that a new install
attempt will have a better chance of actually fixing the problem. (Sometimes
existing files are not replaced during re-installs, so it's best to start
with a clean slate). A link at the bottom of this page leads to the following
article, again for Office 2000, but likely similar for Office 2002:

http://support.microsoft.com/?id=219423

Or, you might want to start by using this KB article instead:

How to manually remove the Microsoft Office XP Developer installation from
your computer
http://support.microsoft.com/?id=827428

If you need to go to these extreme steps, make sure you apply the service
packs after re-installing Office XP. I'll include more information on how to
do that later, if you find that you need to do this.


Tom
__________________________________________

:

Hi again .... and thanks for keeping on with this marathon session.

I created a new db2.mdb with a simple "tblMailingList" and the "Sub
SendMessages(Optional AttachmentPath)" subroutine. I commented (') out all
lines following the listing below,except for the "End Sub"
I have the following References (VBA):
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Outlook 10.0 Object Library
..... nothing else.

When I run "SendMessages" from the "Immediate" window, I receive the
following error:
Run-time error '48':
Error in loading DLL

I am stumped

Jim


Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
'Dim objOutlookMsg As Outlook.MailItem
'Dim objOutlookRecip As Outlook.Recipient
'Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

'Do Until MyRS.EOF
' Create the e-mail message.
'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
'TheAddress = MyRS![EmailAddress]

<snip>

 

[Guest]

PS. I forgot to add something in my last reply:

Change the following two lines of code:

From:
Dim MyDB As Database
Dim MyRS As Recordset

To:
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

This doesn't have anything to do with the immediate problem on your notebook
PC with error 48, but it will help to prevent any ambiguity in the future.
For more information on this topic, please see the following article:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/gem_tips1.html


Tom

 

[Guest]

Hi Tom,
Again, I am sorry for not getting back to you earlier.
I followed your suggestion on replacing the dao360.dll, but to no avail. I
stilll have the "Error in loading dll."
I am at the point of removal and re-installation of Outlook/Office, but have
been occupied on other work.

One note: When I use the Access program on my desktop PC and send (.send) an
email w/attachment, it works as desired. However, when I close Access and
try to open Outlook, to"check" my email, Outlook will not open. I have to
re-boot, then it works. Does the (.send) mess with something within the
Outlook program? Just a thought.
Jim

Hi Jim,

Okay, let's first try eliminating the DAO Object Library as a potential
source of error, since this library is being loaded in the cut-down version.
Search your hard drive for the file named DAO360.dll. What version do you
have? I have version 3.60.8618.0 for Office XP. Ignore any copies that are in
folders that include ServicePackFiles or dllcache in the folder name. For
example, my copy is found in the following folder:

C:\Program Files\Common Files\Microsoft Shared\DAO

Use the instructions shown in step 5 of this KB article to re-register the
DAO file:
http://support.microsoft.com/?id=303769

For step #5, please note that it is important to include a set of quotes
around the full path to the DAO360.dll file. For example, on my PC the
following path is valid, however, this command will fail:

regsvr32 C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

with a LoadLibrary error. You need to include the quotes, like this, for
this command to be successful:

regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

Use the path that is valid for your PC. An easy way to do this is to try
searching for each .dll file that you need to re-register. When the search is
complete, reduce the size of the find files dialog enough so that you can
display both the search results and the Start > Run dialog on the same
screen. Enter the command: Regsvr32 into the Start > Run dialog. Then use
the drag-and-drop technique to drag the dao360.dll file to the Start > Run
dialog. When you drop it in place, the complete path including the
double-quote marks should be filled in automatically.

If you still have no joy after this, then it's time to look into a complete
removal and re-installation of either Outlook 2002 or Office XP. Here is a KB
article that I found for removing Outlook 2000. I don't see a similar article
available for Outlook 2002, but the steps are likely similar:

OL2000: How to Uninstall Outlook from Office 2000
http://support.microsoft.com/?id=257667

Note that this article includes the following at the bottom:
"This process does not remove all of the Outlook 2000 program files from you
hard disk. The registry entries and the icons for Outlook are either disabled
or removed."

You really do want to remove as much as possible, so that a new install
attempt will have a better chance of actually fixing the problem. (Sometimes
existing files are not replaced during re-installs, so it's best to start
with a clean slate). A link at the bottom of this page leads to the following
article, again for Office 2000, but likely similar for Office 2002:

http://support.microsoft.com/?id=219423

Or, you might want to start by using this KB article instead:

How to manually remove the Microsoft Office XP Developer installation from
your computer
http://support.microsoft.com/?id=827428

If you need to go to these extreme steps, make sure you apply the service
packs after re-installing Office XP. I'll include more information on how to
do that later, if you find that you need to do this.


Tom
__________________________________________

:

Hi again .... and thanks for keeping on with this marathon session.

I created a new db2.mdb with a simple "tblMailingList" and the "Sub
SendMessages(Optional AttachmentPath)" subroutine. I commented (') out all
lines following the listing below,except for the "End Sub"
I have the following References (VBA):
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Outlook 10.0 Object Library
.... nothing else.

When I run "SendMessages" from the "Immediate" window, I receive the
following error:
Run-time error '48':
Error in loading DLL

I am stumped

Jim


Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
'Dim objOutlookMsg As Outlook.MailItem
'Dim objOutlookRecip As Outlook.Recipient
'Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

'Do Until MyRS.EOF
' Create the e-mail message.
'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
'TheAddress = MyRS![EmailAddress]

<snip>

 

[Guest]

Hi Jim,

My only remaining suggestions are:

1.) Try to call Microsoft for help. If they are willing to provide some low
cost or free help, then this problem can likely be solved. They obviously
have resourses to information that common people, like myself, do not have
available. (I do not work at Microsoft).

2.) Take the more drastic measures previously discussed, ie. attempt to
completely remove and re-install Outlook. If that fails, then attempt to
completely remove and re-install Office.

One note: When I use the Access program on my desktop PC and send
(.send) an email w/attachment, it works as desired. However, when I
close Access and try to open Outlook, to"check" my email, Outlook will not
open. I have to re-boot, then it works. Does the (.send) mess with something
within the Outlook program?

I have not seen this type of error either. Outlook has a built-in security
feature, that I call the "file whacker", which will make so-called
"potentially unsafe" file attachments unavailable, if you receive a message
from another person with such an attached file. It turns out that .mdb files
are included on the hit list. To get around this issue, you need to make a
change to your registry setting, by adding a new key named "Level1Remove"
(without the quotes), as discussed in this KB article:

Cannot open attachments in Microsoft Outlook
http://support.microsoft.com/?id=829982


This article includes the following notes, so it may not work for you:

You can modify the attachment security behavior in Outlook if you are using
Outlook in one of the following scenarios:

You are not using Outlook in an Exchange environment.

In an Exchange environment, the administrator has not configured the Outlook
Security settings to disallow changes to the attachment security behavior.


Sorry I cannot be of more help.

Tom
_________________________________________

:

Hi Tom,
Again, I am sorry for not getting back to you earlier.
I followed your suggestion on replacing the dao360.dll, but to no avail. I
stilll have the "Error in loading dll."
I am at the point of removal and re-installation of Outlook/Office, but have
been occupied on other work.

One note: When I use the Access program on my desktop PC and send (.send) an
email w/attachment, it works as desired. However, when I close Access and
try to open Outlook, to"check" my email, Outlook will not open. I have to
re-boot, then it works. Does the (.send) mess with something within the
Outlook program? Just a thought.
Jim