A Microsoft Outlook email forum. Outlook Banter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Outlook Banter forum » Microsoft Outlook Email Newsgroups » Outlook and VBA
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Search this Thread Display Modes
  #1  
Old April 14th 09, 12:45 PM posted to microsoft.public.outlook.program_vba
nono[_2_]
external usenet poster
 
Posts: 2
Default Create email from Access on Outlook and make sure that email has b

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 ", , , "test", "test"


End Sub


Any help would be greatly apreciated
Ads
  #2  
Old April 14th 09, 06:50 PM posted to microsoft.public.outlook.program_vba
Michael Bauer [MVP - Outlook]
external usenet poster
 
Posts: 1,885
Default Create email from Access on Outlook and make sure that email has b



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:

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 ", , , "test", "test"


End Sub


Any help would be greatly apreciated

 




Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create email from Access on Outlook and make sure that email has beensent using WithEvents method [email protected] Outlook and VBA 3 April 14th 09 06:53 PM
2 simple macros - create task from email and move email to folder [email protected] Outlook and VBA 5 February 4th 07 10:57 AM
Have all email addresses in one email using Outlook called from Access 03 Ultraviolet47 Outlook - General Queries 0 November 17th 06 07:45 PM
can't make outlook express default email app when send page by email merdrum Outlook Express 3 January 16th 06 03:28 AM
Hotmail no longer allows email access via Outlook Express for free email accounts? Christina Outlook Express 3 January 11th 06 05:49 AM


All times are GMT +1. The time now is 12:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.Search Engine Friendly URLs by vBSEO 2.4.0
Copyright ©2004-2024 Outlook Banter.
The comments are property of their posters.