Daisy80
asked on
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!
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!
ASKER
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!!
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.
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.
ASKER
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
<<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
Definitely so. In my environment the messages are generated and sent via a web application so they are constant (always the same).
OM Gang
ASKER
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!
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!
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
OM Gang
ASKER
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!
Thanks for the update.
OM Gang
OM Gang
ASKER
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?
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
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
ASKER
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.
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.
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.
ASKER
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!!!
ASKER
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
ErrorMsg.jpg
References.jpg
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
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
ASKER
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!
ASKER
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!
I'm glad it's working for you. Good luck with the rest of your project.
OM Gang
OM Gang
Sorry it can't be simpler...