Submit button coding

[Jlombard_]

Hi guys,

I'm trying to code a submit button for my VBA userform although I'm a bit of a novice and this is a bit of a complex project!

Here's what I'd like the submit button to do when clicked:

1) Send a notification email to abc@xyz.com, subject "new AOK has been submitted!", Body: "We've received a new AOK! go to I:\#IIS BRANDS\AOK\AOK submissions.xlsx for full details"

2) If a control property called "TextBox1" has already had the same value inputted it will reject it with a message saying "this customer has already had a AOK submitted!"

3) If the mandatory fields aren't filled in a message appears saying "please complete all mandatory forms" (if we can highlight the fields that require text that would be cool too)

4) I'd then want the data submitted pulled through into a separate worksheet called "AOK Submissions" where a new row is added when new data is submitted so new submissions are always at the top.

5) The form has to work locally, however, when the data is submitted that will pull through into a workbook which is under a shared folder drive - apparently this makes a difference.

6) this form will have over 100 people with access to it, when multiple submissions are made at the same time I've been told it can cause issues therefore would it be possible to set up a check to see if the new data has been pulled into the AOK submissions sheet, if not a message displays asking the user to click resubmit.

7) after a form is successfully submitted a message will display saying "your AOK has been successfully submitted" - once they click okay on this message the form itself (and the sheet it is in) will automatically close.

Is this something anyone can help me with? I appreciate it's a lot so any help where I can get it will be fantastic!

 

[smpatty08]

1) I am assuming you are using Microsoft Outlook as your email client.

Sub email()
'Must add references (Alt+F11 > Tools > References) to
' 1) OLE Automation
' 2) Microsoft Outlook xx.0 Object Library
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem


'Set variables
Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)


'Sets email address for notifications recipient
myMail.To = "YOUR EMAIL HERE"
'Sets email Subject
myMail.Subject = "New AOK has been submitted!"
'Sets email Body
myMail.HTMLBody = "We've received a new AOK! Go to I:\#IIS BRANDS\AOK\AOK submissions.xlsx for full details" 'uncomment this if you want a formatted body


'Display email in Outlook
'myMail.Display True
'Sends email
myMail.Send
End Sub

Everything else will require much more detail before anybody will be able to help.

2) We need to know where "TextBox1" will be looking for existing text

3) We need to know how many fields of data the UserForm will contain and which ones specifically are mandatory

4) This should be simple once we know how many fields of data are in the UserForm and in what column order you want them in on Sheet("AOK Submissions")

5 & 6) This will be tricky because only one person will be able to have the Workbook open at one time and this can create problems if people forget to close it. You would probably have to save as a Microsoft Office Template file so that multiple people could have it open at the same time.

7) This code will close the Active Workbook without saving. If you want to save the change False to True

ActiveWorkbook.Close False

You may want to try to build this one step at a time over multiple posts and only after you have thought in detail about how you want the Workbook to look and function. Hope this helps you get started!

 

[Kyle123]

It sounds like you should be using access, is there a reason you aren't?