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

  • Thread starter mysqlproject@googlemail.com
  • Start date
Status
Not open for further replies.
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
 
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
 
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
 
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
O Can't create new email or access email acounts Outlook 2003 Using Outlook 1
B Create email from Access on Outlook and make sure that email has b Outlook VBA and Custom Forms 1
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
Wotme create email only data file Using Outlook 1
L Capture email addresses and create a comma separated list Outlook VBA and Custom Forms 5
D Create advanced search (email) via VBA with LONG QUERY (>1024 char) Outlook VBA and Custom Forms 2
D Create new email from the received Email Body with attachment Outlook VBA and Custom Forms 10
P Can I create a Rule that sends me an email when I get a Task? Using Outlook 2
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
D Can Exchange Admin Center create a pst for users email/contacts/calendar? Exchange Server Administration 0
O Multiple email accounts - hesitate to create a new profile Using Outlook 3
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
JackBlack What tools do you use to create the signature for email? Using Outlook 3
Rupert Dragwater How to create a new email with @outlook.com Using Outlook.com accounts in Outlook 32
F Should a new email account also create new contacts Using Outlook 2
R Outlook add-in to create new contact from an email. Using Outlook 0
G How do I create a custom pick list in VB for an outlook automated email? Outlook VBA and Custom Forms 1
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
G Create an Appointment at the Contact's Address From Email Outlook VBA and Custom Forms 0
Diane Poremsky Create a Task from an Email using a Rule Using Outlook 0
Diane Poremsky Create Appointment From Email Automatically Using Outlook 0
B VBA Code to create appointment from email Outlook VBA and Custom Forms 1
Diane Poremsky Create an Outlook appointment from an email message Using Outlook 4
A Create Macro for hyperlink(email) in message body Outlook VBA and Custom Forms 9
Diane Poremsky Create Tasks from Email and move to different Task folders Using Outlook 0
S Create task with email URL instead of attachment Outlook VBA and Custom Forms 4
rc4524 Create auto follow-up reminder email for already sent messages Outlook VBA and Custom Forms 1
anoble1 How to create an email with a link to add a calendar appointment Using Outlook 1
A Create Task from Email and put body & email as attachment into task notes Outlook VBA and Custom Forms 17
D Create Exchange Rule for everyone that highlights an email based on content of subject Exchange Server Administration 1
H Create a Task by Draft-EMail > Task+Reminder not Working Outlook VBA and Custom Forms 1
R Auto-create receipt from email and forward to payer Using Outlook 3
M How to create a document or email template for Opportunities BCM (Business Contact Manager) 0
G Create rule based on image in email body Using Outlook 1
anoble1 Can you create a rule once an email moves to the Vault Inbox to another place? Using Outlook 0
C Visual Basic auto create task from email including attachments Using Outlook 9
2 Create an email brochure - oft file? Using Outlook 3
C Create a rule to only check new content in email - disregard original content Using Outlook 3
K Outlook Cached Mode - can't create rules to move email to another mailbox Using Outlook 2
M Create Rule to BCC email? Using Outlook 2
O Outlook 2010 - How to create custom Group By Arrangements for email Using Outlook 3
K Create individualized employee email messages Outlook VBA and Custom Forms 1
P How do I create a macro to add contacts from email messages? Outlook VBA and Custom Forms 1
S How to Create Buttons in email body / toolbar Outlook VBA and Custom Forms 3
S HELP: Create Buttons in Outlook Email body or Tool bar Outlook VBA and Custom Forms 1
T How to create an email template that generates a text email Outlook VBA and Custom Forms 1
S Create Auto eMail with Web table Content Outlook VBA and Custom Forms 1
B Modify VBA to create a RULE to block multiple messages Outlook VBA and Custom Forms 0
J Want to create a button on the nav bar (module add-in) to run code Outlook VBA and Custom Forms 2

Similar threads

Back
Top