I was looking for a way to have Outlook automatically take an attachment (containing a form) and enter it into an Access DB. Ideally the users could send an email to a pre-set email address, and outlook would "check the mail" and enter the info from the attachment directly into the Access DB.
I have done something similar, but it used Excel as a template. We used Excel as everyone is happy with it, and alot of staff DO NOT know access and are affraid of forms !
I had to create VBA module in outlook for 'on new message' this then searched my inbox, and any excel workbooks it placed into a specified directory. you can also put a macro button on an outlook toolbar to 'force this procedure to run' ~ Incase it doesnt do it automatically.
Then I used a call routine in outlook to open the database, the database had a main form that had a timer, if no action was taken within 10 seconds it would run a routine to upload the excel templates.
It checked each excel workbook in that drive for a key element to make sure that it was a template to upload, then it uploaded the data.
It would then run a few reports and email various management, then it would delete all the excel spreadsheets and close itself..Probably one of the coolest databases I wrote..
Heres the code from outlook
Private Sub Application_NewMail()
Dim myFolder As Outlook.MAPIFolder
Set myOlApp = CreateObject("Outlook.application")
Set myFolder = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFo lderInbox)
Set myitem = myOlApp.CreateItem(olMailItem)
For A = 1 To myFolder.Items.Count
If myFolder.Items(A).UnRead = True Then
If myFolder.Items(A).Attachments.Count <> 0 Then
For b = 1 To myFolder.Items(A).Attachments.Count
If UCase(Right(myFolder.Items(A).Attachments(b).Displ ayName, 3)) = "XLS" Then
Make sure you have a folder that exsists !! In the example above mine is C:\new email otherwise it'll give you an error..
Please also note, it renames the workbooks by with the name of the sender and a current date.
I'm still trying to find the database, I have a feeling I might have deleted it , but I didn't clean out my outlook VBA ! - lucky or what...
Anyways, if you do decide to do this, you will need a loop in your code in Access to read all the files within the folder, and check a cell for a specific key value. My Excel template was a single sheet, that had certain locked cells, my key value was something like. Inventory Template for Stores (in cell A1).
When the loop found this, it uploaded the information.
Finally, dont forget to clean the folder out. otherwise it will upload all the spreadsheets again.
Thats if you do it this way.
If you need any other suggestions or help let me know.