MS-Access - select part of a string from email file

userRX701 0
Viewed 21

I have set up a student database in ms-access 2016 to track students on support to study and imported all the emails from a folder related to students into a database called emails (I still need to work out the VBA code to update this folder on opening/closing a form). When a student emails me, I can match that email easily to the main database as it is just from them. However, when an email is sent to a student, often other staff members are cc'd in to the email. I therefore need to be able to select out just the student's email name from the To column. So it may be Jo Smith; Leah Jones; Jo Davies (UG) (or any combination of that depending on if the student is the main recipient or not. The name of the student will always have (UG) at the end of the name, so can be identified.

Is there a way of selecting out just the student name (with the (UG) at the end is fine) from the string of names into a new column using an update column? The recipient names are separated by a ; (but if the student is first or last on the list obviously won't have a ' before/after their name). I need this to link it to the name so I can see emails sent to and from the student in forms, alongside their other information.

Many thanks in advance Ali

ms-access
1 Answers

It's been over a decade since I wrote an Access VBA UDF and probably a year or 5 since I wrote VBA, so grain of salt here, but this should be in the ballpark:

Function getStudentEmail(emails as String) as String

    'Declare array to hold emails
    Dim emailsArr() as String

    'Split delimited string of emails into the array with split() function
    emailsArr = Split(emails, ";")
    
    'Loop through array
    For Each email in EmailsArr
        'Check if email string has (UG) in it
        If InStr(1, email, "(UG)", 1) Then
            'Set the return to the email and break the for loop ending the function
            getStudentEmail = email
            Exit For
        End If
    Next email

End Function

How to use:

  1. Open up the Visual Basic Editor (VBE) with the "Visual Basic" button in the ribbon under the "Database Tools" section:

    enter image description here

  2. Right-click on your database in the "project" pane and choose Insert>>Module:

    enter image description here

  3. Paste your code in:

    enter image description here

  4. Now you should be able to use this function in your SQL like SELECT getStudentEmail(yourEmailColumn) FROM yourtable;

JNevill0
Related