?
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
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

771 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