We help IT Professionals succeed at work.

Access 2000 email problem with Outlook

silverbob
silverbob asked
on
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?
Comment
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
            objOutlookRecip.Resolve
        Next
       
        ' Should we display the message before sending?
        If DisplayMsg Then
            .Display
        Else
            .Send
        End If

    End With
    Set objOutlook = Nothing
End Sub

Author

Commented:
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?
Also-
I have code that will use the MAPI object rather than outlook if you are interested.

Author

Commented:
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
        Beep
        MsgBox ("A sales rep must be selected")
        Me.Salesperson.SetFocus
        Exit Sub
    Else
        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:
http://support.microsoft.com/support/kb/articles/Q260/8/19.ASP?LN=EN-US&SD=gn&FR=0&qry=MAPI&rnk=23&src=DHCS_MSPSS_gn_SRCH&SPR=ACC

Author

Commented:
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
        .MAPILogon
        .MAPIAddMessage
        .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"

        .MAPIUpdateMessage
        .MAPISendMessage boolSaveCopy:=False
        .MAPILogoff
    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()
    mobjNewMessage.Update
End Sub

Private Sub Class_Initialize()
    mboolErr = False
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
    Set mobjMessage = Nothing
    mobjSession.Logoff
    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
        .Update
    End With

Exit_MAPIAddAttachment:
    Set objAttachment = Nothing
    Exit Sub
Error_MAPIAddAttachment:
    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, _
                                                Type:=intAddressType)
        Else
            Set objNewRecipient = .Recipients.Add(Name:=stPerson, _
                                                Type:=intAddressType)
        End If
        objNewRecipient.Resolve
    End With

Exit_MAPIAddRecipient:
    Set objNewRecipient = Nothing
    Exit Sub

Error_MAPIAddRecipient:
    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")
    mobjSession.Logon

exit_sMAPILogon:
    Exit Sub

err_sMAPILogon:
    mboolErr = True
    If Err = CdoE_LOGON_FAILED - mcERR_DECIMAL Then
        MsgBox "Logon Failed", vbCritical + vbOKOnly, "Error"
    ElseIf Err = cERROR_USERCANCEL Then
        MsgBox "Aborting since you pressed cancel.", _
                vbOKOnly + vbInformation, "Operatoin Cancelled!"
    Else
        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...")
    mobjSession.Logoff

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

err_sMAPILogoff:
    Resume exit_sMAPILogoff
End Sub
'**************** Class End  ***********************

Author

Commented:
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.

Author

Commented:
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:
O/S
ACCESS
OUTLOOK
MAPI32

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.

Author

Commented:

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:

Author

Commented:
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?

Author

Commented:
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
        Beep
        MsgBox ("You must select your name from the drop-down list")
        Me.Techie.SetFocus
        Exit Sub
    Else
        strRecipient = Me.Techie.Column(1) & ";"
    End If
End If

' Are we sending email to Sales?

If Me.CheckboxSalesRep Then
    If IsNull(Me.Salesperson) Then
        Beep
        MsgBox ("A sales rep must be selected")
        Me.Salesperson.SetFocus
        Exit Sub
    ElseIf Me.CumSessionTime > Long_session_time Then
        strRecipient = strRecipient & Me.Salesperson.Column(1) & Me.Salesperson.Column(2) & ";" & Tech_VP & ";" & Tech_Mgr & ";"
    Else
        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
        Beep
        MsgBox ("An email address must be entered in 'Email to...'")
        Me.Email_other.SetFocus
        Exit Sub
    Else
        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?

Author

Commented:
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.