• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Need some help with Access 2003's Visual Basic

N

nsafreak

Diamond Member
Ok folks I'm sure that there are other languages or programs that could get this done a lot easier without much hassle. However at this time Access 2003 is my only option. What I want to do is to have it extract information from the currently open email window in Outlook 2003 upon either a keycombo press event or a command button press event. Once it extracts the information I want it to put it in an access database table. The emails will have a format similar to this:

Order number: 553432
Company Name: Brighton Technology
Product Ordered: Mark 1 Widget
Quantity: 500
Price: $50


As you can see the field names will match the information to the left of the colon. Is it possible to do this in Access 2003's Visual Basic program or am I out of luck? Examples would be great as it has been a long long time since I have done any programming and the last programming I did was C++.
 
I don't know if it is possible to get the contents of an open email window, although given the completeness of the office automation interfaces I have no doubt there is a way to use Outlook to drive through an email box.

Seems like the hard way around, though. Why not just write a little script to open the user's email box, scan for messages that have a certain subject or certain content, and extract them either into a flatfile or directly into the database? I think even if they are on Exchange you should be able to use the basic POP3 transactions to get the content out.
 
Originally posted by: Markbnj
I don't know if it is possible to get the contents of an open email window, although given the completeness of the office automation interfaces I have no doubt there is a way to use Outlook to drive through an email box.

Seems like the hard way around, though. Why not just write a little script to open the user's email box, scan for messages that have a certain subject or certain content, and extract them either into a flatfile or directly into the database? I think even if they are on Exchange you should be able to use the basic POP3 transactions to get the content out.
Click to expand...

Well part of the problem is that the mail isn't stored in the user's inbox it's in a list that certain people have access to although this would include that user. Would a script be able to access other inboxes other than your own that you have permission to access?
 
The POP3 protocol supports an AUTH message that transfers the login credentials. It's been awhile since I've programmed to it, but I wouldn't try to do that now anyway. I'd look for a scriptable .Net object that you can use to get the messages out and then drive them into Access.

Here's a free one with source code: http://www.codeproject.com/KB/IP/NetPopMimeClient.aspx
 
Originally posted by: Markbnj
The POP3 protocol supports an AUTH message that transfers the login credentials. It's been awhile since I've programmed to it, but I wouldn't try to do that now anyway. I'd look for a scriptable .Net object that you can use to get the messages out and then drive them into Access.

Here's a free one with source code: http://www.codeproject.com/KB/IP/NetPopMimeClient.aspx
Click to expand...

Well that would probably work but there's one minor problem. The Exchange server that the user's mail is on is not setup for POP access at all. And I have no control whatsoever over the Exchange server.
 
I created a table called tblOrder with the exact same fieldnames(Order Number, company Name, etc). I had to add a reference to the outlook object library(should be called "Microsoft Outlook 11.0 Object" Library in Office 2003). You'll also need a reference to ActiveX Data Objects 2.8.

Based on what message is selected(or even multiple messages) in the messages view, it reads the body of the message, parses it for the CR/LF, parses it again for the ":" then inserts a new record and sets the fieldvalues accordingly. Just copy and paste the code into a new module, and type in OutlookInsert in the immediate window to test from the VBA code editor.

Yes I am bored. 🙁

EDIT: I see the attach code is still not working :thumbsdown:

______
Function OutlookInsert()
On Error GoTo Err_OutlookInsert
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim strParsed As Variant, strParsed2 As Variant
Dim i As Long, i2 As Long
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentProject.Connection
cn.Open
Set rs = New ADODB.Recordset
rs.Open ("tblOrder"), cn, adOpenDynamic, adLockOptimistic
If ActiveExplorer.Selection.Count > 0 Then
For i2 = 1 To ActiveExplorer.Selection.Count
Set objMail = ActiveExplorer.Selection(i2)
strbody = objMail.Body
strParsed = Split(strbody, vbCrLf)
rs.AddNew
For i = 0 To UBound(strParsed)
If strParsed(i) <> "" Then
strParsed2 = Split(strParsed(i), ":")
rs.Fields(strParsed2(0)) = strParsed2(1)
End If
Next i
rs.Update
Next i2
End If
MsgBox "Complete"

Exit_OutlookInsert
Exit Sub

Err_OutlookInsert:
MsgBox Err.Description
Resume Exit_OutlookInsert

End Function
______________________________
 
Originally posted by: KLin
I created a table called tblOrder with the exact same fieldnames(Order Number, company Name, etc). I had to add a reference to the outlook object library(should be called "Microsoft Outlook 11.0 Object" Library in Office 2003). You'll also need a reference to ActiveX Data Objects 2.8.

Based on what message is selected(or even multiple messages) in the messages view, it reads the body of the message, parses it for the CR/LF, parses it again for the ":" then inserts a new record and sets the fieldvalues accordingly. Just copy and paste the code into a new module, and type in OutlookInsert in the immediate window to test from the VBA code editor.

Yes I am bored. 🙁

EDIT: I see the attach code is still not working :thumbsdown:

______
Function OutlookInsert()
On Error GoTo Err_OutlookInsert
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim strParsed As Variant, strParsed2 As Variant
Dim i As Long, i2 As Long
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentProject.Connection
cn.Open
Set rs = New ADODB.Recordset
rs.Open ("tblOrder"), cn, adOpenDynamic, adLockOptimistic
If ActiveExplorer.Selection.Count > 0 Then
For i2 = 1 To ActiveExplorer.Selection.Count
Set objMail = ActiveExplorer.Selection(i2)
strbody = objMail.Body
strParsed = Split(strbody, vbCrLf)
rs.AddNew
For i = 0 To UBound(strParsed)
If strParsed(i) <> "" Then
strParsed2 = Split(strParsed(i), ":")
rs.Fields(strParsed2(0)) = strParsed2(1)
End If
Next i
rs.Update
Next i2
End If
MsgBox "Complete"

Exit_OutlookInsert
Exit Sub

Err_OutlookInsert:
MsgBox Err.Description
Resume Exit_OutlookInsert

End Function
______________________________
Click to expand...


You my friend get a few :beer: :beer: on me. Thanks! 🙂

 
Originally posted by: KLin
I created a table called tblOrder with the exact same fieldnames(Order Number, company Name, etc). I had to add a reference to the outlook object library(should be called "Microsoft Outlook 11.0 Object" Library in Office 2003). You'll also need a reference to ActiveX Data Objects 2.8.

Based on what message is selected(or even multiple messages) in the messages view, it reads the body of the message, parses it for the CR/LF, parses it again for the ":" then inserts a new record and sets the fieldvalues accordingly. Just copy and paste the code into a new module, and type in OutlookInsert in the immediate window to test from the VBA code editor.

Yes I am bored. 🙁

EDIT: I see the attach code is still not working :thumbsdown:

______
Function OutlookInsert()
On Error GoTo Err_OutlookInsert
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim strParsed As Variant, strParsed2 As Variant
Dim i As Long, i2 As Long
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentProject.Connection
cn.Open
Set rs = New ADODB.Recordset
rs.Open ("tblOrder"), cn, adOpenDynamic, adLockOptimistic
If ActiveExplorer.Selection.Count > 0 Then
For i2 = 1 To ActiveExplorer.Selection.Count
Set objMail = ActiveExplorer.Selection(i2)
strbody = objMail.Body
strParsed = Split(strbody, vbCrLf)
rs.AddNew
For i = 0 To UBound(strParsed)
If strParsed(i) <> "" Then
strParsed2 = Split(strParsed(i), ":")
rs.Fields(strParsed2(0)) = strParsed2(1)
End If
Next i
rs.Update
Next i2
End If
MsgBox "Complete"

Exit_OutlookInsert
Exit Sub

Err_OutlookInsert:
MsgBox Err.Description
Resume Exit_OutlookInsert

End Function
______________________________
Click to expand...



Well the code looks good to me but something must be going wrong with the email. I keep getting the error message Item cannot be found in the collection corresponding to the requested name or ordinal but all of the field names are in the database that correspond with the email. Is there anything else that would cause that problem?
 
I assume you're using Outlook 2003 correct? You need to highlight the order email in outlook, then run the code above.
 
Did you add a reference to the outlook object library? It's under tools/references in the VBA editor.
 
Can you post a screenshot link to the error? I've tested it in Office 2007 and Office xp without any issues.
 
Function OutlookInsert()
On Error GoTo Err_OutlookInsert
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim objMail As Outlook.MailItem
Dim strbody As String
Dim strParsed As Variant, strParsed2 As Variant
Dim i As Long, i2 As Long
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentProject.Connection
cn.Open
Set rs = New ADODB.Recordset
rs.Open ("tblOrder"), cn, adOpenDynamic, adLockOptimistic
If ActiveExplorer.Selection.Count > 0 Then
For i2 = 1 To ActiveExplorer.Selection.Count
Set objMail = ActiveExplorer.Selection(i2)
strbody = objMail.Body
strParsed = Split(strbody, vbCrLf)
rs.AddNew
For i = 0 To UBound(strParsed)
If strParsed(i) <> "" Then
strParsed2 = Split(strParsed(i), ":")
rs.Fields(strParsed2(0)) = strParsed2(1)
End If
Next i
rs.Update
Next i2
End If
MsgBox "Complete"

Exit_OutlookInsert:
Exit Function

Err_OutlookInsert:
MsgBox Err.Description
Resume Exit_OutlookInsert

End Function


I found a couple of small issues. I forgot a colon after the Exit_outlookinsert and I was telling it to Exit Sub instead of Exit Function.
 
Yeah I had to do some modifications to your code because apparently I didn't know how the emails really were formatted. I thought they were straightforward but there were a few cases where there was a space before the colon and there were lines that were not to be read into the database.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top