Send Email with Access 2007

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I used the code below in Access 2002 and it worked fine. Just upgraded to
Access 2007 and I am getting an error message as I try to send an email.

The error message is:
‘The command or action ‘SendObject’ isn’t available now’

I have set a reference to Microsoft Outlook 10.0 Object Library

I have MS Office 2007 installed (and Outlook 2007 is installed) but the
reference seems to be pointing to Outlook 2002. I referenced the highest
order of Outlook that I could find in my available references. I’ve seen
higher order references in the list; for instance I have a reference for
Microsoft Excel 12.0 Object Library (but no 10.0 Object Library)

Here is the code that I am working with:

Option Compare Database

Private Sub cmdMail_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim strSQL As String '-- Create SQL update statement
Dim stWho As String '-- Reference to tblUsers
Dim errLoop As Error
Dim strFirstName As String

'-- Combo of names to assign ticket to
stWho = Me.cboUser
stWhere = "Users.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from Users
varTo = DLookup("[strEMail]", "Users", stWhere)

stText = ", please see the attachment." & Chr$(13) & Chr$(13) & _
"Thanks," & RecDate & Chr$(13) & Chr$(13) & _
"Ryan---"

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:
Resume Next
Exit_cmdMailTicket_Click:
Exit Sub

Err_cmdMailTicket_Click:
MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Sub

Basically, I used to be able to select a record from a ComboBox, and send an
email to that record. Now, when I click the CommandButton, I get the error
message: ‘The command or action ‘SendObject’ isn’t available now’

As an aside, I am running Office 2002 and Office 2007 (simultaneously) on my
ThinkPad. I have had trouble running several VBA-driven Access 2002 files
(most give me errors, but some work perfectly well). AFAIK, Office 2002 and
Office 2007 work in on the same machine, right. I’ve only had problems with
VBA in Access. I do lots of VBA work in Word and Excel too; Access is the
only app that gives me problems

Thanks everyone!
Ryan---
 
Thanks Orange! I ended up loading my 2002 Office CD, did a repair, and the
issue went away.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


orange via AccessMonster.com said:
ryguy7272 said:
I used the code below in Access 2002 and it worked fine. Just upgraded to
Access 2007 and I am getting an error message as I try to send an email.

The error message is:
‘The command or action ‘SendObject’ isn’t available now’

I have set a reference to Microsoft Outlook 10.0 Object Library

I have MS Office 2007 installed (and Outlook 2007 is installed) but the
reference seems to be pointing to Outlook 2002. I referenced the highest
order of Outlook that I could find in my available references. I’ve seen
higher order references in the list; for instance I have a reference for
Microsoft Excel 12.0 Object Library (but no 10.0 Object Library)

Here is the code that I am working with:

Option Compare Database

Private Sub cmdMail_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim strSQL As String '-- Create SQL update statement
Dim stWho As String '-- Reference to tblUsers
Dim errLoop As Error
Dim strFirstName As String

'-- Combo of names to assign ticket to
stWho = Me.cboUser
stWhere = "Users.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from Users
varTo = DLookup("[strEMail]", "Users", stWhere)

stText = ", please see the attachment." & Chr$(13) & Chr$(13) & _
"Thanks," & RecDate & Chr$(13) & Chr$(13) & _
"Ryan---"

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:
Resume Next
Exit_cmdMailTicket_Click:
Exit Sub

Err_cmdMailTicket_Click:
MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Sub

Basically, I used to be able to select a record from a ComboBox, and send an
email to that record. Now, when I click the CommandButton, I get the error
message: ‘The command or action ‘SendObject’ isn’t available now’

As an aside, I am running Office 2002 and Office 2007 (simultaneously) on my
ThinkPad. I have had trouble running several VBA-driven Access 2002 files
(most give me errors, but some work perfectly well). AFAIK, Office 2002 and
Office 2007 work in on the same machine, right. I’ve only had problems with
VBA in Access. I do lots of VBA work in Word and Excel too; Access is the
only app that gives me problems

Thanks everyone!
Ryan---
Click to expand...
I don't see anything wrong syntax wise. Here's a sample of sending an email.
http://www.databasedev.co.uk/send_email.html

The only thing I can think of is some piece(s) of Office 2002 and Office 2007
could be interfering with each other. I have heard of people using 2002 and
2007 simultaneously, but I don't know if there were any situations/issues
with regard to the order of installations or the placement of files/folders.
Click to expand...
 
Back
Top