• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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

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
RUSS_EMI
Asked:
RUSS_EMI
  • 7
  • 6
1 Solution
 
nico5038Commented:
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
 
RUSS_EMIAuthor Commented:
Nic,
   Did you mean to delete the objAccess stuff.  I just execute the DoCmd code without referencing any specific access objects?
Russ
0
 
nico5038Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
RUSS_EMIAuthor Commented:
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
 
nico5038Commented:
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
 
RUSS_EMIAuthor Commented:
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
 
nico5038Commented:
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
 
RUSS_EMIAuthor Commented:
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
 
nico5038Commented:
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
 
RUSS_EMIAuthor Commented:
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
 
nico5038Commented:
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
 
RUSS_EMIAuthor Commented:
Thanks.  I have a testme.txt file ready for import.  I'll let you know.
Russ
0
 
RUSS_EMIAuthor Commented:
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

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!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now