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

 

[bemcbride]

Can i send it to you so you can see it? This is driving me crazy I've tried everything and now I'm thinking it's something little that i'm just not doing

 

[VoG]

Maybe there are some leading or trailing spaces

Sub SendEmail()
'use the MsgBox object to inform the user
Dim ans As VbMsgBoxResult
If Range("C10").Value Like "*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

 

[bemcbride]

Maybe because I but a conditional format on it...should I take that off?

 

[bemcbride]

When I run it with "OK to submit" in cell...it tells me "Invalid Procedure Call or Argument "

 

[bemcbride]

I have a new issue now.

I'm getting a invalid procedure or argument error when C9 = "OK to submit". The correct msg box appears when C9 reads "Fields left to complete"

When I debugger it shows this line...I've checked it over and over but can't find a miss...(I was thinking maybe it wanted "If ans" but when I did that it just kept erroring as well.

Your help is very much appreciated!

This is for a process improvement motion I'm having at my company (I'm trying to show off all the things excel CAN do


"Sub SendEmail()
'use the MsgBox object to inform the user
Dim ans As VbMsgBoxResult
If Range("C9").Value Like "*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

 

[ZVI]

This typo comes from the original post #1.
Try instead:
ans = MsgBox("Are you ready to submit CC refund request?" & vbLf & vbLf & "Clicking Yes will send an email to credit cards for processing of your CC refund request", vbYesNo, "Company Credit Card Department")

And I see now Range("C9"), not the discussed Range("C10"). Hope it's correct.

 

[bemcbride]

YESSS!!!! it works!!!


Thank you sooo much it's beautiful lol