EMail via Outlook Failure

[Peter Hibbs]

Access 2003 SP3 + Outlook 2003

I am using the following function kindly supplied by Tom Wickerath a
couple of years ago.

--------------------------------------------------------------------------------
Public Function SendMail(strRecipients As String, strSubject As
String, strBody As String) As String

'Written by Tom Wickerath, May 7, 2006.
'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 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.

Has anyone else come across the problem.

TIA

Peter Hibbs.

 

[Arvin Meyer [MVP]]

Peter,

The code looks fine. I'm not sure what the index of olMailitem is so you
might want to change:

Set myItem = myObject.CreateItem(0)
to:
Set myItem = myObject.CreateItem(olMailitem)

If that doesn't work, set a breakpoint and try stepping through it, and see
exactly where it's breaking.

 

[Peter Hibbs]

Arvin,

Changing the 0 to olMailItem made no difference (olMailItem is set to
0 anyway). Stepping through the code it fails at the .Display command.

This problem also seems to be affecting my other databases so I'm
wondering if something in Outlook is broken. Any thoughts, maybe I
will need to consult the Outlook NG.

Peter.

 

[Arvin Meyer [MVP]]

Hi Peter,

It may be something in Outlook, try commenting out .Display and the mail
should go out with a .Send command, never displaying the Outlook app.

One other thing, see if there's any difference if Outlooks's open or closed.

 

[Peter Hibbs]

Arvin,

Interesting.

If I Rem out the .Display line, nothing happens. No error message,
Outlook does not display (of course) and the email is not sent.

If I replace the .Display line with .Send then Outlook does not
display (logical) but the email does get sent (after the usual
warnings about some app trying to send an email). OK, but the user may
want to amend the default Subject or Body text before sending.

If I open Outlook first and then send an email from Access, everything
seems to work correctly, that is, no error messages and the email can
be sent or cancelled.

It would seem that Outlook does not like the .Display command for some
reason. I don't suppose SP3 has played havoc with Outlook like it has
with Access? I can't see any mention of problems on the Internet.

Any more thoughts. It's very late here, will have to come back to this
tomorrow. Thanks for the ideas so far.

Peter.

 

[Arvin Meyer [MVP]]

strSubject and strBody can be amended from the form wjich is running the
code, either from a bound or an unbound textbox. My own practice is to do
just that. You can kill the security prompts by using Outlook Redemption.

http://www.dimastr.com/redemption/

 

[Peter Hibbs]

That is an option, I agree, although I'm still a bit concerned as to
why the code does not work.

I looked at the Redemption site and it would seem to be a viable
alternative to using Outlook direct. However I am struggling to get it
to work, the examples shown just throw up Compile errors. I will
perservere for the moment and maybe start another thread if I can't
get it working (unless you have any simple code that you would like to
share with us).

Peter.

 

[Peter Hibbs]

If anyone's interested I fixed the problem, with the help of Michael
Bauer in the Outlook NG, by adding the Ns object and two extra lines
to Logon. No idea why it works but it seems to, so far. The function
now looks like this :-

Public Function SendMail(strRecipients As String, strSubject As
String, strBody As String) As String

'Written by Tom Wickerath, May 7, 2006.
'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 Ns As Outlook.Namespace

On Error GoTo ProcError

Set myObject = CreateObject("Outlook.Application")
Set Ns = myObject.GetNamespace("Mapi")
Ns.Logon
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

Peter Hibbs.

 

[Arvin Meyer [MVP]]

If anyone's interested I fixed the problem, with the help of Michael
Bauer in the Outlook NG, by adding the Ns object and two extra lines
to Logon. No idea why it works but it seems to, so far. The function
now looks like this :-

I suspect a patch that you installed broke the existing functionality. Using
a Namespace is a .NET construction, not a VBA one.