+ Reply to Thread
Results 1 to 2 of 2

Excel Email Macro VBA - How to send an email once all required fields have been fille

  1. #1
    Registered User
    Join Date
    02-01-2012
    Location
    Lytham
    MS-Off Ver
    Excel 2007
    Posts
    2

    Excel Email Macro VBA - How to send an email once all required fields have been fille

    Im a beginner to Excel VBA Code but here's what Ive got so far:

    - Ive created a form that I want customers to fill out
    - Some fields have been coded as mandatory
    - The form can then be submitted by email straight to me by clicking on a button at the bottom of the form
    - I want the submit button to check all required fields have been completed before submitting the form
    - The below code already pops up an info box stating 'You must complete field X' if it is blank
    - The code also sends the email when all fields are completed.

    HOWEVER...

    If there is data missing from one of the required fields it still sends the email anyway rather than pausing at each missed point and allowing the user to fill in the data and then continuing to check the rest of the form - I wish this process to be looped until all fields required are completed - and only then should the email feature activate.

    Bacially an If/Then function to state - 'If all required fields complete Then Send email'

    I hope this made sense...

    Sub SendIt()

    If [D17].Value = "" Then
    MsgBox "There MUST be an entry in Full Name!", vbOKOnly, "Entry Reqd"
    [D17].Select
    Cancel = True
    End If

    If [D18].Value = "" Then
    MsgBox "There MUST be an entry in Contact Number!", vbOKOnly, "Entry Reqd"
    [D18].Select
    Cancel = True
    End If

    If [D19].Value = "" Then
    MsgBox "There MUST be an entry in Contact Email!", vbOKOnly, "Entry Reqd"
    [D19].Select
    Cancel = True
    End If

    If [D24].Value = "" Then
    MsgBox "There MUST be an entry in Date of Change!", vbOKOnly, "Entry Reqd"
    [D24].Select
    Cancel = True
    End If

    If [D27].Value = "" Then
    MsgBox "There MUST be an entry in Building Number/Name!", vbOKOnly, "Entry Reqd"
    [D27].Select
    Cancel = True
    End If

    If [D28].Value = "" Then
    MsgBox "There MUST be an entry in Address Line 1!", vbOKOnly, "Entry Reqd"
    [D28].Select
    Cancel = True
    End If

    If [D29].Value = "" Then
    MsgBox "There MUST be an entry in Address Line 2!", vbOKOnly, "Entry Reqd"
    [D29].Select
    Cancel = True
    End If

    If [D30].Value = "" Then
    MsgBox "There MUST be an entry in Town/City!", vbOKOnly, "Entry Reqd"
    [D30].Select
    Cancel = True
    End If

    If [D32].Value = "" Then
    MsgBox "There MUST be an entry in Postcode!", vbOKOnly, "Entry Reqd"
    [D32].Select
    Cancel = True
    End If

    If [D36].Value = "" Then
    MsgBox "There MUST be an entry in Previous Tenant/Owner!", vbOKOnly, "Entry Reqd"
    [D36].Select
    Cancel = True
    End If

    If [D39].Value = "" Then
    MsgBox "There MUST be an entry in Forwarding Address!", vbOKOnly, "Entry Reqd"
    [D39].Select
    Cancel = True
    End If

    If [D43].Value = "" Then
    MsgBox "There MUST be an entry in Contact Name!", vbOKOnly, "Entry Reqd"
    [D43].Select
    Cancel = True
    End If

    If [D44].Value = "" Then
    MsgBox "There MUST be an entry in Contact Number!", vbOKOnly, "Entry Reqd"
    [D44].Select
    Cancel = True
    End If

    If [D47].Value = "" Then
    MsgBox "There MUST be an entry in New Tenant/Owner!", vbOKOnly, "Entry Reqd"
    [D47].Select
    Cancel = True
    End If

    If [D50].Value = "" Then
    MsgBox "There MUST be an entry in Billing Address!", vbOKOnly, "Entry Reqd"
    [D50].Select
    Cancel = True
    End If

    If [D54].Value = "" Then
    MsgBox "There MUST be an entry in Contact Name!", vbOKOnly, "Entry Reqd"
    [D54].Select
    Cancel = True
    End If

    If [D55].Value = "" Then
    MsgBox "There MUST be an entry in Contact Number!", vbOKOnly, "Entry Reqd"
    [D55].Select
    Cancel = True
    End If

    If [D67].Value = "" Then
    MsgBox "There MUST be an entry in Utility Type!", vbOKOnly, "Entry Reqd"
    [D67].Select
    Cancel = True
    End If

    Application.Dialogs(xlDialogSendMail). arg1:="made@madeup.com", _
    arg2:="Completed Supply Transfer COT Form for <PLEASE INSERT SITE ADDRESS AND/OR MPAN/MPRN>"

    End Sub

  2. #2
    Registered User
    Join Date
    02-01-2012
    Location
    Lytham
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel Email Macro VBA - How to send an email once all required fields have been f

    Problem Solved.

    I added 'Exit Sub' just before the 'End If' of each of the defined mandatory fields e.g.

    If [D67].Value = "" Then
    MsgBox "There MUST be an entry in Utility Type!", vbOKOnly, "Entry Reqd"
    [D67].Select
    Cancel = True
    Exit Sub
    End If

    This way, the macro stopped after each failed mandatory field.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1