Prevent e-mail if condition not met

[Niven]

Hi, What is wrong with this code?Sub Send_via_email(Cancel As Boolean)'' Send_via_email Macro' Dim blnCancel As Boolean Dim sMail As String Dim rngCell1 As Excel.Range, rngCell2 As Excel.Range sMail = Application.Dialogs(xlDialogSendMail).Show blnCancel = Not Sheets("Weekly Timesheet").Range("C44").Value If blnCancel Then Call MsgBox(Prompt:="Have ALL hours worked been allocated correctly?", Title:="Cannot send e-mail", Buttons:=vbExclamation + vbOKOnly)' If Cancel = blnCancel Then sMail End IfEnd Sub

 

[Jon von der Heyden]

Hi Niven

For one it's fairly illegible

This is what you have posted, but broken down line-by-line and indented:

Sub Send_via_email(Cancel As Boolean)
    '' Send_via_email Macro'
    Dim blnCancel As Boolean
    Dim sMail As String
    Dim rngCell1 As Excel.Range, rngCell2 As Excel.Range
    
    sMail = Application.Dialogs(xlDialogSendMail).Show
    blnCancel = Not Sheets("Weekly Timesheet").Range("C44").Value
    
    If blnCancel Then
        Call MsgBox(Prompt:="Have ALL hours worked been allocated correctly?", Title:="Cannot send e-mail", Buttons:=vbExclamation + vbOKOnly)
        'If Cancel = blnCancel Then sMail
    End If
End Sub

You haven't said what the problem is but I'm guessing it's allowing you to send email regardless of whether or not blnCancel is True/False.
The reason for that is you are calling the SendMail dialog BEFORE you test whether or not it should be cancelled. That bit should come after... Also I don't see the point in having a Cancel argument for the routine

Sub Send_via_email()
    Dim blnCancel As Boolean
    Dim sMail As String

    blnCancel = Not Sheets("Weekly Timesheet").Range("C44").Value
    If blnCancel Then
        Call MsgBox(Prompt:="Have ALL hours worked been allocated correctly?", Title:="Cannot send e-mail", Buttons:=vbExclamation + vbOKOnly)
        Exit Sub
    Else
        Application.Dialogs(xlDialogSendMail).Show
    End If
End Sub

Finally; consider a different approcah for sending mail. There are plenty of ways shown here: Ron De Bruin - Send Mail. This way your code can actually send the mail rather than just create a mail item.

Edit: To paste code in the forum do the following:
1) Copy the code from the VBE
2) In the forum post body type [code]; then past your code; and at the end of the code type [/code]

 

[Niven]

Thanks a lot you're a genius! I tried copying and pasting but the code still came up all in one paragraph. The code works perfectly! I don't quite understand the other option you spoke about i.e. sending the mail without opening outlook.

 

[Jon von der Heyden]

I don't quite understand the other option you spoke about i.e. sending the mail without opening outlook.

I posted a link to his website in my last post. The links aren't so obvious, but click Ron De Bruin - Send Mail.

 

[Niven]

Thanks a lot, I managed to copy some code and it works!

 

[Niven]

Hi I am trying to post a code but when I preview the post it still comes up with all the code lumped together. I typed

 before the first word and then at the end I typed

.

 

[Niven]

I don't know why but I am trying to post a code but it doesn't appear correctly and I am using what you told me - to type code in brackets. The entire code appears in one long paragraph.

 

[Jon von der Heyden]

Are you posting directly from the VBE? Try pasting it to Word 1st.

 

[Niven]

Hi Sorry I tried it a few times, copied from the Word doc and pasted and then when I preview post it is all in a mess???

 

[Jon von der Heyden]

Ok just post it here and I'll sort it out for you.