Using Outlook Object to Send Email from Access

[PurpleUnicorn]

I have an application that loops through a recordset (containing email addresses and filenames) and calls a function that sends email using the Outlook object. At the end of my function I quit Outlook and set my outlook variable = nothing.

If I have my Outlook set up NOT to automatically send email, I do not have a problem. However, if it is set up to automatically send email, multiple instances of Outlook get created and have to be deleted from task manager.

How can I prevent this from happening?

Thanks

 

[origapizni]

In the Microsoft: Access Modules (VBA Coding) forum

please see faq705-1634

 

[PurpleUnicorn]

Thanks for the reply. I am using something very similar to the code in the FAQ. If my application sends 5 emails, when I open task manager when my program ends, there are 5 instances of outlook that I must delete. I have included my code - maybe you will see what the problem is.

I loop through a recordset getting the path/file(vAttach), email address(vRecip),subject(vsub) and message(vmsg). All items are stored in a table. Then for each record the following line of code runs:

Call SendEmail(vPath & vFile, vEmail, vSubject, vmessage)

Function SendEmail(vAttach As String, vRecip As String, vSub As String, vMsg As String)
On Error GoTo Err_SendEmail

Dim objOutlook As Object
Dim objMessage As Object

Set objOutlook = CreateObject("Outlook.Application&quot
Set objMessage = objOutlook.CreateItem(0)
With objMessage
.SUBJECT = vSub
.Body = vMsg
.Attachments.Add vAttach
.To = vRecip
.Send
End With

Set objMessage = Nothing
objOutlook.Quit
Set objOutlook = Nothing

Exit_SendEmail:
Exit Function

Err_SendEmail:
MsgBox err.number & " " & Err.Description
Resume Exit_SendEmail

End Function

 

[origapizni]

Looks like it should work, but since it doesn't you might try reworking your logic to move the "Set objOutlook" statement somewhere before you start looping, therefore only opening Outlook once. (That's the way the code in the FAQ example is written.)

For what it's worth, here's some interesting code examples. Not directly related to your question, but interesting anyways.

http://www.microsoft.com/AccessDev/articles/outprog.htm

http://support.microsoft.com/default.aspx?scid=KB;en-us;q209948

http://www.microsoft.com/office/previous/outlook/SuprEasy.asp

 

[MarrowGenx]

I am currently using a similar module, but I am not using the objOutlook.Quit line. Also, I have found the need to completely rewrite the code (new module/sub) when this thing happens. I dont really change the code, just rewrite it and the problem goes away. Hope this helps.

Marrow

 

[asjeff]

Hi PurpleUnicorn

To avoid creating multiple instances use GetObject rather that CreateObject, and then trap the error if there isn't an instance already open. eg:

on error goto errorhandler:
Set objOutlook = GetObject(, "Outlook.Application&quot

errorhandler:
'if no instance open then an error number will be raised.
'trap this error number

if err.number = XXX then
Set objOutlook = CreateObject("Outlook.Application&quot
err.clear
resume
End If


This is all from the top of my head and I haven't tested it with Outlook but I use this code with all other VBA applications so it should work

Hope it helps....

Asjeff

 

[PurpleUnicorn]

Thank you for all of the responses. Unfortunately, I am still having the same problem. My application is creating the emails and placing them in the Outbox. For each email that gets created, there is an instance of Outlook.exe in the task manager - even if outlook is closed. This only occurs if Outlook is closed prior to running the program.

I am using Outlook 2002 and have downloaded a program that handles the security feature preventing other applications from automatically sending email - the program automatically answers yes to the message prompt.

Somehow, I think this all just complicates the process. I have told my client that they must open outlook prior to running the application - or check the task manager when the program is completed.

Thanks again