Add a Msg Box

[matthewnsarah07]

Just a quick one!
I have attached some code I am using below - it sends an email summary of a holiday request form using the data from the current record viewed.

Once the email has been sent via Outlook I just want a Msg Box to pop up to say it was sent succesfully with an ok button.

What code do I need to add?
-------------------------------------

Private Sub Command22_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
.CC = "RCCNWResourceTeam@highways.gsi.gov.uk" & ";" & Me.Staff_Name
.Subject = "RCC Leave Request Number " & [Request_Number] & " "
.Body = "Leave Request Summary as follows:" & vbCrLf & vbCrLf & "Staff Name: " & [Staff_Name] & " " & [Staff_Number] & vbCrLf & vbCrLf & "Team Manager: " & [Team_Manager] & vbCrLf & vbCrLf & "Request Type: " & [Request_Type] & vbCrLf & vbCrLf & "Date Submitted: " & [Date_of_Request] & vbCrLf & vbCrLf & "Date From: " & [Date_From] & " Date To: " & [Date To] & " Total: " & [Total Days] & vbCrLf & vbCrLf & "Type of Leave: " & [Leave_Type] & vbCrLf & "Notes: " & [Notes]



.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

 

[doco]

msgbox "Message has been sent", vbOKOnly, "MyMessage"

 

[doco]

Cleaned up a bit

Private Sub Command22_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)

mess_body = "Leave Request Summary as follows:" & vbCrLf & vbCrLf
mess_body = mess_body & "Staff Name: " & [Staff_Name] & " " & [Staff_Number]
mess_body = mess_body & vbCrLf & vbCrLf & "Team Manager: " & [Team_Manager]
mess_body = mess_body & vbCrLf & vbCrLf & "Request Type: " & [Request_Type]
mess_body = mess_body & vbCrLf & vbCrLf & "Date Submitted: "
mess_body = mess_body & [Date_of_Request] & vbCrLf & vbCrLf & "Date From: "
mess_body = mess_body & [Date_From] & " Date To: " & [Date To] & " Total: "
mess_body = mess_body & [Total Days] & vbCrLf & vbCrLf & "Type of Leave: "
mess_body = mess_body & [Leave_Type] & vbCrLf & "Notes: " & [Notes]

    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = Me.Team_Manager
        .CC = "RCCNWResourceTeam@highways.gsi.gov.uk" & ";" & Me.Staff_Name
        .Subject = "RCC Leave Request Number " & [Request_Number] & " "
        .Body = mess_body
        .Send
    End With

    MsgBox "Message has been sent", vbOKOnly, "MyMessage"
'   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

 

[doco]

I hadn't noticed before but you initialized the outlook objects twice. Was that intentional?

Private Sub Command22_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)

mess_body = "Leave Request Summary as follows:" & vbCrLf & vbCrLf
mess_body = mess_body & "Staff Name: " & [Staff_Name] & " " & [Staff_Number]
mess_body = mess_body & vbCrLf & vbCrLf & "Team Manager: " & [Team_Manager]
mess_body = mess_body & vbCrLf & vbCrLf & "Request Type: " & [Request_Type]
mess_body = mess_body & vbCrLf & vbCrLf & "Date Submitted: "
mess_body = mess_body & [Date_of_Request] & vbCrLf & vbCrLf & "Date From: "
mess_body = mess_body & [Date_From] & " Date To: " & [Date To] & " Total: "
mess_body = mess_body & [Total Days] & vbCrLf & vbCrLf & "Type of Leave: "
mess_body = mess_body & [Leave_Type] & vbCrLf & "Notes: " & [Notes]

    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = Me.Team_Manager
        .CC = "RCCNWResourceTeam@highways.gsi.gov.uk" & ";" & Me.Staff_Name
        .Subject = "RCC Leave Request Number " & [Request_Number] & " "
        .Body = mess_body
        .Send
    End With

    MsgBox "Message has been sent", vbOKOnly, "MyMessage"
'   MsgBox MailOutLook.Body
TheExit:
    Set appOutLook = Nothing
    Set mailoputlook = Nothing
    On Error GoTo 0
    Exit Sub
    
email_error:
    MsgBox "An error was encountered." & vbCrLf & _
        "The error message is: " & Err.Description
    Resume TheExit
        
End Sub

Its always a good idea to clean up even though VB has a garbage collection. Also, unless you intend for the error trap to extend to other routines, its a good idea to always zero your error traps.

HTH

 

[matthewnsarah07]

Thanks for your help, works perfectly!

Don't know why I initialised Outlook twice, I think its because I've used this code elsewhere to send 2 separate emails and its been left behind

Thanks Again