email & delete Import Error tables

[ravikode]

Hi,

I am trying to import some Tables from Text files. After the Import is done I want a function to capture all the "Import Error Tables" and send a email with attachment(of the error Tables) and delete the error Tables from the data base. I have tried the below function. The delete works but email doesnot work. Can someone please help.



Public Function VerifyImportErrorTables()
On Error GoTo Err_VerifyImportErrorTables

Dim tblDef As TableDef
Dim strEmailTo As String, strCopyTo As String, strSubject As String

strEmailTo = "ravikode1@yahoo.com"
strCopyTo = "ravikode1@yahoo.com"
strSubject = "Import Error Tables on "


For Each tblDef In CurrentDb.TableDefs
If InStr(1, tblDef.Name, "ImportError") > 0 Then

DoCmd.SelectObject acTable, tblDef.Name, True
'DoCmd.SendObject acSendReport, strTable, acFormatRTF, to:=strEmailTo, cc:=strCopyTo, _
subject:=strSubject, editmessage:=False
'DoCmd.OutputTo acOutputTable, strTable, acFormatRTF, "C:\Temp\ErrorTable.RFT"

DoCmd.DeleteObject acTable, tblDef.Name

MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report was sent to your default printer." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again.", vbInformation, "Import Errors"

End If
Next tblDef

Exit_VerifyImportErrorTables:
Exit Function

Err_VerifyImportErrorTables:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_VerifyImportErrorTables

End Function

 

[mdmilner]

I didn't see where in your code you assigned values to strTable - but under the assumption that clip is just missing.

Why send a table?

Why not just append the information directly to a string variable complete with vbCrLf (line feed) to format and send it within the body of the email but only at the end after the for each...next so you only send one message.

strBody = strBody & vbCrLf & vbTab & strAddCC

A direct single line snip from a working function. This was me appending a new line, then a tab and then adding the contents of strAddCc to the string.

Mike

 

[ravikode]

Hi Mike,

Thanks for your response. can you give a detailed info of how to write that program. I am trying to automate this so I need to see the tables in a email.

Thanks
ravi

 

[mdmilner]

Sorry, just noticed this. I didn't get to test it, but basically, I assigned the text from your MsgBox to strBody -- added a reference to tblDef.Name and then revised the SendObject command slightly. I wasn't sure which version of Access you were writing for, but this syntax works in A2K

Here's hoping I didn't introduce a typo. Watch out for errors in the sendobject command related to which type of quote marks ("") were used.

Mike

Public Function VerifyImportErrorTables() 
On Error GoTo Err_VerifyImportErrorTables 

Dim tblDef As TableDef 
Dim strEmailTo As String, strCopyTo As String, strSubject As String 
Dim strBody As String

strEmailTo = "ravikode1@yahoo.com" 
strCopyTo = "ravikode1@yahoo.com" 
strSubject = "Import Error Tables on " 

For Each tblDef In CurrentDb.TableDefs 
If InStr(1, tblDef.Name, "ImportError") > 0 Then 

strBody = "There was an error importing all of your records in "
strBody = strBody & tblDef.Name & "."
strBody = strBody & vbCrLf & vbLf & "An error report was sent to your default printer." 
strBody = strBody & vbCrLf & vbLf & "The error report will detail the "
strBody = strBody & "error reason for each field and row number for each "
strBody = strBody & "record that was not successfully imported from your file." 
strBody = strBody & vbCrLf & vbLf & "Please correct all errors and import your data again."

'DoCmd.SendObject acSendNoObject, "", acFormatRTF, strEmailTo, strCopyTo, strSubject, strBody, False, “” 

DoCmd.DeleteObject acTable, tblDef.Name 

MsgBox strBody,vbInformation, "Import Errors"

End If 
Next tblDef 

Exit_VerifyImportErrorTables: 
Exit Function 

Err_VerifyImportErrorTables: 
MsgBox Err.Number & " - " & Err.Description 
Resume Exit_VerifyImportErrorTables 

End Function

 

[ravikode]

Thanks for your help