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

Email Automation from Access Problem

P: 206
Hi all,

I am trying to add a feature to our database that automates sending emails.
When entering a new order, I want the option to send an email to the company the order came from, attaching a copy of our work order.

The file that I want to attach is a rich text document created by Access and stored on our server. The creation of this document is not the problem.

The problem is getting the email option to work. It does not seem to work. In fact, it just does nothing at all, not even an error message.

The following code is just part of the VBA for an On Click event for a command button on my Add New Orders form. The button click does several things, all of which work fine. I tried to add the email option to the list of events that occur on that button click.

Expand|Select|Wrap|Line Numbers
  1. '   Email New Order to Recipient
  3. '   Yes No Box
  4.     Msg = "Do you want to EMAIL a confirmation of this order?"    ' Define message.
  5.     Style = vbYesNo   ' Define buttons.
  6.     Title = "Email Confirmation"    ' Define title.
  7.     Response = MsgBox(Msg, Style, Title)
  8. If Response = vbYes Then    ' User chose Yes
  10.     Dim objOutlook As Outlook.Application
  11.     Dim objOutlookMsg As Outlook.MailItem
  12.     Dim objOutlookRecip As Outlook.Recipient
  13.     Dim objOutlookAttach As Outlook.Attachment
  14.     Dim stRecip As String
  16.     If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
  17.     stRecip = ""
  18.     Else
  19.     stRecip = DLookup("email", "cust", "[cusname]='" & Me!ORDER & "'")
  20.     End If
  22.    ' Create the Outlook session.
  23.    Set objOutlook = CreateObject("Outlook.Application")
  24.    ' Create the message.
  25.    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  26.    With objOutlookMsg
  27.       ' Add the To recipient(s) to the message.
  28.      Set objOutlookRecip = .Recipients.Add(stRecip)
  29.       objOutlookRecip.Type = olTo
  30.       ' Set the Subject and Body of the message.
  31.       .Subject = "Survey Order Receipt Confirmation (" & Me.CASE_N & ")"
  32.       .Body = "This email is to confirm that we have entered your recent survey request into our system. Please review the attached work order, and let us know if you find any errors. Thank you!" & vbCrLf & vbCrLf
  33.       ' Add attachments to the message.
  34.       If Not IsMissing("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf") Then
  35.          Set objOutlookAttach = .Attachments.Add("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf")
  36.       End If
  37.       ' Display email
  38.       objOutlookMsg.Display
  40.    End With
  41.    Set objOutlookMsg = Nothing
  42.    Set objOutlook = Nothing
  43.    Set objOutlookRecip = Nothing
  44.    Set objOutlookAttach = Nothing
  46. End If
Let me know if you will need to see the entire code for that button click.

"SC_NEW" is control source table for the "Add New Orders" form
"ORDER" is the field on that table where we enter the company that sent us an order
"CASE_N" is the field on that table where we enter the ordering company's case number.
"FILE_NO" is a field for an automatically generated File Number (not the same as the case number)

"cust" is a table where we store contact information for regular customers
"cusname" is the field that stores the company's name
"email" is the field (hyperlink field) that stores an email address for that company (is the fact that it's a hyperlink field the problem, maybe?)

The File Number has already been generated, and the files on the server have already been created as a part of the On Click event, prior to reaching this part of the code.

I think I have covered everything. Let me know if I forgot to define anything.

Note: I want the email to open up for editing before sending.

Oct 30 '08 #1
Share this Question
Share on Google+
28 Replies

P: 93

I normally put STOP before the code I want to check then run it and use F8 to check the code step by step in majority of cases it resolves my issues straight away.

Hope it helps
Oct 31 '08 #2

Expert Mod 15k+
P: 31,560
I don't have much experience with Outlook automation, but I can give links to some concepts. Debugging through the code to enable you to tell us what is not working does sound like a good idea. This level of code is not so easy to look through and spot problems, especially without specific experience in the relevant area.

When debugging Application Automation it helps to set the Application object to visible first. It starts off invisible but adding this code after line #24 should help :
Expand|Select|Wrap|Line Numbers
  1. objOutlook.Visible = True
Helpful resources :
(Debugging in VBA).
(Application Automation).
Oct 31 '08 #3

P: 6
Hi there,

I believe - at least part of your problem - may reside in the DLOOKUP ...

stRecip = DLookup("email", "cust", "[cusname]='" & Me!ORDER & "'")

Where "email" should be in brackets "[email]" ...

You may also want to be very careful in name comparisons and use the Trim$ to be certain niether the server name or form name contains any extra spaces.

(The rest looks good to me ... )

Hope that helps a bit.

Oct 31 '08 #4

P: 206
Thanks for the help. I changed the "email" to "[email]". I now have it working, but only when an email address is in the "email" field.

I tried to write the code to also pop up an outgoing email, but leave the To: field blank, when there is nothing in the "email" field. That still isn't working.

The way I tried to do that can be seen in the code I posted (lines 16 & 17). I tried to make the string value be "" when the "email" field is blank. That is apparently not the trick. When the "email" field is blank, all it does is... nothing at all. In fact it interrupts and ends all the code running from the OnClick event.

Do you have a suggestion as to how I might accomplish this? Maybe some kind of Nz or IsNull? Again I do want it to open up an outgoing message, and just leave the To: blank if the "email" field is blank.

Nov 1 '08 #5

P: 206
Hi again,

I have one remaining issue with the email automation thing.
When Access tries to send an email through outlook, the Outlook warning appears saying "another program is trying to send an email, do you want to allow it?" or something to that effect. Everything I've read says that there is no way to eliminate that message.

The problem is that if I click on "no" (don't allow), the remaining code that is supposed to run in my form after the email automation just stops. It does not complete the rest of the code. Apparently clicking "no" tricks Access into thinking an error occurred.

I need for it to continue running the code, regardless of if the Send Email portion of it is successful or not. Is there a way to do this?
Nov 18 '08 #6

Expert Mod 15k+
P: 31,560
Have you tried any error handling commands like :
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
Nov 19 '08 #7

P: 206
Have you tried any error handling commands like :
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
No, I hadn't done that. Shows how much I know!

I had tried something similar, but in the form of On Error Goto.....
with the the Goto referencing to the next step.

One question: If the beginning of the code that runs (it does several things back to back) has an On Error Goto command that sends it to the end of the code, will putting On Error Resume Next in the email portion of the code supercede the original On Error command (assuming that no errors occured prior)?

This is the code from the email portion on to the end.
Is my placement correct?
Expand|Select|Wrap|Line Numbers
  1. '   Email New Order to Recipient
  3. On Error Resume Next
  5. '   Yes No Box
  6.     Msg = "Do you want to EMAIL a confirmation of this order?"    ' Define message.
  7.     Style = vbYesNo   ' Define buttons.
  8.     Title = "Email Confirmation"    ' Define title.
  9.     Response = MsgBox(Msg, Style, Title)
  10. If Response = vbYes Then    ' User chose Yes
  12.     Dim objOutlook As Outlook.Application
  13.     Dim objOutlookMsg As Outlook.MailItem
  14.     Dim objOutlookRecip As Outlook.Recipient
  15.     Dim objOutlookAttach As Outlook.Attachment
  16.     Dim stRecip As String
  18.     If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
  19.     stRecip = "email_address_unknown"
  20.     Else
  21.     stRecip = DLookup("[email]", "cust", "[cusname]='" & Me!ORDER & "'")
  22.     End If
  24.    ' Create the Outlook session.
  25.    Set objOutlook = CreateObject("Outlook.Application")
  26.    ' Create the message.
  27.    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  28.    With objOutlookMsg
  29.       ' Add the To recipient(s) to the message.
  30.      Set objOutlookRecip = .Recipients.Add(stRecip)
  31.       objOutlookRecip.Type = olTo
  32.       ' Set the Subject and Body of the message.
  33.       .Subject = "Survey Order Receipt Confirmation (" & Me.CASENO & ")"
  34.       .Body = "***Confirmation of Receipt of New Survey Request***" & Chr(13) & Chr(13) & "This email is to notify you that we have created a work order for your recent survey request for " & Me.ADDRESS & "." & Chr(13) & "Our Job Number for this survey is " & Me.FILE_NO & "." & Chr(13) & "Please review the attached copy of our work order and let us know if you find any errors or have any questions." & Chr(13) & "Thank you!" & Chr(13) & Chr(13) & "Larry N. Scartz, LTD." & Chr(13) & "703-494-4181" & vbCrLf & vbCrLf
  35.       ' Add attachments to the message.
  36.       If Not IsMissing("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf") Then
  37.          Set objOutlookAttach = .Attachments.Add("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf")
  38.       End If
  39.       ' Display email
  40.       objOutlookMsg.Display
  42.    End With
  43.    Set objOutlookMsg = Nothing
  44.    Set objOutlook = Nothing
  45.    Set objOutlookRecip = Nothing
  46.    Set objOutlookAttach = Nothing
  48. End If
  50. '   Delete New Order from Add New Orders
  52.     DoCmd.SetWarnings False
  53.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  54.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  55.     DoCmd.SetWarnings True
  57.     Me.lblPrintOrder.Visible = False
  58.     Me.ProgressBar.Visible = False
  59.     Me.Form.Repaint
  61.     DoCmd.RunMacro "Goto New Record"
  63. Exit_Create_Order_File_Structure_Click:
  64.     Exit Sub
  66. Err_Create_Order_File_Structure_Click:
  67.     DoCmd.SetWarnings True
  68.     MsgBox Err.Description
  69.     Resume Exit_Create_Order_File_Structure_Click
  70. End Sub
Nov 19 '08 #8

Expert Mod 15k+
P: 31,560
Here's a related post I made earlier in a completely separate thread.
I always feel the On Error ... commands in VBA are a bit of a bodge. They don't nest properly and are procedure relative.

When using such code it is often necessary to separate out functionality into logical procedures as you have done. It's nearly always the better design anyway mind you, having separate concepts in their own procedures, so it may be a bonus in disguise.
It is complicated handling this in one long procedure. I strongly recommend you look at splittting your code up into logical procedures.
Nov 19 '08 #9

Expert 2.5K+
P: 2,653
Hello, tdw.

Outlook security is a well known and annoying issue.
Would you like to use CDO instead of Outlook automation?

Nov 19 '08 #10

P: 206
Hello, tdw.

Outlook security is a well known and annoying issue.
Would you like to use CDO instead of Outlook automation?

The Outlook security doesn't cause me a problem, other than when it interrupts my code if the user clicks "no" when Outlook asks if they want to "allow" Access to send the email.

I really don't know anything about CDO, but I like for the email to show up in the "Sent Mail" folder in Outlook, and if CDO wouldn't do that then I would prefer to stick with Outlook.

NeoPa, thanks for the advice, I will study up on separating my code into logical procedures (which right now I know nothing about). I'll post a new question if I run into problems with that.

As always, I really appreciate your kind help.
Nov 20 '08 #11

Expert Mod 15k+
P: 31,560
CDO (or CDONTS) won't integrate with Outlook that way I'm afraid. It's becoming more common to use though nowadays due to the restrictions of coding with Outlook.
As always, I really appreciate your kind help.
Not a problem. We like to help if we can :)
Nov 20 '08 #12

Expert 100+
P: 903
You could use an addon DLL called Redemption. The Redemption DLL uses Extended MAPI instead of Simple MAPI and bypasses the Outlook error messages. You can download it for free and try it. If you decide to use it I believe it is a one time $199 fee with unlimited royalty rights.

I have used it and it works great. I am still debating if I will incorporate it in my final app since I am working on another extended MAPI solution for free. Anyways good luck.

Link to redemption

Nov 20 '08 #13

Expert 100+
P: 234
I was checking out the Express ClickYes program (as it is free), which led me to the original paper from a couple of professors at the Air Force Academy about using a vb script to bypass the dialog by using sendkeys, which is what the ClickYes program does. Working from their paper, I developed this code in VBA. Put it in a module and call it from your automated email routine - it will click the "Yes" button for your users.
Expand|Select|Wrap|Line Numbers
  1. Public Sub BypassOutlookSecurity()
  2. Dim fName As String, fs As Object, f As Object
  3.     Set fs = CreateObject("Scripting.FileSystemObject")
  4.     fName = "c:\DATABASES\Bypass.vbs"
  5.     Set f = fs.CreateTextFile(fName, True)
  6.     f.writeline "Set fso = CreateObject(""WScript.Shell"")"
  7.     f.writeline "While fso.AppActivate(""Microsoft Office Outlook"") = FALSE"
  8.     f.writeline "wscript.sleep 1000"
  9.     f.writeline "Wend"
  10.     f.writeline "wscript.sleep 7000"
  11.     f.writeline "fso.SendKeys ""{LEFT}"", True"
  12.     f.writeline "fso.SendKeys ""{ENTER}"", True"
  13.     f.Close
  14.     Shell ("WScript.exe " & fName)
  15. End Sub
You have to have the ability to run scripts on your machine, but most administrators don't have that option disabled.

I've also made a custom form that displays while my email automation code is running that says "Sending automated email - DO NOT click 'No' on the dialog pop-up." This is a just-in-case, as my automation runs overnight in most cases.
Nov 21 '08 #14

Expert 100+
P: 189

This automation issue has annoyed me in the past, too. I now don't even bother trying to do the work in Access, but instead have Access call an ASP page using InternetExplorer.Application, which loads the info into itself from the database and emails it using CDONTS.

One major benefit of this approach is the ability to easily send HTML email (Your Company branding, tabular CSS styled order data, etc) using .HTMLBody and the Microsoft.XMLHTTP object.

Let me know if this appeals to you and I'll post more.

Nov 21 '08 #15

Expert Mod 15k+
P: 31,560
I would suggest going ahead and doing that anyway Gaz.

This is a topic that comes up quite frequently one way and another. Having a fuller answer on your particular solution can only be helpful for all those others looking.
Nov 25 '08 #16

Expert 100+
P: 189
I hadn't actually read all of the posts in this thread, I now see that the OP wants the email to appear in his Outlook .pst file, which this approach obviously can't do.

I think perhaps I'll instead write an article on the subject, and cater for both classic ASP and PHP.

Nov 25 '08 #17

Expert Mod 15k+
P: 31,560
Sounds good.

When done, post a link in here JIC. Remember, there will be other searchers than simply the OP and a link to alternatives won't hurt as long as it's clear that it's not a specific answer to the question.
Nov 25 '08 #18

P: 206
NeoPa, regarding your suggestion to split the code into logical procedures... I can't seem to find information about that. I know I said I'd ask about it in another thread if I needed to, and I still will if I end up needing direct guidance.

For now though, could you point me in a direction where I can find out about this on my own? Thanks
Nov 26 '08 #19

Expert 100+
P: 234
I'm guessing what NeoPa meant was that you should take all of your code from the Dim statements regarding Outlook to the statements setting the Outlook variables to nothing and move them to a new procedure - call it something like Sub Send2Outlook(). Then call it from the original procedure, like so -

If Response = vbYes then Send2Outlook

Splitting things up this way makes your code easier to read and can help with error tracking and debugging.

That about right, NeoPa?

P.S., DoCmd.RunCommand (command) is an easier and more intuitive way to manipulate your records than DoCmd.DoMenuItem. For instance, to delete the current record, use "DoCmd.RunCommand acCmdDeleteRecord." Check the VBA Help file for more on RunCommand.
Nov 26 '08 #20

P: 206
So, I would just cut and paste the code into a new procedure. Would that mean a seperate module? Or somewhere within the form module?

As far as the delete record... I used to have it the way you suggested but then changed it. I can't for the life of me remember why.
Nov 26 '08 #21

Expert Mod 15k+
P: 31,560
That's about the size of it Topher :)

I had this thread pending to give a fuller explanation when I got some time, but that's certainly the gist of it.

Separate procedures can either be created in the same (form's) module or defined as Public in a general module (not attached to a particular object). Determining which is the more appropriate approach comes down to where and when the procedure will ever be called from.

If it's general purpose code then the general module would be the choice. If it's only for use within that particular object (form or whatever) then the form's module would be.

PS. You're also correct in that it is generally better policy to avoid use of commands which are menu relative. Especially as they are so hard to read and interpret.
Nov 27 '08 #22

P: 206
Makes sense. I think in this case a procedure within the form's module would be most appropriate. I'll give it a try.
Dec 1 '08 #23

P: 206
I have the email thing working fine on my computer. However on another computer (my boss's) it just doesn't work. No error message or anything, it just doesn't open a new email message at all. Outlook is installed on his computer, and it is set as his default email program.

Any ideas what might be different on his computer than on mine?
Jan 13 '09 #24

Expert Mod 15k+
P: 31,560
That's a bit of an open-ended question, but I'd look at any security settings you can. Also look at options set in the Access and Outlook clients.
Jan 13 '09 #25

P: 206
I was afraid that would be the case. I've looked at all of those basic security and other settings that I can think of.
I apologize about the question being open ended, but the problem, so far as I can tell, is probably something just that simple. Just can't find it.

However, I also wondered if it's some kind of reference library problem. I looked at all that and it looks fine too.

Just at a loss.
Jan 13 '09 #26

Expert Mod 15k+
P: 31,560
Don't feel bad. That's just the situation you're in (not a pleasant one).

It's like taking the register for absentees though - Hands up those that are missing today.

Also, just because I can't help further - doesn't mean no-one can necessarily. What you've already said so far may register with someone as a problem they're familiar with. Not me though, I'm afraid.
Jan 13 '09 #27

P: 206
Yes that is a good way of describing it. That someone has encountered this before and happens to see the question is all I can hope for. I think I've Googled it all I can at this point.
Jan 14 '09 #28

Expert 100+
P: 234
Thought I'd do a follow-up on my code here for Outlook 2007. It looks like MS pulled the "Office" out of it in the name tagline, so in line 7 of the code you'd change "Microsoft Office Outlook" to "Microsoft Outlook" - also, the same for Outlook 2000.
Feb 13 '09 #29

Post your reply

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