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

Status
Not open for further replies.
B

bm9ubw

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
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

Am Tue, 14 Apr 2009 04:45:01 -0700 schrieb 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 "arnauddupuis@yahoo.fr", , , "test", "test"

> End Sub

> Any help would be greatly apreciated
 
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
M Create email from Access on Outlook and make sure that email has beensent using WithEvents method Outlook VBA and Custom Forms 3
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
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 0
J How to create a drop down user defined field that will appear on an inbox view Outlook VBA and Custom Forms 8
Commodore Any way to create "from-only" account on Outlook 2021? Using Outlook 1
N Can't create NEW GROUP and add/remove a member from existing Group in Outlook Using Outlook 1

Similar threads

Top