Sending Outlook email through Access VBA

[bigu2fan00]

Hello.
I don't know Access VBA (I just know Excel) so I need help please. I added a button with the below code. This button brings up an email in Outlook with the specified info below plus the txtsuggest as the body of the email. The txtsuggest is just a text box on Access where the user can type whatever they want to send. Right now everything works fine, however, I want Access to send the email automatically after the user types the stuff into the text box. Currently, it just brings up the email window with all of the necessary info and forces the user to hit the "send" button on the email. How can I get it to auto send?

Thanks!!!



Private Sub Command2_Click()
On Error GoTo Err_Command2_Click



DoCmd.SendObject acSendNoObject, , , "Person's email", , , " Custody Fund Database Comment/Suggestion", txtsuggest


Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

[RobDog888]

The DoCmd method will always display the mail item and have the
user press the send key. Two options, one user some APIs to
press the send key. Two, use some different code like below
which will send the mail item without displaying it, but may bring
up a security prompt depending on your version of Outlook.

Option Explicit
'Add reference to MS Outlook xx.0 Object library
Private moApp As Outlook.Application
 
Private Sub Command1_Click()
    
    Dim oEmail As Outlook.MailItem
    
    Set oEmail = moApp.CreateItem(olMailItem) 'Or 0
    oEmail.To = "Person's email"
    oEmail.Subject = "Custody Fund Database Comment/Suggestion"
    oEmail.Body = txtsuggest
    oEmail.Send
    
End Sub
 
Private Sub Form_Load()
    Set moApp = New Outlook.Application
End Sub

[bigu2fan00]

Thanks for your response. I tried copying/pasting your code but I'm having a little bit of trouble and got the below error message. This error is referring to the "On Load" part and to the "On Click" part. It seems like this is conflicting or something with the code I had pasted on my original post because that's not working now and is giving me the same error.

What am I doing wrong here?

Thanks.



"the expression may not result in the name of a macro, the name of a user-defined fuction, or Event Procedure."
"there may have been an error evaluating the function, event, or macro."

[RobDog888]

Can you post what you have in the event procedures exactly
and what does it show in the properties window for the form in
question for the two event procedures?

[bigu2fan00]

Thanks for your response.
I've created two forms....one with the code I originally posted and one with the code you supplied. Everything in the two procedures are posted in this forum as it appears in VBA so I'm not sure what else I can provide.

As far as the properties, like I said there are two forms. What part(s) of the properties do you want to know. I'm just trying to think of a way to get you the info you need without having to type all of that out. If it's a must that I need to provide each field, then let me know.

Thanks for your time.

[RobDog888]

In the "On Load" property it should show - "[Event Procedure]".
Same for the On Click event. Do they say that?

[bigu2fan00]

The "on click" property does reflect [Event Procedure]. However, the property box doesn't have an "on-load" property. Here's what it does have though......
Within that event procedure is the VBA code you provided to me. The only "load" that I see is within the code you gave me.....


On Enter
On Exit
On Got Focus
On Lost Focus
On Click [Event Procedure]
On Dbl Click
On Mouse Down
On Mouse Move
On Mouse Up
On Key Down
On Key Up
On Key Press


Many Thanks for your patience and help.

[RobDog888]

Sorry about the On Load that is only for VB. Here is a working example
database I put together for you. It has one table and one form. On
the form that is bound to the table, it allows you to select the
name or email address to use to create an email for and send it.

It already has the reference to Outlook 11.0 so you will need to
change it to your version before you try. Its in the class behind
Form1.

HTH

[bigu2fan00]

That's very kind of you to create that. I'll take a look at it.

Thanks for your help!!

[bigu2fan00]

Well, I guess I'm stuck already, sorry. Like I said I'm new at this so it's probably me. I've pasted the code directly from the .mdb you wrote for me. It's giving me the same "on click" error from before. Do I need to change anything at all in the below code to get your program working?
I don't know what Outlook version I have (10.0??). Here's what it says...
MS Outlook 2002 (10.6515.6626) SP3.
You said to change my Outlook version if needed because the below code is for version 11.0 but I guess I'm confused because I don't see where the below code points to 11.0. Can you help me with that too?

thanks!!



Option Explicit
Option Compare Database
'Add reference to MS Outlook xx.0 Object library
Private moApp As Outlook.Application

Private Sub cmdSend_Click()

Dim oEmail As Outlook.MailItem

Set oEmail = moApp.CreateItem(olMailItem) 'Or 0
oEmail.To = Forms![Form1]!txtEmailAddress.Value
oEmail.Subject = "Custody Fund Database Comment/Suggestion"
oEmail.Body = Forms![Form1]!txtSuggestions.Value
oEmail.Send

End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()
Set moApp = New Outlook.Application
End Sub

[RobDog888]

In the VB Editor (Atl + F11) click on Tools > References... >
and select MS Outlook 10.0 Object Library. Then click ok.

You have Outlook 10.0 or Outlook XP.

This should get you to your next issue.

[RobDog888]

Just noticed something. What is the name of your form? In my
code I am using Form1 so you need to change it to reflect your
forms name.

'...
oEmail.To = Forms![[i]WhatEverYourFormsNameIsPutItInHere[/i]]!txtEmailAddress.Value
'Etc.
'...
'...
'...

[bigu2fan00]

Ok..... I see that in the references MS Outlook 10.0 was checked but AlSO checked was "missing MS Outlook 11.0" so I unchecked just that.

I'm starting basic so right now, meaning, I'm just trying to run your program that you uploaded to see if it works. Therefore, the VB code should be correct regardiing the Form1 name but I still get the same error regarding the "on-click" action.

Any other suggestions, or things I should check? If we can figure out why your program isn't working for me, then we can carry that over into my program.

Thanks.

[RobDog888]

So your saying that even the db I created for you doesn't work? If
so, then maybe its your Access installation. You may want to un-
install it and the reboot. Then do a re-install and then try my db.

HTH

[bigu2fan00]

Well, the MS Access I'm using is at my job and 1,100 use the same thing so I don't think it's just my computer or install. I can't reinstall because I don't have the privileges and IT would need a valid reason to reinstall.

Thanks anyways for the help.