more message box to email help

[rjmdc]

last message worked amazing
how can i tweak

whats am i doing 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", vbOK + vbExclamation, "can launchl")

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

 

[Anthony47]

whats am i doing wrong?

The first error is let us alone in trying to understand what you are wishing to do

Then I guess this line is wrong: If Target.Cells.Count = 1 And Target.Column = 6 and Target.Column = 7 Then

Also wonder which characters set you used to insert the "check mark" in your vba.

Bye

 

[rjmdc]

i was trying to use a code i already used
i am learning more and more
i know the first 2 lines are wring
i need if
1- both cells on this row in column 6 and 7 are checked
2- the check was created form a dropdown that i used a symbol, i couldnt figure out how to get that symbol into vba

stay safe and healthy

 

[rjmdc]

PARTICIPANT	DDRO	TABS	BRK	BUDGET EFFECTIVE DATE	Launch Date	Lifeplan	SAP
John Smith	QU	111111​	JC	10/1/2019​	11/4/2019​	✔	✔

if lifeplan and sap are both checked (that means when i enter the second check regardless if in lifeplan or sap (i can enter either or first etc depending on when i receive the document)
then i want to activate the message box and email

can that be done?
i think the symbol used for the checkmark was unicode(hex) 2714

 

[Anthony47]

I modified the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
    If Target.Column = 6 And AscW(Target.Value & " ") = 10003 And AscW(Target.Offset(0, 1).Value & " ") = 10003 Or _
      Target.Column = 7 And AscW(Target.Value & " ") = 10003 And AscW(Target.Offset(0, -1).Value & " ") = 10003 Then
            result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "can launchl")
            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

Try it...

 

[rjmdc]

hi
it doesnt work
i think my font creates unicode (hex) 2714

 

[Anthony47]

You know what you do, I rely on your information.

If (hex) 2714 is used then you have to repalce those 10003 with 10004

You can test the charactercode to be used with this macro:

Sub UnicCode()
'0
MsgBox (AscW(Selection.Cells(1, 1).Value))
End Sub

Select a cell and execute Sub UnicCode, a msgbox will return the code
Does not work on empty cells

Bye

 

[rjmdc]

so my code reads but it still doesnt work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 7 And AscW(Target.Value & " ") = 10004 And AscW(Target.Offset(0, 1).Value & " ") = 10004 Or _
Target.Column = 8 And AscW(Target.Value & " ") = 10004 And AscW(Target.Offset(0, -1).Value & " ") = 10004 Then
result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "can launch")
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") ' ("mail@mail.org")
newmsg.Subject = "can launch" ' Add Subject
newmsg.Body = "launch" & vbCrLf & "" & _
"Please schedule launch for " & _
Target.Cells.Offset(0, -6).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]

please explain this part of the code
AscW(Target.Value & " ") = 10004
all i have in the cell is a checkmark that was verified with your code as 10004

 

[Anthony47]

so my code reads but it still doesnt work

It doesnt work means nothing...
The code, if put in the correct position (the "Class Module" of the worksheet that you are working on), will activate an outlook process for creating and sending a new mail when:
-column 6 is modified, the modified cell contains the checkmark, column 7 of the same contains also a checkmark
or
-column 7 is modified, the modified cell contains the checkmark, column 6 of the same row contains also a checkmark

With your modification it works on columns 7 and 8

For additional certainty, add brackets on these two lines:

    If (Target.Column = 6 And AscW(Target.Value & " ") = 10004 And AscW(Target.Offset(0, 1).Value & " ") = 10004) Or _
      (Target.Column = 7 And AscW(Target.Value & " ") = 10004 And AscW(Target.Offset(0, -1).Value & " ") = 10004) Then

AscW(Target.Value & " ") = 10004

This ckecks which character is in the modified cell

I also suggest that you replace "olMailItem" with 0 (zero), unless you already set a reference to the Outlook vba library (but I am not suggesting that you add this reference)

Bye