URGENT: VBA msgbox send email issue

[bemcbride]

It's been awhile since I've done VBA so I'm rusty


I have a sheet, that has an If statement saying =IF(B9>0,"Fields left to complete","OK to submit") there's also a Submit button. If user clicks submit button and cell says "OK to submit" then a msgbox comes up saying it will send an email to email@company.com and the email is sent. If user clicks submit button and cell says "Fields left to complete" a msg box comes up saying you haven't filled everything out and they just click ok.


When i click the button it will give me the "You haven't filled everything out" part. I think VBA is not recognizing the OK to submit part.

PLEEEAAAASSSSEEE help I need to turn this is soon




Here's what I have below:



Sub SendEmail()
'use the MsgBox object to inform the user

If C10 = "OK to submit" Then
MsgBox "Are you ready to submit CC refund request?", vbYesNo, "Company Credit Card Department", , "Clicking Yes will send an email to credit cards for processing of your CC refund request"
ActiveWorkbook.SendMail "email@company.com", "CC refund request for" & B17
Else
MsgBox "You have not completed all required fields."
End If


End Sub

 

[VoG]

Maybe

Sub SendEmail()
'use the MsgBox object to inform the user
Dim ans As VbMsgBoxResult
If Range("C10").Value = "OK to submit" Then
    ans = MsgBox("Are you ready to submit CC refund request?", vbYesNo, "Company Credit Card Department", , "Clicking Yes will send an email to credit cards for processing of your CC refund request")
    If ans = vbYes Then ActiveWorkbook.SendMail "email@company.com", "CC refund request for" & Range("B17").Value
Else
    MsgBox "You have not completed all required fields."
End If
End Sub

 

[BiocideJ]

If I understand this correctly,

If C10 = "OK to submit" Then

Should be

 If Range("C10").Value = "OK to submit" Then

EDIT: above answer is actually more complete because it actually handles the Yes/No options instead of just sending the e-mail no matter what you click.

 

[bemcbride]

the message box is still telling me "you have not completed all required fields" when cell says OK to submit....is it because the cell has a formula in it and not just ok to submit as in text?

 

[VoG]

What is the formula in C10?

 

[bemcbride]

=IF(B9>0,"Fields left to complete","OK to submit")

 

[BiocideJ]

Is the button on the same tab as the cell with that formula? If not, what is the name of the tab where C10 is supposed to be read from?

Also, for the same reason, is the B17 cell that is to be included in the e-mail on the same tab as the button or another tab?

 

[VoG]

So it should work as expected - is the macro looking at the correct sheet?

 

[bemcbride]

Everything is on the same tab

 

[bemcbride]

Lol I'm not sure...I mean it will give me the 2nd message so I'm assuming yes. And everything is on the same tab