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
300 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

17 Experts available now in Live!

Get 1:1 Help Now