more message box to email help

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,137
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

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
PARTICIPANTDDRO TABSBRKBUDGET EFFECTIVE DATELaunch DateLifeplanSAP
John SmithQU
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,137

ADVERTISEMENT

I modified the code as follows:
Code:
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

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
hi
it doesnt work
i think my font creates unicode (hex) 2714
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,137

ADVERTISEMENT

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:
VBA Code:
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

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,137
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:
VBA Code:
    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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top