VB Code to put an prompt box?

Cossie

Active Member
Joined
May 6, 2002
Messages
323
hello everyone

I have this code on a command button, but need help with adding a step that offers the user the opportunity to cancel the process either by the hitting escape button or clicking on VB buttons.

What do i need to add or modify?

Sub email()

Dim saddress As String
Subject = "Michael Project Referral"
saddress = InputBox("Please enter the Email address to send email")
Msg = "are you sure?"
Buttons = vbCancel + vbOK + vbYes
ActiveWorkbook.SendMail Recipients:=saddress, Subject:=Subject, ReturnReceipt:=False
Cancel = True
MsgBox "Your email has been sent to the Project Manager for actioning" & vbCr & saddress & vbCr & "", , "MESSAGE"

End Sub

Thanks very much :) :)
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
try this:
Code:
Public Sub test_Mail()
Dim Mail_Address, Subject As String

Subject = "Michael Project Referral"
Mail_Address = Application.InputBox("Please enter Mail address to send mail", "e-mail Address", "")
If Not Mail_Address = False And Mail_Address <> "" Then
    If MsgBox("Send mail to " & Mail_Address, vbQuestion + vbYesNo, "Send mail Confirmation") = vbNo Then Exit Sub
    ActiveWorkbook.SendMail Recipients:=saddress, Subject:=Subject, ReturnReceipt:=False
    MsgBox "Your email has been sent to the Project Manager for actioning" & vbCr & Mail_Address & vbCr & "", , "MESSAGE"
End If
End Sub
 

Cossie

Active Member
Joined
May 6, 2002
Messages
323
fantastic works well Thank you

if i wanted to hard code an email address in instead of the option box what would i need to change??


Thanks again
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
First of all the first code i posted had an error (I did not try it out) at string
Recipients:=saddress where it should be: Recipients:=Mail_Address

So original code:
Code:
Public Sub test_Mail()
Dim Mail_Address, Subject As String
'original code, with inputbox blank to fill mail address

Subject = "Michael Project Referral"
Mail_Address = Application.InputBox("Please enter Mail address to send mail", "e-mail Address", "")

If Not Mail_Address = False And Mail_Address <> "" Then
    If MsgBox("Send mail to " & Mail_Address, vbQuestion + vbYesNo, "Send mail Confirmation") = vbNo Then Exit Sub
    ActiveWorkbook.SendMail Recipients:=Mail_Address, Subject:=Subject, ReturnReceipt:=False
    MsgBox "Your email has been sent to the Project Manager for actioning" & vbCr & Mail_Address & vbCr & "", , "MESSAGE"
End If
End Sub

Code to let inputbox on, so you may change the mail address if you want but set a default mail address:
Code:
Public Sub test_Mail1()
Dim Mail_Address, Subject As String, Default_mail As String
'set default address, but let you change it if you want

Subject = "Michael Project Referral"
Default_mail = "ktab71@yahoo.gr" 'place here the default mail address for the inputbox
Mail_Address = Application.InputBox("Please enter Mail address to send mail", "e-mail Address", Default_mail)

If Not Mail_Address = False And Mail_Address <> "" Then
    If MsgBox("Send mail to " & Mail_Address, vbQuestion + vbYesNo, "Send mail Confirmation") = vbNo Then Exit Sub
    ActiveWorkbook.SendMail Recipients:=Mail_Address, Subject:=Subject, ReturnReceipt:=False
    MsgBox "Your email has been sent to the Project Manager for actioning" & vbCr & Mail_Address & vbCr & "", , "MESSAGE"
End If
End Sub

Last, hardcoded mail address , only confirm with msgbox:
Code:
Public Sub test_Mail2()
Dim Mail_Address, Subject As String, Default_mail As String
'harcoding the mail address ; no option to change it , only through VBE

Subject = "Michael Project Referral"
Default_mail = "ktab71@yahoo.gr" 'hardcode mail address here

If MsgBox("Send mail to " & Default_mail, vbQuestion + vbYesNo, "Send mail Confirmation") = vbNo Then Exit Sub
ActiveWorkbook.SendMail Recipients:=Default_mail, Subject:=Subject, ReturnReceipt:=False
MsgBox "Your email has been sent to the Project Manager for actioning" & vbCr & Default_mail & vbCr & "", , "MESSAGE"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

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