Send e-mail via command button on form. 4

[Artois27]

I am trying to get a command button on a form to e-mail the form data via MS Outlook 2007 when the button is clicked. I want the” To...” field of the e-mail to be auto populated with an e-mail address that is on a text box on the form in question. I tried achieving this with macros but could not get it to work no matter what I tried.

Can anyone help?

 

[kjv1611]

Are you comfortable with using VBA? That may be your only route, not sure, as I've not tried anything like that via a macro instead of just VBA.

Using VBA, You can tell it: Grab this text from here, throw it into the To: box, grab this from here, put it in the Subject:, that into the body... and so on... and you can tell it to send or to just leave it ready for you to send manually.

One easy way, I think (easier, at least) is using the Outlook object model by enabling it through Tools - References, and then creating an Outlook Application object, and a mail-item object.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[BTSTeam]

Check out the DoCmd.SendObject command.

 

[MazeWorX]

There are plenty of functions written already. just try a search i like to use one that formats the mail in HTML and also eliminates the outlook "another program is try to access .....blah blah blah" nag message and sends the mail. Not practical if you are distributing your db as it requires a module in outlook but ill post if your interested

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Artois27]

Here is the VB code I used to get this function working. The only issue is that it only work when Outlook is open. When it is closed it states a run time error 287. Application defined or object defined error. Any ideas?

'Start of code
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'Creates string with email address
strEmail = boss
strBody = Date_Requested & Chr(13) & Chr(13)

'Creates and sends email
With objEmail
.To = strEmail
.Subject = "New Absence Request"
.Body = strBody
.Send
End With

Set objEmail = Nothing
'Closes Outlook. Remove if you do not want to close Outlook
'objOutlook.Quit
Exit Sub
End Sub

 

[kjv1611]

Artois27,

With your code, you're using the Outlook object (which is totally fine, and what I suggested). So to fix it, here's a variation I did - not identical, but the actions are the same, except for the corrections.

'Outlook test 20120109

Sub TestOutlook()
    Dim appOutlook As New Outlook.Application
    Dim strEmail As String, strBody As String
    
    Dim objEmail As Outlook.MailItem
    
    Set objEmail = appOutlook.CreateItem(olMailItem)
    
    strEmail = "myemail@mydomain.com"
    strBody = "Today is Monday.  Yippee!"
    
    With objEmail
        .To = strEmail
        .Subject = "This is a test"
        .Body = strBody
        .Send
    End With

On Error Resume Next
    If objEmail Is Nothing Then Else Set objEmail = Nothing
'If you want to close Outlook
    If appOutlook Is Nothing Then Else appOutlook.Quit: Set appOutlook = Nothing
'If YOu want to keep Outlook open:
    If appOutlook Is Nothing Then Else Set appOutlook = Nothing
    
End Sub

The above will work without fail whether Outlook is open or closed. I just wrote and tested to verify.

Also, your string variables - notice the changes in my code. You need to specify the type for each variable, or else the one not specified will be set to Variant, which is much larger than string.

Also, I changed the clean-up portion to always close out the objects, just comment out the version you don't want, keep the other one.

And I used appOutlook instead of objOutlook - use whatever you like, of course.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Artois27]

Thanks for your reply Kjv1611.

I inserted your code and replaced mine and changed the following lines so it reads the info I want from my form.

strEmail = boss
strBody = Date_Requested & Chr(13) & Chr(13)

I also commented out the code to close Outlook.

However the exact same issue occurs. Your code works when Outlook is open but not when its closed?

 

[kjv1611]

Well, have you tried stepping through the code to see if it gives errors while stepping through? <F8> key to step through the code...

My guess is that when it opens Outlook, Outlook isn't opened long enough before creating the email message, so basically Outlook is simply not ready for the command, and thus you get an error.

I only tried with stepping through the code. Once early on in the testing, I did get an error when I just executed it. So perhaps you'll need to add some wait function in to slow it down while it wants for Outlook to be running..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Artois27]

Kjv1611,

I think you are correct, I think it just needs to pause for a few secs before sending as .send is where the debug highlights the problem to be when it fails.

Only problem is, how and where in my code do I set this pause? Thanks again for your help.

 

[PHV]

What about this ?

...
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.Application")
[!]DoEvents[/!]
Set objEmail = objOutlook.CreateItem(olMailItem)
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Artois27]

I put that into my code but same issue. I'm not a VB programmer so I don't know how to acheive this pause.

 

[PHV]

And this ?

...
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.Application")
[!]DoEvents[/!]
Set objEmail = objOutlook.CreateItem(olMailItem)
...
  [!]DoEvents[/!]
  .Send
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Artois27]

Sorry it's made no change :-(

 

[Artois27]

It works with the following, I don't which DoEvents is getting it to work but the code below works when outlook is open or closed!

Private Sub Command31_Click()
'Start of code
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.Application")

DoEvents

Set objEmail = objOutlook.CreateItem(olMailItem)

DoEvents

'Creates string with email address
strEmail = boss
strBody = Date_Requested & Chr(13) & Chr(13)

DoEvents

'Creates and sends email
With objEmail

DoEvents
.To = strEmail

DoEvents
.Subject = "New Absence Request"

DoEvents
.Body = strBody
DoEvents
DoEvents
.Send
End With

Set objEmail = Nothing
'Closes Outlook. Remove if you do not want to close Outlook
'objOutlook.Quit
Exit Sub
End Sub

 

[kjv1611]

By the way, where you're using Chr(13), you can use vbCrLf instead - either works, but in some ways, the latter seems easier to remember, and more obvious to someone else reading, perhaps. I've used both, and generally use the latter now.

Glad the whole thing is sorted out now. Sometimes adding DoEvents in various spots in code does indeed help. I've noticed in some applications it helps in that it keeps the application from appearing to just hang - such cases where the given process is going to take a while.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57