• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

Update Access Database Automatically using Email Message

Hi all-

I've been stuck on this one for a while now & have probably exhausted all of my help files & related web sites found via google.  I have an access db that currently has a button on a form where the user can click & it automatically sends an email to all managers listed in a table.  The email has a list of applications for which the employees under him/her has access to.  The manager has to reply to the email with a yes/no next to each application in order to determine if access should be revoked.  

We currently have it so that when the replies from the managers are received, the users have to manually update the database accordingly as to whether or not revoke access to an application for the given employee.  (This is currently a form they update that pulls from a simple field in a table that has yes/no to revoke).  

My question & new mission given by my boss, how can I set it up so that the database gets updated automatically with the yes/no responses from the email?  Does anybody maybe have any sample VBA code that will allow me to automate the creation, sending & receiving of emails?

I know that '07 offers a new functionality to Create Email, but this wizard is too complex for the users I am working with & this all needs to be done behind the scenes with the main prompt being to choose the managers or department to send it too.

I know I have my work cut out for here b/c I'd imagine this isn't going to be easy.... Any help/advice you could give would be GREATLY appreciated!

Thanks!
0
Daisy80
Asked:
Daisy80
  • 10
  • 8
  • 2
  • +1
1 Solution
 
mmarx82Commented:
If you know web programing the thing to do would be send out your email with a yes link and a no link to a website, then program your website to make those updates in the database.
Sorry it can't be simpler...
0
 
Daisy80Author Commented:
oh boy...so you don't think this can be done w/o a website?  I was afraid of this.  I guess I'll have to look down this avenue.  Well many thanks for your help & quick response!!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
InfoPath can do this to some extent, although it seems to be a bit buggy at times. I've never used it so can't really comment any further, but it is fairly well integrated in 2007.

If you cannot use InfoPath for some reason then you could possibly monitor Outlook (assuming you're using Outlook) to determine when a new object comes into OUtlook. If you setup a Rule to move your desired email to a specific folder, then Access can just poll that folder until something new arrives, then open the email object and try to parse the Body property. Whether this works or not would depend in large part on your users, since you would almost have to have a properly formatted email message in order to correctly parse it.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Daisy80Author Commented:
Yes I do have outlook.  That is a very interesting & creative approach.  You may be onto something here!  Let me play around with this idea.  Thank you so much for that suggestion!
0
 
omgangCommented:
Definitely possible to update/append data to your database from email messages in Outlook.  I've attached some sample code that does similar to what you want.  The Outlook/Exchange mailbox is attached to the Access app as a linked table.  The function iterates through each message and only processes those with specific subject text.  The function parses the message body (calls a number of other functions to perform the parsing) and finally injects a record into a MsSQL database.  For your purposes you'd probably want to add some functionality to move the message to a different email folder once processed.  In my example, all messages processed are formatted exactly the same (submitted by an online .NET application).  It sounds like this will be the same in your environment since the users will be submitting a pre-designed form.

OM Gang
Public Function ParseMessage()
On Error GoTo Err_ParseMessage

    Dim rsDest As ADODB.Recordset
    Dim rsSource As DAO.Recordset
    Dim blContact As Boolean, blResult As Boolean
    Dim dteRecvd As Date
    Dim intRecCount As Integer, intCurPos As Integer, intNextPos As Integer
    Dim intFieldCnt As Integer, intCatCount As Integer, intSeq As Integer
    Dim intCategory As Integer
    Dim lngID As Long
    Dim strMsg As String, strSubj As String, strSourceTbl As String
    Dim strCurText As String, strDestTbl As String, strNotice As String
    Dim strRetText As String, strSQL As String
    Dim arrVals(20) As Variant   '<--- this is an array to hold all the parsed values you wish to retrieve from the message body
                                  'you need to size it accordingly
    
         'these are text strings present in every message being processed in my environment so I assigned them to constants
    Const LONGDASH As String = "------------------------------"
    Const LONGDASHCNT As Integer = 30
    Const SHORTDASH As String = "---------------"
    Const SHORTDASHCNT As Integer = 15

    strSourceTbl = "MessageInbox"   '<----- name of Outlook/Exchange linked table in your Access app
    strDestTbl = "TestSubmission"

    strSubj = "This is the subject text of the messages you want to process"
    
    Set rsSource = CurrentDb.OpenRecordset(strSourceTbl)
    intRecCount = 0

    rsSource.MoveFirst
    Do Until rsSource.EOF
        If Not rsSource!Subject Like strSubj & "*" Then
            GoTo NextRecord
        End If
        
        'intFieldCnt = 1

        strMsg = rsSource("Contents")    '<---- here we are grabbing the value of the Contents field from the recordset (message body)
        
            'flush array of all existing values
        For intFieldCnt = 0 To UBound(arrVals)
            arrVals(intFieldCnt) = 0
        Next intFieldCnt
    
            'here we iterate through the expected recordset values we want to retrieve and select specific sections of the message
            'to send to the individual parsing functions
        For intFieldCnt = 1 To 11
            Select Case intFieldCnt
                Case 1      'date/time message submitted
                    intCurPos = InStr(strMsg, vbCr)
                    strCurText = Left(strMsg, intCurPos - 1)
                    strCurText = Mid(strCurText, InStr(strCurText, ",") + 2)
                        'call function to convert string value to date
                    dteRecvd = ConvertToDate(strCurText)
                    If dteRecvd = #9/9/1399# Then
                        strNotice = "Invalid Date value in message.  Here's the text:"
                        strNotice = strNotice & vbCrLf & strCurText
                        MsgBox strNotice, vbCritical, "Operation Aborted"
                        GoTo Exit_ParseMessage
                    End If
                    
                        'only import records more recent then this specified date/time stamp
                    If dteRecvd <= #2/4/2010 10:46:07 AM# Then GoTo NextRecord   '<--- you probably don't need this
                    
                    arrVals(intFieldCnt - 1) = dteRecvd
                    'Debug.Print dteRecvd
                
                Case 2      'can be contacted?
                    intCurPos = InStr((intCurPos), strMsg, LONGDASH) + LONGDASHCNT
                    intCurPos = InStr(intCurPos, strMsg, LONGDASH) + LONGDASHCNT + 2
                    intNextPos = InStr(intCurPos, strMsg, SHORTDASH) - 2
                    strCurText = Mid(strMsg, intCurPos, (intNextPos - intCurPos))
                        'call function to covert string value to boolean
                    blContact = ConvOKToContact(strCurText)
                    arrVals(intFieldCnt - 1) = blContact
                    'Debug.Print blContact
                
                Case 3, 4, 5, 6, 7   'contact info
                    intCurPos = InStr(intNextPos, strMsg, ":") + 2
                    intNextPos = InStr(intCurPos, strMsg, vbCr)
                    strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                        'call function to process string value
                    strRetText = ProcSubmissionText(intFieldCnt, strCurText)
                    arrVals(intFieldCnt - 1) = strRetText
                    'Debug.Print strCurText
                    
                Case 8      'city, state zip
                    intCurPos = InStr(intNextPos, strMsg, ":") + 2
                    intNextPos = InStr(intCurPos, strMsg, vbCr)
                    strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                    For intSeq = 1 To 3
                            'call function to process string value
                        strRetText = ProcSubmissionText(intFieldCnt, strCurText, intSeq)
                        arrVals(intFieldCnt + intSeq - 2) = strRetText
                    Next intSeq
                    'Debug.Print Mid(strMsg, intCurPos, intNextPos - intCurPos)
                    
                Case 9      'company employee?
                    intCurPos = intNextPos + 2
                    intNextPos = InStr(intCurPos, strMsg, vbCr)
                    strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                        'call function to convert string value
                    strRetText = ConvertCompEmpl(strCurText)
                    arrVals(intFieldCnt + 1) = strRetText
                    'Debug.Print strCurText

                Case 10     'categories
                    For intCatCount = 1 To 10
                        Select Case intCatCount
                            Case 1      'first category value
                                intCurPos = InStr(intNextPos + LONGDASHCNT, strMsg, SHORTDASH) + SHORTDASHCNT + 2
                                intNextPos = InStr(intCurPos, strMsg, vbCr)
                                strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                                
                            Case Else   'all other category values
                                intCurPos = intNextPos + 2
                                intNextPos = InStr(intCurPos, strMsg, vbCr)
                                strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                            
                        End Select
                        If strCurText = LONGDASH Then Exit For
                            'call function to convert string value
                        intCategory = ConvertCategoryText(strCurText)
                        If intCategory = 99 Then GoTo Exit_ParseMessage
                        arrVals(intCategory) = True
                        'Debug.Print strCurText
                    Next intCatCount
                    
                Case 11     'body
                    intCurPos = intNextPos + 2
                    intCurPos = InStr(intCurPos, strMsg, SHORTDASH) + SHORTDASHCNT + 2
                    intNextPos = InStr(intCurPos, strMsg, LONGDASH)
                    strCurText = Mid(strMsg, intCurPos, intNextPos - intCurPos)
                        'call function to process text
                    strRetText = ProcSubmissionText(intFieldCnt, strCurText)
                    arrVals(20) = strRetText
                    'Debug.Print strCurText
        
            End Select
        Next intFieldCnt

        'For intRecCount = 0 To 20
        '    Debug.Print intRecCount & ":  " & arrVals(intRecCount)
        'Next intRecCount
        
            'only insert record into table if it doesn't already exist
        Set rsDest = New ADODB.Recordset
        strSQL = "SELECT * FROM " & strDestTbl & " WHERE SubmissionDate = #" & arrVals(0) & "# AND Email = " & Chr(34) & arrVals(4) & Chr(34)
        rsDest.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
        'Debug.Print strSQL
        'GoTo Exit_ParseMessage
             'here we insert a new record into the database table.  You could also update an existing record
        If rsDest.RecordCount = 0 Then
                'no matching record found
            blResult = InsertRecord(arrVals, strDestTbl, lngID)
                'increment lngID
            lngID = lngID + 1
        End If
        Set rsDest = Nothing
        
NextRecord:
        intRecCount = intRecCount + 1
        'If intRecCount = 1 Then Exit Do
        rsSource.MoveNext
    Loop

Exit_ParseMessage:
    Set rsDest = Nothing
    Set rsSource = Nothing
    Exit Function

Err_ParseMessage:
    MsgBox Err.Number & ", " & Err.Description, , "Error in function ParseMessage"
    Resume Exit_ParseMessage

End Function

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
the code by omgang should get you going; my only issue with this process would be the possibility of users munging up the email format (i.e. adding text into the body, etc) which could mess up your parsing processes.
0
 
omgangCommented:
<<my only issue with this process would be the possibility of users munging up the email format (i.e. adding text into the body, etc) which could mess up your parsing processes>>

Definitely so.  In my environment the messages are generated and sent via a web application so they are constant (always the same).
OM Gang
0
 
Daisy80Author Commented:
omgang-  Thank you SO much for sharing this code!!!  Do you know if this will work for prior versions such as 2003?  I'm taking a look at it now & try to alter for my needs.  

Also fyi- i stumbled upon the following website which i was just looking at: http://blogs.msdn.com/access/archive/2009/02/09/automate-data-collection-forms-using-vba.aspx .  it involves altering the MSysDataCollection file in Access as well as the AccesDCActionFile.xml in outlook.  I was going to try this method, but you're code looks a lot easier to tackle, so many thanks again!  Stay tuned!
0
 
omgangCommented:
I'm currently using it in Access 2003.  I don't believe there is anything in there that wouldn't work just fine in Access 2000 or XP (my mdb file format is Access 2000).
OM Gang
0
 
Daisy80Author Commented:
omgang- I just wanted to let you know that I got side-swiped on another project yesterday, so I didn't have a chance yet to test out your code.  I plan on doing so shortly & will keep you posted.  Many many thanks again for your help!
0
 
omgangCommented:
Thanks for the update.
OM Gang
0
 
Daisy80Author Commented:
omgang- i'm having some trouble w/ your code.  i'm getting the error "user-defined type not defined" right at the beginning following line: "im rsDest As ADODB.Recordset".  Again, I apologize b/c I am new to vba.  is there maybe a reference I need to enable for it to recognize this command or am I doing something wrong?  
0
 
omgangCommented:
You need to set a reference to the ADO object library.
While in the VBE (Visual Basic Editor) go to the Tools menu and select references.  You need to scroll down the list of available references until you find
Microsoft ActiveX Data Objects x.x library.  Select it and then click the OK button.  Choose Compile from the Debug menu afterwards.

OM Gang
AccessReferences.jpg
0
 
Daisy80Author Commented:
that definately did the trick- thanks so much!!  Now another stupid question.  You say that the Outlook/Exchange mailbox is attached to the Access app as a linked table.  Now, can I set it so that the access table would be linked to any user that is currently using the database or does it have to be a static email address of 1 person?  There will be multiple users using this db where they'll have to have the ability to use access to send emails & update the db w/ the information in the emails received.
0
 
omgangCommented:
You've got some options here.
Assuming the application is split into a front-end app and back-end database:

The simplest is to re-link the Outlook table for each user that is going to be using the application.  Install the front-end app onto the users workstation and re-link the MessageInbox table to the users Outlook Inbox.  This typically only needs to be done the first time you install the app onto the user workstation and each time you update the app with a newer version.  No changes to the procedure are required.

You can also develop a routine to automatically relink the table.  Seems like a bit of work for something that doesn't need to be done too often.

You can also change the process to fully automate MS Outlook, prompting the user for the mailbox/profile and mailbox folder the messages are in.  This makes the app flexible in the sense you can hand it out to your users and not have to worry about re-linking tables etc.

I like simple.
0
 
Daisy80Author Commented:
hmmm... i like your thoughts- very creative.  let me play around with this & see which options best suit my needs.  many thanks again for your continued help with this!!!
0
 
Daisy80Author Commented:
Hi there again omgang. Sorry for all of the questions, but for some reason it's bombing out at the ConvertToDate command.  It's giving me the Sub or Function Not Defined error.  I made sure that all of my references mirrored yours, but it didn't do the trick.  Also, I'm using Access 2007- does that make a difference?  I've attached both the error message as well as all of my active references I have enabled.  Do you know what I'm doing wrong?


 

ErrorMsg.jpg
References.jpg
0
 
omgangCommented:
ConvertToDate is a custom function I wrote for my application.  It takes text from the email message body and returns the value as a date format.  You may, or may not, need this functionality in your environment.  The text string for dae/time submitted in the messages (my environment) is like
Thursday, March  25, 2010, 11:19:54 AM
The Outlook linked table has a field Received that contains a date value.  You may be able to use this value for your purposes and not need the function call.  In my environment, I wanted the date/time the web app submitted the message (which was written into the message body) as opposed to the date/time it was received by Exchange.

I've attached the function just in case.
OM Gang
Public Function ConvertToDate(strDateText As String) As Date
On Error GoTo Err_ConvertToDate

    Dim intPos As Integer
    Dim dteDateRet As Date
    Dim strNewText As String

    intPos = InStr(strDateText, ",")
    intPos = InStr(intPos + 1, strDateText, ",")
    strNewText = Left(strDateText, intPos - 1) & Right(strDateText, Len(strDateText) - intPos)
    dteDateRet = CDate(strNewText)

Exit_ConvertToDate:
        'return value
    ConvertToDate = dteDateRet
    Exit Function

Err_ConvertToDate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in function ConvertToDate"
        'error return value
    dteDateRet = #9/9/1399#
    Resume Exit_ConvertToDate

End Function

Open in new window

0
 
Daisy80Author Commented:
hi omgang- I need to apologize to you for the long delay.  I've since got pulled on a different project, but upon returning, I was able to use your code & after playing around w/ it for a bit, I was able to modify it to fit my needs.  It's still sort of a work in process, as this was a bit over my head at first.  But, thank you so much for sharing this code w/ me as it does work very nicely.  Thanks again!
0
 
Daisy80Author Commented:
I only said partially complete b/c I had to play around w/ it to get it to parse the messages to my needs. But, you had no way of knowing exactly what we were after.  Many thanks again for your help!
0
 
omgangCommented:
I'm glad it's working for you.  Good luck with the rest of your project.
OM Gang
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 10
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now