Is it possible to assign 2 validation formulas to one field in an outlook form?

Basically we would like the subject field to require a number and but also allow text.  We do not want the email to be able to be sent unless there is a number somewhere in the subject field.

We are currently using the formula isnumeric ([Subject])=True to require the number but cant quite figure out how to allow text as well.  I figure there is a way to use an 'and' statement or even an alphanumeric variable that we may not be aware of.

This is our first go at getting further into outlook so any help is appreciated!
Infinity SolutionsData, Voice, Internet ServicesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
'151118, strive4peace
   Dim i As Integer _
      , boo As Boolean
      
   boo = False
   
   For i = 1 To Len(Item.Subject)
      If IsNumeric(Mid(Item.Subject, i, 1)) Then
         boo = True
         Exit For
      End If
   Next i
   If Not boo Then
      MsgBox "You must have a number in the subject before sending", , "Cancelling Send"
      Cancel = True
   End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Thank you, I will test this out shortly.  I assume this is input into the VBA project within outlook correct? Under ThisOutlookSession or Module1?

Edit:  Nevermind, I think I got it by putting it under 'thisoutlooksession'! I will do some more testing and report back.  Thank You!
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
It seems to work well.  I just noticed though that this now runs on every email and not just the form I created.  Is there a different section to put the code for it to run only on my form?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Let me do some testing ~
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
do you have an OK button on your form to do the sending?  If so, that is where the code would go.  If you post the code behind your form, I will put this in there (not Cancel = True though) ~

in the design view of the form, right-click on the form and choose View Code
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
There is no OK Button, just the default send one.  I just used the default message form template when designing my new form.  Nothing really needed changing but the requirement of the number in the subject field.

When hitting view code in the design view of the form it is blank.
Capture2.JPG
Previously I was posting the code you gave me in the 'ThisOutlookSession' section inside a VBA Project/Macro.  That had it working but had it working for every email even the emails not using our form.  Not sure if this was the right place to put it.  
Capture.JPG
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I need to know a bit more about your form so I can trigger the code to just check for the form ... perhaps you can publish it and attach it?

from the developer ribbon, select Publish and specify a folder you can find :)

thanks
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
For your form: do users click one button? all buttons? Is there a button they definitely click?

If so, code could be called from that button to assign a property to the mail item that the Application_ItemSend code I gave you could check for.  If the property is found, then the check could be done.  Otherwise, the check will not be done.

To see code for a specific button, go to the design view of the form and double-click on a button.

thanks!
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Im not sure exactly how to attach the form exactly, but it is just the message form template when you go to select the form.  It is the default email that comes up when a user hits new email.  I have just altered this and saved it as a separate form/template for our uses. To send the email they would hit the send button.
This is what it looks like when the form is run:
Capture3.JPG
and here it is in the design view
Capture4.JPG
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, John

I need a way to figure out how to distinguish use of the form as opposed to just composing a regular email ...

could you outline the steps you do differently from regular email when you are using the form?

thank you

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Sure thing crystal, Thank you for all the awesome help so far.

This form will be used to tag specific emails with a numerical value.  For instance,  Job A(doing the dishes for lack of a better example) will be given a number of 123.  Any email pertaining to this Job A throughout the company will have 123 typed into the subject by the user.  I prefer to use the subject field instead of creating a custom field so that mobile users can see the number as well.  Basically I wanted them to have the option of using the custom form that requires the number, and the ability to use the default mail form(That doesn't require a number) for other personal emails.

If this ends up not being possible that may be ok.  We will just have to inform them that a personal email code number that doesnt pertain to a job will need to be input.  If this happens to be the case, am I placing the code within the VB project in the correct place? The picture I attached a few posts earlier shows where I currently have it.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

thank you -- and yes, you did put it in the right place.  However, what we are looking for is some sort of trigger to get OUT of that code when it is just a regular message ... something like this:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
   if not triggerCondition = true then 
      exit sub
   end if

Open in new window

What I am looking for is a way to construct the triggerCondition so the code knows the difference between a regular email and an email you want tested.

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Could it be something including the form name/file path?




also I have to 'enable all macros' in the macro settings for outlook to run the original VB code you sent me. Is that correct?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

I might be able to cycle through the ribbon tabs and see if the form ribbon is open ... what is its name?

yes, macros will have to run -- you can see if "Notification for all macros" will work as well.

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
ok thanks :) it said not recommended next to enable all macros so I was just making sure I was doing it correctly/safely.

As for the name of the form i just used subjectjobform.oft when saving as a template.    And have moved the filepath around from the root of C: to the actual Templates folder messing around with things.  The actual form name in my personal forms library is a little different.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I think what is needed is the name of the ribbon tab that it creates since that is something that shows.

thanks, John

warm regards,
crystal

~ have an awesome day ~
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

I was hoping to do some more research on this today but it got too late ... I will look again tomorrow ~ or maybe someone else will jump in too

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
No problem at all crystal.  It had gotten late for us too. We appreciate any help!


But back on topic, I think I can change/make the ribbon buttons and stuff anything you wish.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

the best solution would be for your template to add a custom property that can be checked.  Otherwise, I do not see a way to know if the template is being used.  Here is a reference link:

https://www.add-in-express.com/creating-addins-blog/2011/08/19/how-to-add-a-custom-property-to-the-userproperties-collection-of-an-e-mail-item-in-outlook/

Application_ItemSend could then check and see if the mail item has this property.  If so, the check can be done.

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Thank you, Ill take a look.  I will go ahead and mark your initial code as a solution as i think it will work ok as is!

One more quick question if you don't mind.  Is there some code to also pre fill the subject line with 'Job Number:' ?
I know you can do it in the properties of the field on a custom form, but im now trying to get it all to work without needing a form at all, and to just use the default new email template.

I can resort to the form and making a custom ribbon button/macro if needed.

Thank You Crystal!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

thanks and you're welcome ~

I don't know how your template works, but here is code to fill outlook stuff from VBA:

sub Procedurename() '------------ customize
'151118 strive4peace
   on error goto Proc_Err

   Dim outApp As Object
   Dim outMsg As Object
   
   Set outApp = CreateObject("Outlook.Application")
   
   with outMsg 
      '.Importance = olImportanceHigh
       .To = sEmail
       '.CC = "cc email address"
       '.BCC = "cc email address"
        .Subject = "Your subject line"
        .Body = "Your message"
       ' .Attachments.Add sPathFile
       ' If you want to edit the message then uncomment Display and comment Send
       '.Display
        .Send  
   end with         
   
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set outMsg = Nothing
   Set outApp = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   Procedurename" 'CUSTOMIZE

   Resume Proc_Exit
   Resume
End Sub

Open in new window


obviously, if you are running this from outlook, you won't need outApp

warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
Sorry template is probably not the best way to say it.  I'm just using the default method to compose emails in outlook.  No custom form or anything other than the code you gave me.

As for the new code.
Im getting an error 91 Procedurename when running the project.

Sorry im not much of a programmer haha. I'm guessing something has to be defined in the () after it or somewhere else at least?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi John,

not sure where you put the code ... it should probably go into a general (standard) module.  Also, if you are putting it IN outlook, then don't use outapp so the top lines are:

   Dim outMsg As Object
   with outMsg 

Open in new window

and delete:
Set outApp = Nothing

Procedurename is just a placeholder for whatever you want to call it ...

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug, Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~ also be sure to use Option Explicit at the very top of each module so variables that are not declared or are misspelled will be picked up
Option Explicit ' require variable declaration

Open in new window


warm regards,
crystal

~ have an awesome day ~
Infinity SolutionsData, Voice, Internet ServicesAuthor Commented:
I was orginally placing the code above the original(for requiring a number) in 'ThisOutlookSession'.

I have moved it into its own module as shown below:
Capture6.JPG
and it pops this error:
Capture7.JPG
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oops! I left out a line when needs to be after DIM and before WITH
Set outMsg = outApp.CreateItem(0) '0=olMailItem

Open in new window


also, you should rename the procedure ;) ~
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.