Solved

In Outlook using VBA to work with Access data (open, append, delete, query, report) on XP Pro SP1

Posted on 2004-03-27
13
296 Views
Last Modified: 2011-09-20
I have working code to update an Excel spreadsheet from Outlook using VBA.  I urgently need the same code to work on an Access DB.  Here is the code:

Sub TestMessageRule(Item As Outlook.MailItem)
'Dim objExcel As Object ' Excel application
'Dim objBook As Object ' Excel workbook
'Dim objSheet As Object ' Excel Worksheet
Dim objAccess As Object ' Access application
'Dim objDB As Object ' Access DataBase
'Dim x, y, startrow As Integer

Set objAccess = CreateObject("Access.Application") 'Starts the Access Session
'Set objDB =
objAccess.OpenCurrentDatabase "c:\testme.mdb", False

 
'objAccess.DoCmd.TransferText acExportDelim, , "Alpha", "C:\testme.txt"
'objAccess.DoCmd.RunSQL "INSERT INTO MarApr SELECT ImportTemp.* FROM ImportTemp;  ", 0
'''objAccess.DoCmd.DeleteObject acTable, "Alpha" ' this works!
   
''Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
''Set objBook = objExcel.Workbooks.Add 'Add a Workbook

''Set objBook = objExcel.Workbooks.Open("c:\SSR.xls") 'Open a Workbook
''Set objSheet = objBook.Worksheets.Item(1) 'Select a Sheet

''lines = Split(Item.Body, vbCrLf) 'Get the data
''x = 2
''Do While (objExcel.Application.cells(x, 1) > "")
''x = x + 1
''Loop
''startrow = x

''x = 0
''y = UBound(lines)

''For x = 1 To y
 ''objExcel.Application.cells(startrow, x).Value = lines(x)
 ''If (x + 1 <= y) Then If InStr(1, lines(x + 1), "Note") > 0 Then x = y
''Next x

''objBook.Save

'objBook.SaveAs "c:\SSR.xls"
''objBook.Close
''objExcel.Quit


''Set objSheet = Nothing
''Set objBook = Nothing
''Set objExcel = Nothing



objAccess.Save
objAccess.Close
objAccess.Quit
Set objAccess = Nothing
End Sub

I've used double quotes to show Excel code.  The one Do.Cmd I was able to get working is Delete Table.  
The table is accessed, but no data changes.
Thanks in advance,
Russ in Baghdad
0
Comment
Question by:RUSS_EMI
  • 7
  • 6
13 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10694232
OK, lets give it a try:

Sub TestMessageRule(Item As Outlook.MailItem)
Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet
Dim x, y, startrow As Integer

DoCmd.TransferText acExportDelim, , "Alpha", "C:\testme.txt"
DoCmd.RunSQL "INSERT INTO MarApr SELECT ImportTemp.* FROM ImportTemp;  ", 0
DoCmd.DeleteObject acTable, "Alpha" ' this works!
   
Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
Set objBook = objExcel.Workbooks.Add 'Add a Workbook

Set objBook = objExcel.Workbooks.Open("c:\SSR.xls") 'Open a Workbook
Set objSheet = objBook.Worksheets.Item(1) 'Select a Sheet

lines = Split(Item.Body, vbCrLf) 'Get the data
'x = 2
Do While (objExcel.Application.cells(x, 1) > "")
x = x + 1
Loop
startrow = x

x = 0
y = UBound(lines)

For x = 1 To y
  objExcel.Application.cells(startrow, x).Value = lines(x)
  If (x + 1 <= y) Then If InStr(1, lines(x + 1), "Note") > 0 Then x = y
Next x

objBook.Save

objBook.SaveAs "c:\SSR.xls"
objBook.Close
objExcel.Quit


Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing

End Sub

Let me know where it goes wrong :-)

Nic;o)
The Netherlands
0
 

Author Comment

by:RUSS_EMI
ID: 10694304
Nic,
   Did you mean to delete the objAccess stuff.  I just execute the DoCmd code without referencing any specific access objects?
Russ
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10694373
Yes, basically all Access object stuff can be deleted as you're "in" access.

The docmd's will need the parms, but basically by starting the "c:\testme.mdb" the currentdb will be available with all tables and queries.

Nic;o)
0
 

Author Comment

by:RUSS_EMI
ID: 10694457
OK, I just want to sure I understand:  Even though I'm running VBA from OUTLOOK, if I start the database, all Access setup code is not needed?  Is my code for Excel redundant as well, as it is running in OUTLOOK?
Thank for the patience,
Russ
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10694498
Oops, misread your question.
I assumed you needed to have this code to manipulate an excel sheet from inside an Access database.
When you're running VBA under Outlook, you'll indeed need the Access object.

So remains effectively for me the questions:
1) What isn't working in your present code and what is the error message ?
2) Why use Outlook VBA and not run this from Access ?
    In access you can link a table to an outlook map and process the messages with queries and export to excel too.

Nic;o)
0
 

Author Comment

by:RUSS_EMI
ID: 10694518
Nic,
   On receipt of an email, the Rules Wizard runs this script, which parses the text in it to a spreadsheet and / or database as needed.  This is running unattended on a machine to track incoming message traffic and automatically update databases, charts, reports, etc.  As this is now fuctional for Excel, I don't want to start over.
Russ
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 54

Expert Comment

by:nico5038
ID: 10694575
OK, then lets concentrate on the AccessObject, where does it go wrong ?

Looks to me you want to have the same data as the excelsheet in the access database so a possibly "fast lane" would be just to append/insert the created excel sheet into Access.

Nic;o)
0
 

Author Comment

by:RUSS_EMI
ID: 10694687
Nic,
   In this case that is true, but for futher endeavors I would like not to involve Excel at all.
The delete table works fine.
The table is always opened, but never changed, or exported from / imported to.
No error messages ever occur.
This is really an urgent problem, I thank you for your efforts!
Russ
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10694734
Try to use:

objAccess.DoCmd.RunSQL "INSERT INTO MarApr SELECT * FROM Alpha;"

Looks to me the line:
objAccess.DoCmd.TransferText acExportDelim, , "Alpha", "C:\testme.txt"
Fills the table "Alpha" from the file "C:\testme.txt", but that text file will have to be filled !

Nic;o)
0
 

Author Comment

by:RUSS_EMI
ID: 10694751
Nic,
  Doesn't acExportDelim mean to extract the data to a text file?  I put that in as a sample to get anything to run - (import; export).
At this point I just want to see ANYTHING change or be copied to / from the Alpha table.  (Which is nothing more than primary key ID & the letter of the alphabet.
Please see if you can get any action to take place (except for "table delete" - that works).
Russ

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10694841
Just try the import parameter:
objAccess.DoCmd.TransferText acImportDelim, , "Alpha", "C:\testme.txt"

I'll give it a try myself, but that can take a while....

Nic;o)
0
 

Author Comment

by:RUSS_EMI
ID: 10694879
Thanks.  I have a testme.txt file ready for import.  I'll let you know.
Russ
0
 

Author Comment

by:RUSS_EMI
ID: 10694912
Nic,
  Nope just had the hourglass for a second or so.  No import of new data, no export, no save of db.
Russ
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now