Is there a way to program Access to gather information from an e-mail? For example, if an e-mail came through with an order number in the subject line, is there a way to click a button in Access and have that number come over into the database, and perhaps the date the e-mail was received?
Question Gather Information
- Thread starter LadyDi
- Start date
c_smithwick
Underpaid Programmer
- Local time
- Today, 14:49
- Joined
- Jan 8, 2010
- Messages
- 102
You can't do it from Access, but you can do it from Outlook. Put the following in the "ThisOutlook Session" module of the Outlook Inbox you will be monitoring:
Modify it as neccessary. This should open your database and table of data, and add the info for you every time a new Mail Item is received in the Inbox
Code:
Option Explicit
Public db As DAO.Database 'Must have Microsoft DAO 3.6 Object Library referenced
Public WithEvents outItems As Outlook.Items
Public Sub Application_Startup()
Dim strDataPath As String
strDataPath = "ZZZ" 'replace this with the path to your database
Set db = CreateObject("DAO.DBEngine.36").Workspaces(0).OpenDatabase(strDataPath)
Set outItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub outItems_ItemAdd(ByVal outItem As Object)
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset(tblname, dbOpenDynaset) 'replace tblname with the name of your table enclosed in quotes
If TypeName(outItem) = "MailItem" Then
With rst
.AddNew
![OrderNo] = outItem.Subject 'manipulate the Subject string with whatever Mid or Instr manipulations you need to in order to extract the oreder number
![DateReceived] = outItem.RecievedTime
.Update
End With
End If
Set rst = Nothing
End Sub
Public Sub Application_Quit()
'free up memory
Set outItems = Nothing
db.Close
Set db = Nothing
End SubModify it as neccessary. This should open your database and table of data, and add the info for you every time a new Mail Item is received in the Inbox
Actully you can do this from Access using Outlook Automation. You will need to set a Reference to Outlook to gain access to the methods in Outlook.
You can then use VBA code to check for the existance of an email in a specific email folder. If an email is found in that folder, you can then read the various values from that email and process into your database as needed. Once you have processed the information you want or need to process, you simply move the email you have read to another email folder or delete it altogether. I prefer to just move the email to another folder so that in the event something unexpected happens, we would still have the original email and it could be processed again.
This process can be created in a loop that will continue to check for emails in the specified folder and process each one found until there are no more to be processed.
You will need to user the Timer event of a form to have your application check for emails at the desired interval or use some other event, say of a command button or the opening of a specific form, to trigger the checking for email.
You can then use VBA code to check for the existance of an email in a specific email folder. If an email is found in that folder, you can then read the various values from that email and process into your database as needed. Once you have processed the information you want or need to process, you simply move the email you have read to another email folder or delete it altogether. I prefer to just move the email to another folder so that in the event something unexpected happens, we would still have the original email and it could be processed again.
This process can be created in a loop that will continue to check for emails in the specified folder and process each one found until there are no more to be processed.
You will need to user the Timer event of a form to have your application check for emails at the desired interval or use some other event, say of a command button or the opening of a specific form, to trigger the checking for email.
Further to that here's the code you would need (assuming you have Access 2007):
'-----------------------------------------------------------------------------------------------
' Go through the e-mail accounts set up on Outlook and select the relevant one
'-----------------------------------------------------------------------------------------------
Dim sycs As outlook.SyncObjects
Dim syc As outlook.SyncObject
Set myNameSpace = outlook.Application.GetNamespace("MAPI")
Set myfolders = myNameSpace.Folders
Set sycs = myNameSpace.SyncObjects
'Loop through all accounts
For i = 1 To sycs.Count
Set syc = sycs.Item(i)
syc.Start
Next
n = 1
Do Until myNameSpace.Folders.Item
= "NAME OF THE ACCOUNT, E.G. JOE BLOGGS"
n = n + 1
Loop
'-----------------------------------------------------------------------------------------------
'Go through the folders set up under the selected account and select the relevant one
'-----------------------------------------------------------------------------------------------
Set myfolder = myfolders.Item
Set myfolder2 = myfolder.Folders("NAME OF THE FOLDER, E.G. INBOX")
'-----------------------------------------------------------------------------------------------
'Define the objects and variables to be used and reset the counter 'n' equal to 1
'-----------------------------------------------------------------------------------------------
Dim MailAttachments As Object
Dim Item As outlook.MailItem
Dim FileName As String
'-----------------------------------------------------------------------------------------------
' For each unread e-mail in the folder store the details to a variable
' - In the case of the receipient, CC, BCC, Subject & Body sections remove all inverted
' commas to prevent issues importing to table
' - In the case of the Unread flag set Boolean the value to a meaningful text value,
' i.e. Read or Unread
'-----------------------------------------------------------------------------------------------
For Each Item In myfolder2.Items
If Item.UnRead = True Then
MailSubject = Replace(Item.Subject, "'", "")
MailSender = Item.SenderEmailAddress
MailSenderName = Replace(Item.SenderName, "'", "")
MailRecipient = Replace(Item.To, "'", "")
MailCC = Replace(Item.CC, "'", "")
MailBCC = Replace(Item.BCC, "'", "")
MailSentDt = Item.CreationTime
MailReceivedDt = Item.ReceivedTime
MailPriorityCode = Item.Importance
MailBody = Replace(Item.Body, "'", "")
MailUnread = Item.UnRead 'This will be True if the item has not been read
If MailUnread = True Then
MailStatus = "Unread"
ElseIf MailUnread = False Then
MailStatus = "Read"
End If
If MailPriorityCode = 1 Then
MailPriority = "Normal"
ElseIf MailPriorityCode = 2 Then
MailPriority = "High"
ElseIf MailPriorityCode = 0 Then
MailPriority = "Low"
End If
'-----------------------------------------------------------------------------------------------
' Execute a SQL query to copy the data from the above variables into the table
' This also sets the Imported_Date value to the date and time the import was run.
'-----------------------------------------------------------------------------------------------
CurrentDb.Execute "INSERT INTO TABLE_NAME_HERE " _
& "(Subject, Sent_By, Sent_By_eMail_Address, Sent_To, CC_List, BCC_List, Date_Sent, Date_Received, Latest_Dt_Received, Body_of_Mail, Priority_of_Mail, Status_of_Mail, Date_Imported ) VALUES " _
& "('" & MailSubject & "', '" & MailSenderName & "', '" & MailSender & "', '" & MailRecipient & "', '" & MailCC & "', '" & MailBCC & "', '" & MailSentDt & "', '" & MailReceivedDt & "', '" & MailReceivedDt & "', '" & MailBody & "', '" & MailPriority & "', '" & MailStatus & "', '" & Now & "');"
'-----------------------------------------------------------------------------------------------
' Go through the e-mail accounts set up on Outlook and select the relevant one
'-----------------------------------------------------------------------------------------------
Dim sycs As outlook.SyncObjects
Dim syc As outlook.SyncObject
Set myNameSpace = outlook.Application.GetNamespace("MAPI")
Set myfolders = myNameSpace.Folders
Set sycs = myNameSpace.SyncObjects
'Loop through all accounts
For i = 1 To sycs.Count
Set syc = sycs.Item(i)
syc.Start
Next
n = 1
Do Until myNameSpace.Folders.Item
= "NAME OF THE ACCOUNT, E.G. JOE BLOGGS"n = n + 1
Loop
'-----------------------------------------------------------------------------------------------
'Go through the folders set up under the selected account and select the relevant one
'-----------------------------------------------------------------------------------------------
Set myfolder = myfolders.Item
Set myfolder2 = myfolder.Folders("NAME OF THE FOLDER, E.G. INBOX")
'-----------------------------------------------------------------------------------------------
'Define the objects and variables to be used and reset the counter 'n' equal to 1
'-----------------------------------------------------------------------------------------------
Dim MailAttachments As Object
Dim Item As outlook.MailItem
Dim FileName As String
'-----------------------------------------------------------------------------------------------
' For each unread e-mail in the folder store the details to a variable
' - In the case of the receipient, CC, BCC, Subject & Body sections remove all inverted
' commas to prevent issues importing to table
' - In the case of the Unread flag set Boolean the value to a meaningful text value,
' i.e. Read or Unread
'-----------------------------------------------------------------------------------------------
For Each Item In myfolder2.Items
If Item.UnRead = True Then
MailSubject = Replace(Item.Subject, "'", "")
MailSender = Item.SenderEmailAddress
MailSenderName = Replace(Item.SenderName, "'", "")
MailRecipient = Replace(Item.To, "'", "")
MailCC = Replace(Item.CC, "'", "")
MailBCC = Replace(Item.BCC, "'", "")
MailSentDt = Item.CreationTime
MailReceivedDt = Item.ReceivedTime
MailPriorityCode = Item.Importance
MailBody = Replace(Item.Body, "'", "")
MailUnread = Item.UnRead 'This will be True if the item has not been read
If MailUnread = True Then
MailStatus = "Unread"
ElseIf MailUnread = False Then
MailStatus = "Read"
End If
If MailPriorityCode = 1 Then
MailPriority = "Normal"
ElseIf MailPriorityCode = 2 Then
MailPriority = "High"
ElseIf MailPriorityCode = 0 Then
MailPriority = "Low"
End If
'-----------------------------------------------------------------------------------------------
' Execute a SQL query to copy the data from the above variables into the table
' This also sets the Imported_Date value to the date and time the import was run.
'-----------------------------------------------------------------------------------------------
CurrentDb.Execute "INSERT INTO TABLE_NAME_HERE " _
& "(Subject, Sent_By, Sent_By_eMail_Address, Sent_To, CC_List, BCC_List, Date_Sent, Date_Received, Latest_Dt_Received, Body_of_Mail, Priority_of_Mail, Status_of_Mail, Date_Imported ) VALUES " _
& "('" & MailSubject & "', '" & MailSenderName & "', '" & MailSender & "', '" & MailRecipient & "', '" & MailCC & "', '" & MailBCC & "', '" & MailSentDt & "', '" & MailReceivedDt & "', '" & MailReceivedDt & "', '" & MailBody & "', '" & MailPriority & "', '" & MailStatus & "', '" & Now & "');"
Thank you for the information.
I have one more question for you. Is there a way to gather information in an Access Database from a query in a separate program? Where I work, we have Access Databases and we have a program called QMF (Query Management Facility). I would like to be able to sync the two up. I would like to be able to press a button in Access and have it run a query in QMF and then take the data and put it in the corresponding fields on the form in Access. Is that possible?
I have one more question for you. Is there a way to gather information in an Access Database from a query in a separate program? Where I work, we have Access Databases and we have a program called QMF (Query Management Facility). I would like to be able to sync the two up. I would like to be able to press a button in Access and have it run a query in QMF and then take the data and put it in the corresponding fields on the form in Access. Is that possible?
I am not really familiar with the "QMF" tool. However, from what I could tell by just taking a quick look at their website, it is apparant that you can export the data from a query to a delimited file. Once you have the delimited file, you can import that dat into Access. I know you can automate the importing process. What I do not know is if there is any ways that a specific query can be run when the QMF application is opened. You will just have to investigate that one yourself. If you could run a spcific query from a command line, then it just might be possible to use the "FollowHyperlink" method from Access to open the QMF application and have a specific query run and then close the QMF application. As I said, I have zero info about that. Once the data is in a file in the delimited format, you definately can work with that in Access.
Similar threads
