We help IT Professionals succeed at work.

Access 2000 email problem with Outlook

silverbob asked
First of all, there was no problem sending email under Access 97.

Now, with Access 2000, when an email function is initiated, one of the following random outcomes results:

1. Run-time error 2501
2. Run-time error 2958
3. No error - email sent - msgbox alert that email was sent.
4. No error - email held in Outlook Outbox - msgbox alert that email was sent
5. No error - no email sent -  msgbox alert that email was sent

With further testing, for some reason, items 1 and 2 above have ceased, but 3, 4 and 5 persist.  (I have no idea why).  

It appears that initially (after a clean start of Access and Outlook) the email will either be sent or get hung up in the Outlook Outbox.  

After 1 or 2 further attempts, the email doesn't get sent at all.  The DoCmd.SendObject command is completely ignored and the following Msgbox("Mail was sent") is executed.  I have put this in debug mode and verified that the DoCmd is being touched and that there are values associated with the command variables.

The code I am using is

DoCmd.SendObject , , , strRecipient, , , strSubject, strMessage, False
MsgBox ("Email has been sent!")

I have experimented using a different email procedure, but ended up with the same results.
(See MS article Q161088 on Automation)

Can anyone help me get this email function working again?
Watch Question

You may have to reset your reference to the next version of outlook.  Check your references and set the latest version of outlook reference.
The reason your msgbox is always saying the e-mail is sent is because you dont have any logic such as a boolean expression to evaluate if the object has actually been sent.  It is merely executing the next line in your code.
My best advice is to do away with the docmd.sendobject and use the outlook object model.  I will post the code momentarily.
Let me know if you need help getting this to run.  NOTE:
you can refer to fields and other variables in the recipients (To, CC, BCC), just eliminate the ""'s.

Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
    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.

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
         ' Add the To recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
         objOutlookRecip.Type = olTo
         ' Add the CC recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Michael Suyama")
         objOutlookRecip.Type = olCC
        ' Add the BCC recipient(s) to the message.

         Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
         objOutlookRecip.Type = olBCC
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is the body of the message." & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance
        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then

            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If
        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
        ' Should we display the message before sending?
        If DisplayMsg Then
        End If

    End With
    Set objOutlook = Nothing
End Sub


PD -As I mentioned in my question,  I have already tried that code.  That's the same script from MS article Q161088.  What reference item are you referring to?  The MS Access 9.0 Object Library is checked by default and the MS Outlook 9.0 Object Library is also checked.
Sorry, I didn't look at that article.  Yes I was referring to the MS Outlook 9.0 Object Library.   Try checking the Microsoft Office reference also.
Are you resolving your recipients before sending?
Are you sending it to the same recipient each time?
There is a bug listed on the MSKB that talks about a security issue using the docmd.sendobject, did you look at that?
I have code that will use the MAPI object rather than outlook if you are interested.


To answer your questions...

1) I resolve my recipients prior to sending.  The actual recipients are decided using a checkbox form, i.e.

' Are we sending email to Sales?

If Me.CheckboxSalesRep Then
    If IsNull(Me.Salesperson) Then
        MsgBox ("A sales rep must be selected")
        Exit Sub
        strRecipient = strRecipient & Me.Salesperson.Column(1) & Me.Salesperson.Column(2) & ";" & Tech_Mgr & ";"
    End If
End If

2) The recipients are variable and sometimes include a list of people

3) I searched for the security article.  Found one talking about single and dbl quotes around recipient names.  Is that the one you meant?

I checked the "MS Office 9.0 Object Library" reference item, and still have the same problem.
I successfully sent one email.  It sat in the Outlook Outbox for a few seconds and then got sent.
My 2nd try didn't send anything.  The DoCmd line was treated like it was invisible.  In debug mode, I could see that the line was touched and the variables contained values, but Access did nothing with it.

I'd be willing to look at your MAPI script if you don't mind.

Also, unrelated to this.  How can I send an external text file?

DoCmd.SendObject ,"F:\tempkeys\1101.txt", acFormatTXT, strRecipient, , , strSubject, , False

will send me an email (this is with Access97) but I don't get the text file.
Take a look at this article while I dig up that code for you, it sounds like exactly what you're experiencing:


Right.  Two of the symptoms match; either no message, or a runtime 2501, though I haven't seen the runtime error in quite awhile now.  I don't quite understand what I'm supposed to do.  This article contains 4 or 5 pages of code with instructions to use it in the Northwind db.   There is also a reference to Q161088, which is the article I mentioned in my original question.  I have tried the code in Q161088 with the same results.
Yeah, I guess the article is pretty confusing, isn't it.
Did you go to www.Microsoft.com to see if there are any available service-packs you can download?
Also, I dont think you can send an attachment using the SendObject, I think it has to be done with outlook/MAPI
I will try to dig up that MAPI code now.  Be back soon.
Here is that code.  Its similar to what they had in that MSKB article.  I think you have to set a reference to CDO:

Sub TestMAPIEmail()
Dim clMAPI As clsMAPI
    Set clMAPI = New clsMAPIEmail
    With clMAPI
        .MAPISetMessageBody = "Test Message"
        .MAPISetMessageSubject = "Some Test"
        .MAPIAddRecipient stPerson:="dash10@hotmail.com", _
                                    intAddressType:=1         'To
        .MAPIAddRecipient stPerson:="Dev Ashish", _
                                    intAddressType:=2         'cc
        .MAPIAddRecipient stPerson:="smtp:dash10@hotmail.com", _
                                    intAddressType:=3         'bcc

        .MAPIAddAttachment "C:\temp\Readme.doc", "Jet Readme"
        .MAPIAddAttachment stFile:="C:\config.sys"

        .MAPISendMessage boolSaveCopy:=False
    End With
End Sub
'**************** Usage Example End ****************

'**************** Class Start ***********************
Option Compare Database
Option Explicit

Private mobjSession As MAPI.Session
Private mobjMessage As Message
Private mboolErr As Boolean
Private mstStatus As String
Private mobjNewMessage As Message

Private Const mcERR_DOH = vbObjectError + 10000
Private Const mcERR_DECIMAL = 261144    'low word order +1000

Public Sub MAPIAddMessage()
    With mobjSession
        Set mobjNewMessage = .Outbox.Messages.Add
    End With
End Sub

Public Sub MAPIUpdateMessage()
End Sub

Private Sub Class_Initialize()
    mboolErr = False
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
    Set mobjMessage = Nothing
    Set mobjSession = Nothing
End Sub

Public Property Let MAPISetMessageBody(stBodyText As String)
    If Len(stBodyText) > 0 Then mobjNewMessage.Text = stBodyText
End Property

Public Property Let MAPISetMessageSubject(stSubject As String)
    If Len(stSubject) > 0 Then mobjNewMessage.Subject = stSubject
End Property

Public Property Get MAPIIsError() As Boolean
    MAPIIsError = mboolErr
End Property

Public Property Get MAPIRecipientCount() As Integer
    MAPIRecipientCount = mobjNewMessage.Recipients.Count
End Property

Public Sub MAPIAddAttachment(stFile As String, _
                        Optional stLabel As Variant)
Dim objAttachment As Attachment
Dim stMsg As String

    On Error GoTo Error_MAPIAddAttachment

    If mboolErr Then Err.Raise mcERR_DOH
    If Len(Dir(stFile)) = 0 Then Err.Raise mcERR_DOH + 10

    mstStatus = SysCmd(acSysCmdSetStatus, "Adding Attachments...")

    If IsMissing(stLabel) Then stLabel = CStr(stFile)

    With mobjNewMessage
        .Text = " " & mobjNewMessage.Text
        Set objAttachment = .Attachments.Add
        With objAttachment
            .Position = 0
            .Name = stLabel
            'no need to link a file me thinks
            .Type = CdoFileData
            .ReadFromFile stFile
        End With
    End With

    Set objAttachment = Nothing
    Exit Sub
    mboolErr = True
    If Err = mcERR_DOH + 10 Then
        stMsg = "Couldn't locate the file " & vbCrLf
        stMsg = stMsg & "'" & stFile & "'." & vbCrLf
        stMsg = stMsg & "Please check the file name and path and try again."
        MsgBox stMsg, vbExclamation + vbOKOnly, "File Not Found"
    ElseIf Err <> mcERR_DOH Then
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    End If
    Resume Exit_MAPIAddAttachment
End Sub

Public Sub MAPIAddRecipient(stPerson As String, intAddressType As Integer)
Dim objNewRecipient As Recipient 'local

    On Error GoTo Error_MAPIAddRecipient
    mstStatus = SysCmd(acSysCmdSetStatus, "Adding Recipients...")

    If mboolErr Then Err.Raise mcERR_DOH

    'If there's no SMTP present in the stPerson var, then
    'we have to use Name, else Address
    With mobjNewMessage
        If InStr(1, stPerson, "SMTP:") > 0 Then
            Set objNewRecipient = .Recipients.Add(Address:=stPerson, _
            Set objNewRecipient = .Recipients.Add(Name:=stPerson, _
        End If
    End With

    Set objNewRecipient = Nothing
    Exit Sub

    mboolErr = True
    Resume Exit_MAPIAddRecipient
End Sub

Public Sub MAPISendMessage(Optional boolSaveCopy As Variant, _
                            Optional boolShowDialog As Variant)

    mstStatus = SysCmd(acSysCmdSetStatus, "Sending message...")
    If IsMissing(boolSaveCopy) Then
        boolSaveCopy = True
    End If
    If IsMissing(boolShowDialog) Then
        boolShowDialog = False
    End If

    mobjNewMessage.Send savecopy:=boolSaveCopy, showdialog:=boolShowDialog
End Sub

Public Sub MAPILogon()
On Error GoTo err_sMAPILogon
Const cERROR_USERCANCEL = -2147221229

    mstStatus = SysCmd(acSysCmdSetStatus, "Login....")
    Set mobjSession = CreateObject("MAPI.Session")

    Exit Sub

    mboolErr = True
        MsgBox "Logon Failed", vbCritical + vbOKOnly, "Error"
    ElseIf Err = cERROR_USERCANCEL Then
        MsgBox "Aborting since you pressed cancel.", _
                vbOKOnly + vbInformation, "Operatoin Cancelled!"
        MsgBox "Error number " & Err - mcERR_DECIMAL & " description. " _
                & Error$(Err)
    End If
    Resume exit_sMAPILogon
End Sub

Public Sub MAPILogoff()
On Error GoTo err_sMAPILogoff
    mstStatus = SysCmd(acSysCmdSetStatus, "Logging off...")

    Set mobjNewMessage = Nothing
    Set mobjSession = Nothing
    mstStatus = SysCmd(acSysCmdClearStatus)
    Exit Sub

    Resume exit_sMAPILogoff
End Sub
'**************** Class End  ***********************


Sorry I have responded in awhile, but there have been problems with the EE web site.

pd - I appreciate the above code, but I can't believe I have to substitute the above 100 lines in place of a single DoCmd.SendObject.  There must be a "simple" resolution to this problem.


OK, for anyone who is still interested in this thread...

I no longer believe that the code is the problem.  But, I still can't figure this out.

Copy 1 of our db, converted to 2000, and run on Win2k machine A will repeatedly send email.
Copy 2 of our db, converted to 2000, and run on Win2k machine B will fail to send email after one success.
Copy 1 of our db, run on Win2k machine B will fail to send email after one success.
Copy 2 of our db run on Win2k machine A will fail to send email after one success.

Both machines are running identical software.
Versions match exactly for:

This feels like some sort of initialization problem.  The DoCmd.SendObject works fine the first time around on Machine B, but is ignored after the first invocation.  

Can anyone tell me what else I should be looking at?
I know it sounds ridiculous, but the DoCmd.SendObject method is full of errors.  Also, The 100 lines of code actually execute faster than the DoCmd method and are more flexible.  If you search EE for sendobject you will see what I mean.




Psycho -

I thought I had this resolved.  I was quite certain that my problem was from compiling the modules before the conversion.  Well, that doesn't hold true anymore.  I really love inconsistent results.  Thanks MicroSoft.

Well, I gave in and decided to try the Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath) that you provided above.  But, its producing errors.  I get

"The expression On Open you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name."

What does that mean?

By the way, you're getting the points regardless how this turns out.


Well, I got rid of the arguments to the SUB statement and the error went away.
Now, it's just Sub SendMessage()
and I had to comment out the variables the used to be in the argument list.

Now, it seems to work.  I just sent 3 emails in a row.
But, now I'm getting a new dialog I've never seen before.  It reads...

   A program is trying to access e-mail addresses you have stored in Outlook.  Do you want to    allow this?"

   If this is unexpected, it may be a virus and you should choose "No".
   The options are "Allow access for: 1min, 2 mins or 10 mins. "
That is due to your security settings.  There is an article on MSKB that deals with that, I will try to dig it upfor you.


Thanks for the article.  Actually, that link addresses a different security msg I get.  According to the article, I get this because of a security update we applied.  It also says the administrator can override these default settings.  I will look into this.




I'm running into another problem now.  After implementing the Outlook email code, I'm getting...

Run-time error '-451919867 (e5104005)':
Outlook does not recognize one or more names.

There is nothing at MS about this, but searching deja.com I found threads about this exact error dating back to 1998.  And, here's what I discovered...

Each Set objOutlookRecip = .Recipients.Add() can only accept one email address

and I'm trying to send to multiple people.  At the moment, I create a list of recipients and put the entire list into one variable called strRecipient.  So, I guess I could try to split them off into separate variables for TO:, CC: and BCC:

But, what if I need to send to more than three people, which is really common for this app.?  Am I limited to sending to only three people at a time?


To give you a better idea of what I'm doing...

strRecipient = ""
strSubject = Me.Product & " " & Me.session_type & " for " & Me.Company

' Are we sending email to the Techie?

If Me.CheckboxMyself Then
    If IsNull(Me.Techie) Then
        MsgBox ("You must select your name from the drop-down list")
        Exit Sub
        strRecipient = Me.Techie.Column(1) & ";"
    End If
End If

' Are we sending email to Sales?

If Me.CheckboxSalesRep Then
    If IsNull(Me.Salesperson) Then
        MsgBox ("A sales rep must be selected")
        Exit Sub
    ElseIf Me.CumSessionTime > Long_session_time Then
        strRecipient = strRecipient & Me.Salesperson.Column(1) & Me.Salesperson.Column(2) & ";" & Tech_VP & ";" & Tech_Mgr & ";"
        strRecipient = strRecipient & Me.Salesperson.Column(1) & Me.Salesperson.Column(2) & ";" & Tech_Mgr & ";"
    End If
End If

' Sending email to anyone else?

If Me.CheckboxOther Then
    If IsNull(Me.Email_other) Then
        MsgBox ("An email address must be entered in 'Email to...'")
        Exit Sub
        strRecipient = strRecipient & Me.Email_other
    End If
End If

' If button was clicked with no boxes checked, exit subroutine

If Not Me.CheckboxMyself And Not Me.CheckboxSalesRep And Not Me.CheckboxOther Then Exit Sub

Now, the salesrep is a special case because I do a table lookup.  I find the salesrep's name in column 0, and lookup the email addresses in columns 1 and 2

col 0                  col 1                                       col 2
Salesperson   Salesperson_emails         SalesMgr_email
-----------------    ----------------------------            -----------------------  
Bill Brossia      bbrossia@symark.com     ;rfarber@symark.com;arose@symark.com    

So, from the above code, you can see that I would want to potentially send email to about 5 or more people with a single click.  (techie, salesrep, salesmgrs, other)

This worked fine with DoCmd.SendObject.

Now what do I do?


PD - thanks for all your help.  Now I need to get around all those Outlook security dialogs (see my next post)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.