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 = 6 Then
    If Target.Cells.Offset(0, -1).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 ("john.doe@abcd.com.au ") ' Add Recipients
            newmsg.Subject = "Missing Approval"  ' Add Subject
            newmsg.Body = "Updated document." & vbCrLf & "" & _
                          "Please get approval by   " & _
                          Target.Cells.Offset(0, -4).Value & _
                          " for Reference no:   " & _
                          Target.Cells.Offset(0, 1).Value ' 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

This should do what you are asking. It will not activate the macro until you modify column 6, but will only continue if column 5 in the same row contains "_Approval Missing".

 

[rjmdc]

how do i also add the reverse, i tried using what i was taught yesterday but i am messing up the syntax
i would need that if either column 5 is first or column 6 as long as both are filled in and column 5 says approvals missing

 

[Matador56]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Column = 6 Then
 If Target.Cells.Offset(0, -1).Value <> "" Then
   
     If Target.Cells.Offset(0, -1).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 ("john.doe@abcd.com.au ") ' Add Recipients
            newmsg.Subject = "Missing Approval"  ' Add Subject
            newmsg.Body = "Updated document." & vbCrLf & "" & _
                          "Please get approval by   " & _
                          Target.Cells.Offset(0, -4).Value & _
                          " for Reference no:   " & _
                          Target.Cells.Offset(0, 1).Value ' 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 If
If Target.Cells.Count = 1 And Target.Column = 5 Then
    If Target.Cells.Offset(0, 1).Value <> "" Then
   
     If Target.Cells.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 ("john.doe@abcd.com.au ") ' Add Recipients
            newmsg.Subject = "Missing Approval"  ' Add Subject
            newmsg.Body = "Updated document." & vbCrLf & "" & _
                          "Please get approval by   " & _
                          Target.Cells.Offset(0, -4).Value & _
                          " for Reference no:   " & _
                          Target.Cells.Offset(0, 1).Value ' 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 If

End Sub

I am sure there is a better way to do this. But this basically replicates the code to run twice, once for each scenario you listed. See if it works for you. I am nearing my limits of VBA now, its not my most prominent language.

 

[rjmdc]

does anyone have an idea how to do it vice versa without repeating the code twice?

 

[rjmdc]

thanks to all
i used what i learned yesterday and the advice form today and i did it
this is what i used:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then
If Target.Column = 5 And Target.Cells.Value = "_Approval Missing" And Target.Cells.Offset(0, 1).Value <> "" Or _
Target.Column = 6 And Target.Cells.Offset(0, -1).Value = "_Approval Missing" And Target.Cells.Value <> "" 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 ("mail@mail.org") ' Add Recipients
newmsg.Subject = "Missing Approval" ' Add Subject
newmsg.Body = "Updated document." & vbCrLf & "" & _
"Please get approval by " & _
Target.Cells.Offset(0, -4).Value & _
" for Reference no: " & _
Target.Cells.Offset(0, 1).Value ' 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

 

[rjmdc]

i am stuck again
when i add new information that means additional rows to the spreadsheet it gives me a run time error 1004