VB Code to put an prompt box?

Cossie

Active Member
Joined
May 6, 2002
Messages
328
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 :) :)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top