Exporting data to email. Data keeps exporting same table unless you delete it from the folder.

Hello experts I have a button that exports three tables to a Excel format then attaches them to a email. The problem I am having is that all the tables update with different data except the table called tblMODHistory.  It keeps the same data in that was exported the last time. In order for the data to change you have to go to the folder that it is located in called, " Export Folder" and then export the table. Now I dont know if this makes a difference but this is the only table that is linked.  Here is the code below.

Private Sub Command2_Click()
Dim objOutlook              As Outlook.Application
Dim objOutlookMsg           As Outlook.MailItem
Dim objOutlookRecip         As Outlook.Recipient
Dim objOutlookAttach        As Outlook.Attachment
Dim strTable1Name1          As String
Dim strTable1Name2          As String
Dim strTable1Name3          As String
Dim strTable1Name4          As String
Dim strBasePath             As String

strBasePath = "C:\ExportFolder\"
strTable1Name1 = strBasePath & "tblMODHistory.xls"
strTable1Name2 = strBasePath & "tblfuelCellHistory.xls"
strTable1Name3 = strBasePath & "tblAFESCEPHistory.xls"
strTable1Name4 = strBasePath & "tblEmportInduction.xls"

''Export the tables to Excel
DoCmd.TransferSpreadsheet acExport, , "tblMODHistory", strTable1Name1
DoCmd.TransferSpreadsheet acExport, , "tblfuelCellHistory", strTable1Name2
DoCmd.TransferSpreadsheet acExport, , "tblAFESCEPHistory", strTable1Name3
DoCmd.TransferSpreadsheet acExport, , "tblEmportInduction", strTable1Name4
         
    'Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    'Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
       
        With objOutlookMsg
            'Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("Johnson, Mary C (Contractor) (US SSA)")
            objOutlookRecip.TYPE = olTo
            'CC
            Set objOutlookRecip = .Recipients.Add("Jackson, Jerry C (US SSA)")
            objOutlookRecip.TYPE = olCC

            'Set the Subject/Body of the message.
            .Subject = "This is an Automation test with Microsoft Outlook"
            .Body = "This is the body of the message." & vbCrLf & vbCrLf

            'Add attachments to the message.
            Set objOutlookAttach = .attachments.Add(strTable1Name1)
            Set objOutlookAttach = .attachments.Add(strTable1Name2)
            Set objOutlookAttach = .attachments.Add(strTable1Name3)
            Set objOutlookAttach = .attachments.Add(strTable1Name4)

            'Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients
                objOutlookRecip.Resolve
            Next

            'Display message before sending
            .Display
            'Send email right away
            '.Save
            '.Send

        End With

'cleanup
Set objOutlook = Nothing
               

End Sub


Thanks in advance for the help and the time!!!!
gigifarrowAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
<< the data just replaces the old data in the folders. >>

Where/how is that data being replaced?

That is apparently what needs to be fixed with tblMODHistory.  

The code that you have posted here does nothing in terms of replacing data, and will not help you resolve this problem.  It simply exports the data to excel and attaches the spreadsheets to emails.
0
 
mbizupCommented:
The fact that the table is linked wouldn't make a difference.

<It keeps the same data in that was exported the last time>

To ensure that the data is completely new each time the exports are run, you would need code to either delete the table or empty all of its data before populating it with new data.  

How is that table currently being filled?  The code you have posted here isn't quite enough.  The code you need to change or add would be in a different sub or function, and would occur before what you have posted here.
0
 
gigifarrowAuthor Commented:
no i dont delete new data the data just replaces the old data in the folders.
0
 
gigifarrowAuthor Commented:
So that means I need a delete command to delete the folders and replace it with new files? How would I write that code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.