Problem Emailing From Access

[JohnClingeleffer]

I use VBA in MS Access to send e-mails. Relevant code for error is:

Set oItem= objOL.CreateItem(0) ' Const olMailItem
With oItem
errMsg = "Error populating Microsoft Outlook mail item components."
.To = Trim(emAddress)
.Subject = emSubject
.Body = MergeBody(PIN, msgBody)
If Not Isnull(attachmentPath) then
    .attachments.Add attachmentPath
End if
errMsg = "Error sending Microsoft Outlook mail item."
.Send 
End With

There code at the start of the function to open Outlook if it is not open.

Code runs fine under Windows XP/Access 2003/Outlook 2003 whether or not Outlook is initially open and whether or not there is an attachment.
Under Windows Vista/Access 2007/Outlook 2007 and Windows 7/Access 2010/Outlook 2010 the code runs fine with or without an attachment if Outlook is initially open. If Outlook is not open, code works fine if there is an attachment but fails if there is no attachment.

[NeoPa]

Is there a line that it fails on with an associated error message?

Welcome to Bytes!

[patjones]

Where is attachmentPath coming from? Is it possible that it is not really null, but perhaps a zero-length string, and still executing that .Add line?

Pat

[JohnClingeleffer]

AttachmentPath never contains a zero length string. Code fails on the .Send line with error 287 "Applicatio n-defined or object-defined error"

[patjones]

I don't know off the top of my head what would cause that error, and so I'd need to think about it some more.

In the meantime, I posted the basic code that I use to email attachments; I work in Access 2007 only, so at least it should function for you in 2007. As for Access 2010...you're way ahead of most of us, but you can try it.

The "str" variables are just various pieces of information that I set from elsewhere in the code and which you can modify appropriately. Also, remember to set the reference to the Microsoft Outlook library in Tools > References...

Pat

Dim olApp As New Outlook.Application, olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
 
With olMail
    .To = strSendTo
    .Subject = strSubject
    .ReadReceiptRequested = False
    .Body = strBody
    .Attachments.Add strAttachFile
 
    .Send
End With
 
Set olApp = Nothing
Set olMail = Nothing

[JohnClingeleffer]

That looks pretty similar to my code. The really bizarre thing is that the code works fine with or without an attachment if Outlook was already running. I test with the following code

'see if Outlook is running
errMsg = "Error checking if Microsoft Outlook is running."
Set objOL = GetObject(, "Outlook.Application")
'non zero error indicates Outlook is not running so start it
If Err <> 0 Then
    'Outlook wasn't running, so start it
     errMsg = "Error starting Microsoft Outlook."
     Set objOL = CreateObject("Outlook.Application")
     outlookForceStarted = True
End If

But if I start Outlook with the previous code, the .Send line fails only if there is no attachment.

[patjones]

That looks pretty similar to my code. The really bizarre thing is that the code works fine with or without an attachment if Outlook was already running. I test with the following code

'see if Outlook is running
errMsg = "Error checking if Microsoft Outlook is running."
Set objOL = GetObject(, "Outlook.Application")
'non zero error indicates Outlook is not running so start it
If Err <> 0 Then
    'Outlook wasn't running, so start it
     errMsg = "Error starting Microsoft Outlook."
     Set objOL = CreateObject("Outlook.Application")
     outlookForceStarted = True
End If

But if I start Outlook with the previous code, the .Send line fails only if there is no attachment.

That's what prompted me to ask whether or not there is always a value for the attachment path. My concern is that the .Add line is always being executed, regardless of whether you think the If test should work or not.

But my concern would not explain why it works in some circumstances (Outlook already running) and versions of Access and not others.

Pat

[JohnClingeleffer]

If I comment out

'If Not Isnull(attachmentPath) then 
   ' .attachments.Add attachmentPath 
'End if

It fails if Outlook is not initially open and runs fine if Outlook is initially open.

[NeoPa]

This is beyond my scope I'm afraid. I was hoping the error message may help some, but being on the .send line is a bit like saying Any one of the preceeding lines could be the problem, and the error message is Microsoft's way of saying You have something wrong with your object. It could hardly be less specific if they tried.

I'm afraid I only have Access 2003 too, and nothing rang any bells. I'll leave you in Pat's capable hands.

[patjones]

That looks pretty similar to my code. The really bizarre thing is that the code works fine with or without an attachment if Outlook was already running. I test with the following code

'see if Outlook is running
errMsg = "Error checking if Microsoft Outlook is running."
Set objOL = GetObject(, "Outlook.Application")
'non zero error indicates Outlook is not running so start it
If Err <> 0 Then
    'Outlook wasn't running, so start it
     errMsg = "Error starting Microsoft Outlook."
     Set objOL = CreateObject("Outlook.Application")
     outlookForceStarted = True
End If

But if I start Outlook with the previous code, the .Send line fails only if there is no attachment.

You're absolutely right, it is basically the same code. In thinking about it some more, I realized that I've used this code only at my job - under circumstances where Outlook is always open, and with an attachment.

I don't run Outlook on my home machines at this point, so it's going to be hard for me to test anything out, but I'll see if I can do something today or tomorrow.

Pat

[patjones]

Good morning,

I was not able to perform the test that I wanted on my home machine. I did just try it here at work and got the same results that you got...the email sends just fine when Outlook is open (attachment or not), and when Outlook is closed (with attachment). It fails on the .Send line when Outlook is closed and there is no attachment.

It is significant that I'm able to duplicate the problem, because now it allows me to test out ideas without having to ask you to try them. I'll let you know what I come up with.

Pat

[jimatqsi]

Can you query the name of the attached file? It would be interesting to ask, "What file is attached to this email" before sending it?" ... maybe the program thinks there is a file attached when there is not, in some configurations there is a phantom file name for the attachment, which of course cannot be attached, so the .send fails.

Still would beg the question, why and how to fix, but would make interesting inquiry.

Jim

[patjones]

I looked high and low for a solution to this issue. When I went to the MS support page and used their code verbatim, I encountered the same problem.

As Jim says, the best case scenario will be for you to check and see if there's an attachment first, or if you really want to be on the safe side then force Outlook to open so there are no issues regardless of whether something is attached.

Definitely stay subscribed to the thread in case there are any updates to it.

Pat