Create email from Access on Outlook and make sure that email has b

N

nono

Hi,
I'm trying to send an email confirmation from Access using VBA. I then want
to make sure that the email has been sent (ie user didn't close the email
window without sending the email). In order to achieve that, I'm using the
WithEvent method in a class module.
I use Office 2003, with Word as the email editor of Outlook. Not being a
specialist, I don't understand why my program fail to catch events from
Outlook. Bellow you will find the code I'm using, please let me know if you
can help:

All code is located in the my Access Project:
In the Class Module names "clsEmailConfo", I have the following code:

Option Compare Database
Option Explicit

Public WithEvents objOutlook As Outlook.Application Public WithEvents
objOutlookMsg As Outlook.MailItem



Private Sub Class_Initialize()

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


End Sub


Sub sendEmailConfo(Optional myTo As String, Optional myBcc As String,
Optional myCC As String, Optional mySubject As String, Optional myBody As
String) With objOutlookMsg
.To = myTo
.CC = myCC
.BCC = myBcc
'.Attachments.Add (mypathname)
.Subject = "test"

.BodyFormat = olFormatHTML

.HTMLBody = myBody



.Display

End With
End Sub


Private Sub objOutlook_ItemSend(ByVal Item As Object, Cancel As
Boolean)

MsgBox ("TEST")

End Sub

Private Sub objOutlookMsg_Send(Cancel As Boolean) MsgBox ("TEST") End Sub


-------------------------------------------------------------------------------------------

In a module, I have the follwoing code:

Sub test()
Dim myEmail As clsEmailConfo
Set myEmail = New clsEmailConfo

myEmail.sendEmailConfo "(e-mail address removed)", , , "test", "test"


End Sub


Any help would be greatly apreciated
 
M

Michael Bauer [MVP - Outlook]

The problem is that you have declared the variable for the class module
within the function. The code runs through the function and terminates the
class module before the user hits Send.

In your example you could probably do this:

Public myEmail as clsEmailConfo
Sub Test()
Set myEmail...
End Sub

In the class module you can handle everything that closes the mail window,
that is whenever the windows is being closed set the public variable
myEmail=Nothing.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 14 Apr 2009 04:45:01 -0700 schrieb nono:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top