Create email from Access on Outlook and make sure that email has beensent using WithEvents method

  • Thread starter mysqlproject@googlemail.com
  • Start date Views 2,646
M

mysqlproject@googlemail.com

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 "arnauddupuis@yahoo.fr", , , "test", "test"

End Sub

Any help would be greatly apreciated
 
K

Ken Slovak - [MVP - Outlook]

Re: Create email from Access on Outlook and make sure that email has been sent using WithEvents method

Your class reference in the code module is out of scope as soon as Sub

test() ends. Declare your Dim myEmail As clsEmailConfo at the module level

so the class reference stays alive.

<mysqlproject@googlemail.com> wrote in message

news:d4a7649f-aa5a-41eb-82d9-737e7cd8d3ec@f21g2000vbf.googlegroups.com...
> 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 "arnauddupuis@yahoo.fr", , , "test", "test"

> End Sub

> Any help would be greatly apreciated
 
B

bm9ubw

Re: Create email from Access on Outlook and make sure that email h

Ken,

Thank you very much for your help on that matter, Looks like it will be

working now.

greatly apreciated.

Arnaud
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Re: Create email from Access on Outlook and make sure that email has been sent using WithEvents method

Damn, I should get used to reading the newsgroups buttom up in order to see

which double posting has been answered yet...

Best regards

Michael Bauer

Am Tue, 14 Apr 2009 09:37:26 -0400 schrieb - :


> Your class reference in the code module is out of scope as soon as Sub
> test() ends. Declare your Dim myEmail As clsEmailConfo at the module level
> so the class reference stays alive.
 

Top