Using a Command Button to Send an email and Save New Data

[CP611]

I have a button on my form that when you fill out all the information you press and it will save the information to the table and return the form to blank ready for the next entry. I also have a button that will send an email to the person filling it in with all the information on it so they have a record. So as it stands they have to press the email button then the submit button is there a way I can do this as one button so it submits the information to the table, returns the form to blank and sends an email? The submit button at the moment is done on a macro using the button wizard and save data and refresh.

[Welshgasman]

Use the email button to call the Submit button code?

[CP611]

Hi there, how do I do that?

[Welshgasman]

You Call that item of code
Call cmdSubmit_Click or whatever your button is called.
Some people frown on doing that saying you put that code into it's own sub and call that from any place needed. Up to you.

[CP611]

Hi there, thank you. Is that put at the top of the Send Email button? Below is the code that I currently have, would I put that piece of code that you have at the very top of this or under Private Sub Command28_Click(1)?

Private Sub Command28_Click()


Dim Msg As String

Msg = "To " & AddedBy & ",<P>" & _
"You have submitted the below information for " & TransactionDate & "<P>" & _
"Transaction Type: " & TransactionType & "<P>" & _
"Stock Category: " & StockCategory & "<P>" & _
"Stock Description: " & StockDescription & "<P>" & _
"Quantity: " & Quantity & "<P>" & _
"Location: " & Location & "<P>" & _
"If there is anything incorrect please inform the Warehouse Manager ASAP"


Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatHTML
.HTMLBody = Msg
.To = Email
.Subject = "Stock Change to Warehouse"
.Send
End With

Set M = Nothing
Set O = Nothing

End Sub

[Welshgasman]

Give your controls meaningful names, command28 is not going to mean much to anyone after a few months.
Yes put it as the top. Perhaps some comment ad well to explain the code?

[CP611]

Does it also make a different that on the submit button there isn't code, just an embedded on click macro?

[Welshgasman]

I did not use macroes except for batch purposes, too limiting.
Look to see if you can run code from one.

I would start using vba. There is some option somewhere to convert macroes to vba.