Sending e-mail from Access Form

[Nancy T.]

We publish a monthly trade magazine and I have a database with all of our
advertisers and the months that they run ads. Every month I send an e-mail
reminder to several of them reminding them of the deadline date to submit a
new ad for the next issue. I have been doing this for about 2 years with no
problems. Recently I had a problem with my Vista computer and I had to
totally reinstall Vista, which meant that I had to reinstall all of my
programs. (I really HATE Vista!) I reinstalled Office 2007, then remembered
that it did not include Access. I then had to install Access from Office XP.
(I hate Vista, but I DESPISE Office 2007!) Anyhow, now my form to send my
e-mail is not working. Everything works fine up to when I hit the button to
send. Here is a truncated version of the code. All the strings are loaded
with text prior to this.

Dim msg As Outlook.MailItem

Private Sub cmdSendEMail_Click()
Dim msg As Outlook.MailItem
On Error GoTo ErrorHandler

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

(This next line is the one that generates the error)
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = "(e-mail address removed)" (this is my e-mail address here)
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
.Send
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

There error message I get is Error No: 13; Description: Type mismatch

There references that are checked are:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Outlook 12.0 Object Library

Any ideas what may be suddenly causing this error?

TIA,
Nancy

 

[Graham Mandeno]

Hi Nancy

On which line does the error occur?

 

[Nancy T.]

Sorry, I guess I should have made that clearer. I put it within the actual
code which line the error came on. However, this is what causes the error:

Set msg = appOutlook.CreateItem(olMailItem)

Nancy

Hi Nancy

On which line does the error occur?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

We publish a monthly trade magazine and I have a database with all of our
advertisers and the months that they run ads. Every month I send an e-mail
reminder to several of them reminding them of the deadline date to submit
a
new ad for the next issue. I have been doing this for about 2 years with
no
problems. Recently I had a problem with my Vista computer and I had to
totally reinstall Vista, which meant that I had to reinstall all of my
programs. (I really HATE Vista!) I reinstalled Office 2007, then
remembered
that it did not include Access. I then had to install Access from Office
XP.
(I hate Vista, but I DESPISE Office 2007!) Anyhow, now my form to send my
e-mail is not working. Everything works fine up to when I hit the button
to
send. Here is a truncated version of the code. All the strings are loaded
with text prior to this.

Dim msg As Outlook.MailItem

Private Sub cmdSendEMail_Click()
Dim msg As Outlook.MailItem
On Error GoTo ErrorHandler

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

(This next line is the one that generates the error)
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = "(e-mail address removed)" (this is my e-mail address here)
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
.Send
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

There error message I get is Error No: 13; Description: Type mismatch

There references that are checked are:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Outlook 12.0 Object Library

Any ideas what may be suddenly causing this error?

TIA,
Nancy

 

[Graham Mandeno]

Hi Nancy

How is appOutlook declared?
(should be either Object or Outlook.Application)

How is it initialised?
(should be Set appOutlook = CreateObject("Outlook.Application")

If both these are OK, try late binding (declare both AppOutlook and msg As
Object)

Late binding makes no compile-time assumptions about types and object
structures, so is marginally slower but heaps more reliable when moving from
one machine to another, where different versions of the software might be
installed.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry, I guess I should have made that clearer. I put it within the actual
code which line the error came on. However, this is what causes the error:

Set msg = appOutlook.CreateItem(olMailItem)

Nancy

Hi Nancy

On which line does the error occur?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

We publish a monthly trade magazine and I have a database with all of
our
advertisers and the months that they run ads. Every month I send an
e-mail
reminder to several of them reminding them of the deadline date to
submit
a
new ad for the next issue. I have been doing this for about 2 years
with
no
problems. Recently I had a problem with my Vista computer and I had to
totally reinstall Vista, which meant that I had to reinstall all of my
programs. (I really HATE Vista!) I reinstalled Office 2007, then
remembered
that it did not include Access. I then had to install Access from
Office
XP.
(I hate Vista, but I DESPISE Office 2007!) Anyhow, now my form to send
my
e-mail is not working. Everything works fine up to when I hit the
button
to
send. Here is a truncated version of the code. All the strings are
loaded
with text prior to this.

Dim msg As Outlook.MailItem

Private Sub cmdSendEMail_Click()
Dim msg As Outlook.MailItem
On Error GoTo ErrorHandler

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

(This next line is the one that generates the error)
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = "(e-mail address removed)" (this is my e-mail address here)
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
.Send
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit

End Sub

There error message I get is Error No: 13; Description: Type mismatch

There references that are checked are:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Outlook 12.0 Object Library

Any ideas what may be suddenly causing this error?

TIA,
Nancy

 

[Nancy T.]

I am getting a new error message. It now says:
Error No: 429; Description: ActiveX component can't create object

Here is all my code pertaining to this. I have a feeling my problem comes
from installing Access XP AFTER installing Office 2007. (See my first post.)
I think I may have to uninstall both and then install Office XP then the
Office 2007. Unless of course anybody has a better suggestion.


Private Sub cmdSendEMail_Click()
On Error GoTo ErrorHandler

Dim appOutlook As New Outlook.Application
Dim msg As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

With msg
.To = "(e-mail address removed)"
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

 

[Graham Mandeno]

Hi Nancy

Presumably you installed ONLY Access (not Outlook) when you installed Office
XP?

I doubt that it is an installation issue, but if it is then it should be
sufficient just to reinstall Office 2007 (without Access!), instead of
uninstalling and then reinstalling both versions.

Have you tried late binding, as I suggested? I really think it will solve
the problem.

It can be very advantageous when developing your code to use early binding,
because it gives you nice features such as syntax checking and intellisense.
However, I always switch to late binding before I deploy my apps. One huge
advantage of this is that the code will still work if a different version of
the external app (in this case, Outlook) is installed on the target machine.

To make it ease to switch between the two, I use conditional compilation.
For example:

#Const Outlook_EarlyBinding = True

#If Outlook_EarlyBinding Then
Debug.Assert olMailItem = 0
Dim AppOutlook As Outlook.Application
Dim msg As Outlook.MailItem
#Else
Private Const olMailItem = 0
Dim AppOutlook As Object
Dim msg As Object
#End If

You set the #Const to True while developing, and add a reference to the
appropriate library. The object variables are fully declared, to give you
the early binding advantages. Any constants you use should have a
Debug.Assert (as with olMailItem above) to verify that you are using the
correct value for the Const declaration in the #Else block.

When you are done developing, change the #Const value to False and remove
the reference.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting a new error message. It now says:
Error No: 429; Description: ActiveX component can't create object

Here is all my code pertaining to this. I have a feeling my problem comes
from installing Access XP AFTER installing Office 2007. (See my first
post.)
I think I may have to uninstall both and then install Office XP then the
Office 2007. Unless of course anybody has a better suggestion.


Private Sub cmdSendEMail_Click()
On Error GoTo ErrorHandler

Dim appOutlook As New Outlook.Application
Dim msg As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

With msg
.To = "(e-mail address removed)"
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

Hi Nancy

How is appOutlook declared?
(should be either Object or Outlook.Application)

How is it initialised?
(should be Set appOutlook = CreateObject("Outlook.Application")

If both these are OK, try late binding (declare both AppOutlook and msg
As
Object)

Late binding makes no compile-time assumptions about types and object
structures, so is marginally slower but heaps more reliable when moving
from
one machine to another, where different versions of the software might be
installed.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Nancy T.]

First off, let me just say THANK YOU!!!!

I spent all morning and part of the afternoon learning about late binding. I
revised my code and I was still getting an error message. (Luckily we don't
have a sledge hammer in the office or my computer would be in tiny pieces
right now.) I then took your suggestion and inserted the Office 2007 disk
again and told it to Repair. I then opened my database and tried it again
and... It worked!!!! As you said, it was a little slower than before with the
late binding, but I have no problem waiting 15 or 20 seconds.

So did I remember to say THANK YOU!?!?

Hi Nancy

Presumably you installed ONLY Access (not Outlook) when you installed Office
XP?

I doubt that it is an installation issue, but if it is then it should be
sufficient just to reinstall Office 2007 (without Access!), instead of
uninstalling and then reinstalling both versions.

Have you tried late binding, as I suggested? I really think it will solve
the problem.

It can be very advantageous when developing your code to use early binding,
because it gives you nice features such as syntax checking and intellisense.
However, I always switch to late binding before I deploy my apps. One huge
advantage of this is that the code will still work if a different version of
the external app (in this case, Outlook) is installed on the target machine.

To make it ease to switch between the two, I use conditional compilation.
For example:

#Const Outlook_EarlyBinding = True

#If Outlook_EarlyBinding Then
Debug.Assert olMailItem = 0
Dim AppOutlook As Outlook.Application
Dim msg As Outlook.MailItem
#Else
Private Const olMailItem = 0
Dim AppOutlook As Object
Dim msg As Object
#End If

You set the #Const to True while developing, and add a reference to the
appropriate library. The object variables are fully declared, to give you
the early binding advantages. Any constants you use should have a
Debug.Assert (as with olMailItem above) to verify that you are using the
correct value for the Const declaration in the #Else block.

When you are done developing, change the #Const value to False and remove
the reference.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting a new error message. It now says:
Error No: 429; Description: ActiveX component can't create object

Here is all my code pertaining to this. I have a feeling my problem comes
from installing Access XP AFTER installing Office 2007. (See my first
post.)
I think I may have to uninstall both and then install Office XP then the
Office 2007. Unless of course anybody has a better suggestion.


Private Sub cmdSendEMail_Click()
On Error GoTo ErrorHandler

Dim appOutlook As New Outlook.Application
Dim msg As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)

strRecipients = Me![txtRecipients].Value
strSubject = Me![txtMessageSubject].Value
strBody = Me![txtMessageBody].Value

With msg
.To = "(e-mail address removed)"
.BCC = strRecipients
.Subject = strSubject
.Body = strBody
.Display
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

Hi Nancy

How is appOutlook declared?
(should be either Object or Outlook.Application)

How is it initialised?
(should be Set appOutlook = CreateObject("Outlook.Application")

If both these are OK, try late binding (declare both AppOutlook and msg
As
Object)

Late binding makes no compile-time assumptions about types and object
structures, so is marginally slower but heaps more reliable when moving
from
one machine to another, where different versions of the software might be
installed.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand