Email Automation from Access Problem

[tdw]

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.

'   Email New Order to Recipient
 
'   Yes No Box
    Msg = "Do you want to EMAIL a confirmation of this order?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Email Confirmation"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
 
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim stRecip As String
 
    If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
    stRecip = ""
    Else
    stRecip = DLookup("email", "cust", "[cusname]='" & Me!ORDER & "'")
    End If
 
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")
   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(stRecip)
      objOutlookRecip.Type = olTo
      ' Set the Subject and Body of the message.
      .Subject = "Survey Order Receipt Confirmation (" & Me.CASE_N & ")"
      .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
      ' Add attachments to the message.
      If Not IsMissing("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf") Then
         Set objOutlookAttach = .Attachments.Add("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf")
      End If
      ' Display email
      objOutlookMsg.Display
 
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set objOutlookRecip = Nothing
   Set objOutlookAttach = Nothing
 
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.

Thanks!

[emsik1001]

Hi

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
Emil

[NeoPa]

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 :

objOutlook.Visible = True

Helpful resources :
(Debugging in VBA).
(Application Automation).

[Bugran]

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.

Bugran

[tdw]

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.

Thanks

[tdw]

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?

[NeoPa]

Have you tried any error handling commands like :

On Error Resume Next

[tdw]

Have you tried any error handling commands like :

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?

'   Email New Order to Recipient
 
On Error Resume Next
 
'   Yes No Box
    Msg = "Do you want to EMAIL a confirmation of this order?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Email Confirmation"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
 
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim stRecip As String
 
    If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
    stRecip = "email_address_unknown"
    Else
    stRecip = DLookup("[email]", "cust", "[cusname]='" & Me!ORDER & "'")
    End If
 
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")
   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(stRecip)
      objOutlookRecip.Type = olTo
      ' Set the Subject and Body of the message.
      .Subject = "Survey Order Receipt Confirmation (" & Me.CASENO & ")"
      .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
      ' Add attachments to the message.
      If Not IsMissing("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf") Then
         Set objOutlookAttach = .Attachments.Add("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf")
      End If
      ' Display email
      objOutlookMsg.Display
 
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set objOutlookRecip = Nothing
   Set objOutlookAttach = Nothing
 
End If
 
'   Delete New Order from Add New Orders
 
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True
 
    Me.lblPrintOrder.Visible = False
    Me.ProgressBar.Visible = False
    Me.Form.Repaint
 
    DoCmd.RunMacro "Goto New Record"
 
Exit_Create_Order_File_Structure_Click:
    Exit Sub
 
Err_Create_Order_File_Structure_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_Create_Order_File_Structure_Click
End Sub
 

[NeoPa]

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.

[FishVal]

Hello, tdw.

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

Regards,
Fish.

[tdw]

Hello, tdw.

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

Regards,
Fish.

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.

[NeoPa]

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 :)

[mshmyob]

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

cheers,

[topher23]

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.

Public Sub BypassOutlookSecurity()
Dim fName As String, fs As Object, f As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    fName = "c:\DATABASES\Bypass.vbs"
    Set f = fs.CreateTextFile(fName, True)
    f.writeline "Set fso = CreateObject(""WScript.Shell"")"
    f.writeline "While fso.AppActivate(""Microsoft Office Outlook"") = FALSE"
    f.writeline "wscript.sleep 1000"
    f.writeline "Wend"
    f.writeline "wscript.sleep 7000"
    f.writeline "fso.SendKeys ""{LEFT}"", True"
    f.writeline "fso.SendKeys ""{ENTER}"", True"
    f.Close
    Shell ("WScript.exe " & fName)
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.

[GazMathias]

Hi,

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.

Gaz

[NeoPa]

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.

[GazMathias]

@NeoPa
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.

Gaz

[NeoPa]

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.

[tdw]

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

[topher23]

@tdw
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.

[tdw]

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.

[NeoPa]

@topher23
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.

[tdw]

Makes sense. I think in this case a procedure within the form's module would be most appropriate. I'll give it a try.

[tdw]

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?

[NeoPa]

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.

[tdw]

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.

[NeoPa]

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.

[tdw]

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.

[topher23]

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.