Macro to prompt user to check Subject when send button is clicked

[hotmailliw]

Hi

I am trying to come up with a code in excel vba that will prompt use to double check the subject when "Send"
is clicked.

The code i have below is for Outlook vba and will only prompt when the Subject is empty

Is it possible to edit the below so that i can use it in excel and have it to prompt when "Send" is clicked. Thank you

<code style="font-family: Courier, monospace; margin: 0px; padding: 0px; width: auto; zoom: 1; white-space: pre-wrap;">Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean) If Item.Subject = "" Then 'Edit the message and the popup caption on the next line as desired.' msgbox "You are not allowed to send an item with a blank subject. Please enter a subject and send again.", vbCritical + vbOKOnly, "Prevent Blank Subjects" Cancel = True End IfEnd Sub</code></pre>

 

[Ruddles]

What is the "Send" that you are clicking? Is it the one you get when you click the Office button or have you already got some VBA in your Excel workbook which does the sending?

 

[hotmailliw]

What is the "Send" that you are clicking? Is it the one you get when you click the Office button or have you already got some VBA in your Excel workbook which does the sending?

The "Send" refers to the Outlook "Send". I have a existing macro that copy and paste everything in the worksheet into an outlook email but does not send it.

I need a warning box that will pop up when user clicks the "Send" button, that says "You are able to send Subj:xxx"

Thanks

 

[Ruddles]

Post the code you have and I'll show you how to modify it (unless someone beats me to it).

 

[hotmailliw]

Hi Ruddles

This is the code below. Appreciate much for your help. Thanks

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range
On Error GoTo StopMacro
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = Worksheets("Sheet1").Range("A1:B15")
'Remember the activesheet
Set AWorksheet = ActiveSheet
'Create the mail and send it
With Sendrng
' Select the worksheet with the range you want to send
.Parent.Select
'Remember the ActiveCell on that worksheet
Set rng = ActiveCell
'Select the range you want to mail
.Select
' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "This is a test mail."
' In the "With .Item" part you can add more options
' See the tips on this Outlook example page.
' Tips for the Outlook object model examples
With .Item
.To = "l"
.Subject = "My subject"
.Item.Send
End With
End With
'select the original ActiveCell
rng.Select
End With
'Activate the sheet that was active before you run the macro
AWorksheet.Select
StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
'ActiveWorkbook.EnvelopeVisible = False
End Sub

 

[Ruddles]

Replace the line which reads .Item.Send with something like this:-

if .subject="" then
  msgbox "Subject is blank - please insert a subject and send",vbexclamation+vbokonly
  .item.display
else
  .item.send
endif

I'm sure you can see what's happening in this snippet of code.

 

[hotmailliw]

Replace the line which reads .Item.Send with something like this:-

if .subject="" then
  msgbox "Subject is blank - please insert a subject and send",vbexclamation+vbokonly
  .item.display
else
  .item.send
endif

I'm sure you can see what's happening in this snippet of code.

Hi Ruddles

Actually i am really noob with vba. i have been trying to piece parts of codes together and add in mine during the process.

I have been playing with the code, is it possible to make the pop up appear after the email is displayed?

Currently it is appearing before the email is displayed. Thanks

 

[Ruddles]

I think you can see which statement prodcues the message box and which one displays the mail item. Swap them round.

However I suspect what this will do is display the mail item, switch focus to Outlook, and leave the message box waiting for Excel to take focus again, with the Excel icon flashing, which is probably not what you want.


Try it anyway. I think the first version will be better.

 

[hotmailliw]

hi ruffles, thank you.