SendObject bug 3

[RYankowitz]

I've recently run into a problem with using SendObject to email Access data. A client who has been using my system on 1/2 dozen networked machines recently found that three of the machines no longer successfully complete the email process. The SendObject method appears to proceed, but Outlook does not recieve (or send) the message.

A search of the Microsoft Knowledge Base came up with this article: Q260819 - ACC2000: SendObject Method Fails in Access 2000.

To summarize:
SYMPTOMS
The SendObject method silently fails. That is, the message is not sent, and you do not receive any error message or notification that the message was not sent.
CAUSE
There are too many characters in the message. This behavior has been documented with messages that contain between 98 and 2268 characters.

Article Q161088 - Using Automation to Send a Microsoft Outlook Message offers a workaround, but it is limited in that the client must use Outlook as his email application.

Here are my questions (finally!):
1. Has anyone had any experience with this bug?
2. Since the program has been working on several systems for many months, I am assuming the bug is triggered by some specific configuration. Microsoft appears not to have a handle on it. Does anyone else?
3. Is there a better way to set up email functionality which will be independent of the mail application?

 

[dkaplan]

I realize it's been over a year since your original post, but I've just tripped over this bug myself and I am wondering if your questions ever got answered. In my experience, the error seems not to occur if the body of the message is <= 100 characters. (The subject line also seems to count against this limit.)

Other postings have given me the impression that the triggering configuration may vary between machines, so I’m reluctant to trust 100 byte limit, even when things appear to work at my end.

Anything new you've discovered since you posting would be appreciated

Dennis

 

[RYankowitz]

Dennis,

I never did receive any answers to my questions. The Microsoft Knowledge Base still shows the bug as current.

My solution was to abandon the SendObject method and use Outlook Automation. This has the disadvantage of requiring the use of Outlook (which wasn't a problem for me), but it has much more power and flexibility (such as the ability to send attachments).

Use the following code:

Sub SendMessage(DisplayMsg As Boolean, Recipients As String, Subject As String, Message As String, Optional AttachmentPath)
On Error GoTo Err_SendMessage

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(&quot;Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(Recipients)
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
'Set objOutlookRecip = .Recipients.Add(&quot;Michael Suyama&quot
'objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.
'Set objOutlookRecip = .Recipients.Add(&quot;Andrew Fuller&quot
'objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = Subject
.Body = Message
'.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
.Save
.Send
End If
End With
Set objOutlook = Nothing

Exit_SendMessage:
Exit Sub

Err_SendMessage:
MsgBox Error$
Resume Exit_SendMessage

End Sub

This code was copied and modified from the KB article Q209948, HOW TO: Use Automation to Send a Microsoft Outlook Message.

I've been using it for the past year with no problems.

Regards,

Bob

 

[fsweb2002]

Thanks RYankowitz

I finally found out what I just spent over 2 days working on and could work out why!

I´VE checked your code right now and it WORKS GREAT!

Thanks for enlighting us!

regards

 

[leclair]

hello,
I am failry new to VBA and need a little help with the Auotmated Outlook code that was included in this thread. Anyone willing to help?

 

[RYankowitz]

Happy to help, leclair. Fire away with your questions...

 

[leclair]

can we chat---via maybe MSN Messager or another way---much more real time and more efficient

thanks

 

[leclair]

how do I integrate the code you provided in place of a DoCmd.SendObject line

 

[RYankowitz]

Easy as falling off a (really high) cliff.

Just follow these steps:

1. In your database, press F11 to open the Database window. Click the Modules label, select your Main Module, and click Design. Alternatively, you can create or use any module of your choice. Click New and a new module window will pop-up. When you add code to this module and save it, you may give it any desired name.
2. Cut the sub-program code below called SendMessage and paste it into your module. (BTW, I've added an additional option that allows you to send HTML encoded emails, so I'm including a new copy of this sub-program here.)
3. You can execute this directly, but a better way is to use EMailMessage below as a wrapper sub-program. Doing this has two advantages-
a. It simplifies life for you by hiding the options you probably won't often use. You only need concern yourself with the address, subject line, and body text of the message. Of course, if you want to use the other options (CC, BCC, etc.) you can always modify this.
b. There is a curious error sometimes created when you generate but do not actually send the email. After the user cancels the send, Access generates an Error 2501. Sometimes. The error trapping code here displays a friendlier message than Access' usual cryptic method.
To use this, cut and paste the code into the module of your choice, as in step #1, which will make it available from anywhere in the database. I've included an example, Email_Message_Click, you can build from. Let's say you've got a form on which you've placed a button, EMail Message. Also, you've got three text boxes on the form, txtEMailAddress, txtSubject, and txtMessageBody. Use the code below in the Click event of the button.
When you click the button, Outlook will load and open with a filled-out email, ready for editing and sending.

That's it. I'm not sure of you level of expertise in any of these steps, so if you have any problems with them, let me know and I'll fill-in with more detail.

Good luck,

Bob
__________________________________________________________________

Sub SendMessage(DisplayMsg As Boolean, Recipients As String, Subject As String, Message As String, Optional AttachmentPath, Optional UseHTML As Boolean)
On Error GoTo Err_SendMessage

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(&quot;Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(Recipients)
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message, if desired
'Set objOutlookRecip = .Recipients.Add(&quot;CC Name Here&quot
'objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message, if desired
'Set objOutlookRecip = .Recipients.Add(&quot;BCC Name Here&quot
'objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = Subject

If UseHTML Then
.HTMLBody = Message 'tell Outlook to encode in HTML
Else
.Body = Message 'use plain text
End If

'.Importance = olImportanceHigh

' Add attachments to the message, if desired
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
.Save
.Send
End If
End With
Set objOutlook = Nothing

Exit_SendMessage:
Exit Sub

Err_SendMessage:
MsgBox Error$
Resume Exit_SendMessage

End Sub
__________________________________________________________________

Private Sub EMailMessage(EMailAddress As String, Subject As String, MessageBody As String)
On Error GoTo Err_EMailMessage

If EMailAddress=&quot;&quot; Then
EMailAddress = &quot;Replace.This.Address!&quot;
End If

SendMessage True, EMailAddress, Subject, MessageBody

Exit_EMailMessage:
Exit Sub

Err_EMailMessage:
If Err = 2501 Then
Msg = &quot;The Message was not sent!&quot;
MsgBox Msg
Else
MsgBox Error$
End If
Resume Exit_EMailMessage

End Sub
__________________________________________________________________

Private Sub Email_Message_Click()
On Error GoTo Err_Email_Message_Click

EMailMessage txtEMailAddress, txtSubject, txtMessageBody

Exit_Email_Message_Click:
Exit Sub

Err_Email_Message_Click:
MsgBox Err.Description
Resume Exit_Email_Message_Click

End Sub

 

[thojohp]

I began using this code, instead of the SendObject code, and am curious if it is still possible to send reports via this code? Any tips on how this would be done?

Thanks!

John

 

[mp9]

Export the report to a file, send the mail with the specified report filename as an attachment, then delete the exported file.

There's good alternative Outlook-based mail sending code at

http://www22.brinnkster.com/accessory/modules/005.shtml

which additionally lets you set message importance, voting buttons, the &quot;From&quot; mail address, and more.

HTH.

http://www22.brinkster.com/accessory/