EMail Send failure from Access

[Peter Hibbs]

Hi, first post to this NG so feel free to point me in the right
direction if this question is not appropriate.

Using Access 2003 (SP3) + Outlook 2003 (SP3)

I am using the following function to send an email from Access via
Outlook.
--------------------------------------------------------------------------------
Public Function SendMail(strRecipients As String, strSubject As
String, strBody As String) As String

'Entry (strRecipients) = Semicolon delimited string of recipients.
' (strSubject) = Required. Message subject.
' (strBody) = Optional. Body of the message.

Dim myObject As Object, myItem As Object
Dim vCount As Long

On Error GoTo ProcError

Set myObject = CreateObject("Outlook.Application")
Set myItem = myObject.CreateItem(0)
With myItem
.Subject = strSubject
.To = strRecipients

If Len(Trim(strBody)) > 0 Then
.Body = strBody
End If
.Display
End With

ExitProc:
Set myItem = Nothing
Set myObject = Nothing
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
SendMail = "A problem was encountered attempting to automate
Outlook."
Resume ExitProc
Resume

End Function
--------------------------------------------------------------------------------

I am calling it like this from a button on a form :-
--------------------------------------------------------------------------------
Private Sub btnEMail_Click()

Dim vDummy As String

vDummy = SendMail("EMail Address here", "Subject", "Test")

End Sub
--------------------------------------------------------------------------------
The "EMail Address here" string is a valid email address.

The problem is that when I run the code, Outlook opens correctly
showing the email address, subject and body text but if I then Send
the email (or cancel the email by exiting Outlook) I get the following
error message on the .Display line.

Run-time error '-2147417851 (80010105) ':
Method 'Display' of object '_MailItem.' failed

Also Outlook is left running in the background, I have to close it
using Windows Task Manager.

The code Compiles with no errors and Compact & Repair and Decompiling
make no difference. I'm sure this worked OK a while ago (before or
loaded SP3) and I think the same problem occurs in Access 2000 (I'm
still checking that one as Outlook 2003 is being used as the email
client). References look OK, Outlook 11.0 Object Library is ticked
although I don't think it is necessary for this code.

If I open Outlook first and then send the email everything works OK,
the email gets sent and no error messages. It seems that Outlook does
not like the .Display command when it is not already loaded.

I have also tried using Redemption but get the same error message
(although I'm a bit unsure about the code here so that *may* be a bit
misleading).

Access NG have not come up with any clear solution, as yet.

Has anyone else come across the problem, could SP3 have broken
Outlook.

TIA

Peter Hibbs.

 

[Michael Bauer [MVP - Outlook]]

Does it work if you add this?

Dim Ns as Outlook.Namespace
Set myObject = CreateObject("Outlook.Application")
Set Ns=myObject.GetNamespace("Mapi")

--
Best regards
Michael Bauer - MVP Outlook
Use Outlook Categories? This is Your Tool:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Tue, 12 Feb 2008 16:14:31 +0000 schrieb Peter Hibbs:

 

[Peter Hibbs]

Michael,

Unfortunately not, the same error occurs. The code at the start of the
function looks like this now, I assume that is what you intended.
---------------------------------------------------------------------------
Public Function SendMail(strRecipients As String, strSubject As
String, strBody As String) As String

Dim myObject As Object, myItem As Object
Dim vCount As Long
Dim Ns As Outlook.Namespace

On Error GoTo ProcError

Set myObject = CreateObject("Outlook.Application")
Set Ns = myObject.GetNamespace("Mapi")
Set myItem = myObject.CreateItem(0)
.... continues as shown in original post.

 

[Michael Bauer [MVP - Outlook]]

Peter, I cannot reproduce that error. If I add a call to Logon:

Set Ns = myObject.GetNamespace("Mapi")
Ns.Logon

everything works fine.

--
Best regards
Michael Bauer - MVP Outlook
Use Outlook Categories? This is Your Tool:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>


Am Wed, 13 Feb 2008 12:10:30 +0000 schrieb Peter Hibbs:

 

[Peter Hibbs]

Michael,

Brilliant, that works perfectly. I still need to test it with multiple
recipients and attachments but I guess that should still work OK.

Not sure why adding the Logon function should make all the difference.

Hopefully it will work OK on my client's system (Access/Outlook 2000)
which I will check next week.

Anyway, thanks again.

Peter Hibbs.