Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Check Box with MsgBox Yes/No. If Yes.... IF no...

    Hello All,

    My understanding of VBA is very basic, so any help concerning this will be much appreciated.

    The below script delivers two aims

    Step 1) Changes the colour of the allocated cells and adds a user stamp
    Step 2) Sends an email depending on whether yes/no is selected.

    ------------------------------------------------
    Private Sub CheckBox15_Click()

    If CheckBox15.Value Then
    Range("A13:C13").Interior.ColorIndex = 14
    Range("E13").Value = Environ("UserName")
    Else
    Range("A13:C13").Interior.ColorIndex = 35
    Range("E13").ClearContents
    End If

    If CheckBox15.Value Then
    MsgBox "Did any errors occur, whilst performing the High Priority checks?", vbYesNo, "High Priority Checks"
    Else
    CheckBox15 = False
    End If

    If Yes Then
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim MyReci As Outlook.Recipient

    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)
    End If

    With OutMail

    .Recipients.Add ("paul martin")
    .Subject = "High Priority Checks - Please Read"
    .Body = "High Priority Checks Completed, with any errors"
    .Display
    End With

    If No Then
    End If
    With OutMail
    .Recipients.Add ("paul martin")
    .Subject = "High Priority Checks - OK"
    .Body = "High Priority Checks Completed, without any errors"
    .Display
    End With

    End Sub

    ---------------------------------------------------------------------------------

    When part 1 was successful, i'm failing in delivering part 2.

    My aim is, when yes or no is selected an email is generated, this email will be set to .Display so if any editing is required it can be done before the email is sent.

    The text in Red is where the error begins, however i'm unable to overcome the problem.


    Please can anyone help?

    Thank you in advance.

    Kind Regards.
    Last edited by BGL; 2015-11-10 at 10:03.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Your If / End ends before you've sent the mail, so the mail field population fails. You need something like this.

    cheers, Paul

    Code:
    If CheckBox15.Value Then
    iResponse = MsgBox("Did any errors occur, whilst performing the High Priority checks?", vbYesNo, "High Priority Checks")
    Else
    CheckBox15 = False
    End If
    
    If iResponse = 6 Then 'Yes
    	Dim OutApp As Outlook.Application
    	Dim OutMail As Outlook.MailItem
    	Dim MyReci As Outlook.Recipient
    
    	Set OutApp = New Outlook.Application
    	Set OutMail = OutApp.CreateItem(olMailItem)
    	With OutMail
    
    	.Recipients.Add ("Add Example Here")
    	.Subject = "High Priority Checks - Please Read"
    	.Body = "High Priority Checks Completed, with errors"
    	.Display
    	End With
    Else
    	Dim OutApp As Outlook.Application
    	Dim OutMail As Outlook.MailItem
    	Dim MyReci As Outlook.Recipient
    
    	Set OutApp = New Outlook.Application
    	Set OutMail = OutApp.CreateItem(olMailItem)
    	With OutMail
    
    	.Recipients.Add ("Add Example Here")
    	.Subject = "High Priority Checks - OK"
    	.Body = "High Priority Checks Completed, with any errors"
    	.Display
    	End With
    End If

  3. The Following User Says Thank You to Paul T For This Useful Post:

    BGL (2015-11-10)

  4. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul T,

    Thank you for the aforementioned script. Regrettably, this does not resolve the issue I am being presented with.

    To elaborate on the above, when the check box is ticked a question will be asked, providing a yes or no answer, both answers will generate two different emails - only when the tick box has been selected.

    If the tick box has been ticked in error, if cleared I do not want the MsgBox to be presented.

    Thank you again

  5. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Change the vbYesNo to vbYesNoCancel then add this line above "If iResponse"
    If iResponse = 2 Then End Sub

    It's not very elegant but it gives you a starting point.

    cheers, Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •