vba message box

[rjmdc]

hi
i am trying to have a message box then take user directly to email upon the problem what am i doing wrong?
red text works and creates the message, black text isnt working. also i only want an ok not ok/cancel

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 5 Then
If Target.Value = "_Approval Missing" Then

MsgBox "Notify the boss", vbOKCancel

If MsgBox = vbCancel Then SaveUI = True
If MsgBox = vbOK Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Insert name")
newmsg.Recipients.Add ("name@emailaddress")
'add subject
newmsg.Subject = "Missing Approval"
'add body
newmsg.Body = "Updated document." & vbCrLf & "" & "Please getb approval"
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "Outlook message sent", , "Outlook message sent"


End If
End If
End Sub

 

[Matador56]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Column = 5 Then
    If Target.Value = "Approval Missing" Then
        result = MsgBox("Notify the boss", vbOKCancel)
        If result = vbCancel Then SaveUI = True
        If result = vbOK Then

            Set OutlookApp = CreateObject("Outlook.Application")
            Set OlObjects = OutlookApp.GetNamespace("MAPI")
            Set newmsg = OutlookApp.CreateItem(olMailItem)

            newmsg.Recipients.Add ("name@emailaddress ") ' Add Recipients
            newmsg.Subject = "Missing Approval"  ' Add Subject
            newmsg.Body = "Updated document." & vbCrLf & "" & "Please get approval" ' Email Body
            newmsg.Display   'Display Email
            newmsg.Send      'Send Email

            MsgBox "Outlook message sent", , "Outlook message sent"  ' Confirm Sent Email


        End If
        End If
    End If

End Sub

Try this and see if it does what you need.

 

[rjmdc]

hi
thanks
how to i tell message body to pick up the row that the issue was found in and email just that

 

[Matador56]

newmsg.Body = "Updated document." & vbCrLf & "" & "Please get approval" & Target.Cells.Value

This will append the entire row to your email body. Is this what you needed?

 

[rjmdc]

i put this sub onto the page i want this to happen on?
this will both create a message box and then on the ok create the email?

 

[rjmdc]

can i do this with a vbok only? how do i change the code

 

[Matador56]

i put this sub onto the page i want this to happen on?
this will both create a message box and then on the ok create the email?

This will activate when a cell in column D is changed to the value "Approval Missing". (You might need to add your underscore in again, I removed it for testing)
Paste the code into the Sheet1 code section

 

[rjmdc]

you people are all amazing
the e-mail got sent but it didnt pick up the entire row that had the approvals missing so i have no idea who needs an approval this row has about 12 columns and column a has the name and column f has which approval is missing
how do i polish the code to reflect that

 

[Matador56]

Im away from my computer. But something like Target.EntireRow.Cells.Value might work?

 

[rjmdc]

will try and let you know
stay safe and healthy