By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Display email in Outlook from Access VBA

P: 4
AS my users do not want to auto-send an email message generated from within Access 2003, I am using .display instead of .send to show the Outlook message (Word is the Outlook word processor). Can anyone tell me how I can detect whether the "Send" button on the email message was clicked on from within Access VBA?
many thanks in advance, Tony
Nov 23 '11 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 446
Hi
I don't understand the '.show instead of.' part of your question.

I believe you are generating some kind of report from within Access then using a command button to email it with the Send Object method
Expand|Select|Wrap|Line Numbers
  1. DocCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
  2.  
Then I get lost but think you are asking whether the 'Send' button in OutLook has been clicked?

Please give more detail
S7
Nov 23 '11 #2

NeoPa
Expert Mod 15k+
P: 31,492
Tony, there are various ways to handle such things so, without your code to guide us (or a very much better description of your situation) we cannot be of much help to you at this time.

In Outlook there should be an object reflecting the email created. With a reference to that object you can check the Date/Time sent. If the email hasn't been sent yet then it won't be set. More detailed assistance depends heavily on details we don't yet have ;-)
Nov 23 '11 #3

P: 4
Thanks both for your replies. My code is like this :-

Expand|Select|Wrap|Line Numbers
  1.         Set olApp = Outlook.Application
  2.  
  3.         Set objMail = olApp.CreateItem(olMailItem)
  4.  
  5.  
  6.         objMail.To = "email address"
  7.  
  8.         objMail.Subject = "My Subject"
  9.         objMail.Body = "Body Text"
  10.         objMail.Display     ' Here Objmail.display instead of objmail.send
At this point an Outlook Email Message is displayed so the the users can then decide to send it by clicking the "Send" button or not send it by closing the message. I would like to be able to determine which action was done so that I can update other indicators to reflect Email Sent & Date & Time sent etc. So, the question I am asking is How do I detect that? NeoPa has now got me thinking whether I can check Date/Time sent.....
I hope this is a bit clearer...
Nov 23 '11 #4

NeoPa
Expert Mod 15k+
P: 31,492
Ah. You don't really mean 'How do I detect that?', but rather 'How do I capture that event?'. There is a very real difference, as when the code gets control back the message is still open. At this point your code will then complete execution to allow the operator again to take control. Until another event triggers your code doesn't know anything and cannot even check any status.

The difficulty here is that, while you can handle events such as closing an Inspector window (Items are shown in Inspector windows btw), within Outlook, I'm not sure how that might be achieved from outside of Outlook.

If it's the same them you'd need to define your MailItem properly as :
Expand|Select|Wrap|Line Numbers
  1. Private WithEvents VBAItem As MailItem
This will give you access to events for this item of both Close and Send.

Expand|Select|Wrap|Line Numbers
  1. Private Sub VBAItem_Close(Cancel As Boolean)
  2. End Sub
  3.  
  4. Private Sub VBAItem_Send(Cancel As Boolean)
  5. End Sub
You can then recognise what happens - all assuming this method of handling events is supported outside of Outlook.

PS. Please see [code] Tags Must be Used & When Posting (VBA or SQL) Code.
Nov 24 '11 #5

P: 4
Thanks NeoPa. It looks like it can be done by using a Class module. I have chosen a slightly simpler method which works fine i.e. having formed the Email detail, I pop up a Msgbox with all details, allowing the users a Yes/No to send or not send.
Now the only little problem I have is the annoying message that "a program is trying to send an e-mail message on your behalf etc...". Do you know of any way I can suppress that message?
thanks again...
Nov 24 '11 #6

NeoPa
Expert Mod 15k+
P: 31,492
'Fraid not Tony. I've read so much about it but I haven't done much in that area and what is out there is frankly so unclear (unless you're deep into the practice of it already) that I would suggest you search around and try the various tips out. There are so many different possible scenarios with such things that you need to try things out for your situation before you go with it.
Nov 24 '11 #7

P: 4
Well I think I have got a workaround... I installed a third party product called Advanced Security for Outlook (an Add-in for Outlook) which lets me "Allow" the send from Access. i.e. it overrides the security warning and remembers the setting. I think this is just relevant to the invocation from within Access, so security in the rest of the system is not compromised
Nov 25 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.