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

 

[rjmdc]

no
doesnt work says type mismatch
when available can you please help with this final twaek of the code
otherwise it is amazing

 

[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 ("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

 

[rjmdc]

what does this do?

 

[Matador56]

what does this do?

Gets the values from column A and F and puts in the email for you

 

[rjmdc]

you guys are amazing. this is perfect
may you be blessed with health and all good
stay safe

 

[rjmdc]

how do i chose button vb exclamation ok/cancel

 

[rjmdc]

did it with the + symbol
thanks
this is magic

 

[rjmdc]

1-can my email be sent to more than one e-mail address, how can i enter all the addresses?
2- what if the reply i am looking for is a ✔ in BOTH columns G and H then reply yes of message box sends email value of column A "is ready to launch"

 

[rjmdc]

whats wrong?


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Column = 6 and Target.Column = 7 Then
If Target.Value = "✔" Then
result = MsgBox("pressing OK will send email to notify", vbOKCancel + vbExclamation, "can launchl")
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 = "can launchl" ' Add Subject
newmsg.Body = "launch" & vbCrLf & "" & _
"Please schedule launch for " & _
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]

this code worked really well however the email needs to give me the information in the same row of column 6
however, the code and message box and email run as soon as _Approvals missing is entered in column 5 before the class name is entered in column 6.
is there a way to wait for column 6 to be populated before the code runs?

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 ("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

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 ("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