Two VBA Commands into One

[matthewnsarah07]

Hopefully this should be a quick one to sort

I'm currently using two seperate pieces of code via two button on a form to send automated emails to staff.

Simply, I would like to only have to press one button to send both the emails at the same time. Is it easier to merge the to pieces of code or to create a third code that uses one after the other.

Currently they are Command 22 and Command 26 - whats my best option and what code do I need, I'm a novice on VBA

Thanks for your help

 

[pbaldy]

It would help to see the code behind the buttons, but generally you should be able to run both processes behind one button:

DoCmd.SendObject...send first email
DoCmd.SendObject...send second email

 

[matthewnsarah07]

The code for one of the commands is

Private Sub Command26_Click()

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.Team_Manager
.Subject = "Thank you for updating Request Number " & [Request_Number]
.Body = "Leave Request Number " & [Request_Number] & " has now been processed" & vbCrLf & vbCrLf & [Staff_Name] & " has been sent an email informing them of the outcome" & vbCrLf & vbCrLf & "Both the Rota and Leave Sheet have been updated accordingly by " & [Completed_Initials] & vbCrLf & vbCrLf & "Regards" & vbCrLf & vbCrLf & "NW Resource Team" & vbCrLf & vbCrLf & "*Please Note - this email is generated by Leave Manager"
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:



End Sub


The other is the same with some changes in the body of the email. How would I intergtrate two of these commands to work from one - send object doesn't seem to work as the code uses fields etc from the current form view aswell

Thanks

 

[pbaldy]

You can still use SendObject with fields from an open form, but to use your code try adding this right after the End With:

Set appOutLook = Nothing
Set MailOutLook = Nothing

then copy in from the other procedure everything from

Set appOutLook = CreateObject("Outlook.Application")

to

End With

By the way, a rule of thumb to follow is that anything that is "Set" should be set to Nothing when done, so those lines I added should also be at the end of the procedure. I assume the duplicate Set lines are a cut/paste error?

 

[matthewnsarah07]

Thanks Paul I think I've cracked it

But can't exactly be sure as our Exchange server has just gone down so I can't email - no errors were thrown up in the code though so hopefully it is working as planned

 

[pbaldy]

You might be able to see the unsent emails in your Outbox. I think it will work though, so let me know if it doesn't.

 

[matthewnsarah07]

It worked once the exchange server cam back on this morning

Thanks