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

Posted on 2012-08-27
Last Modified: 2012-09-05
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
            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

            'Display message before sending
            'Send email right away

        End With

Set objOutlook = Nothing

End Sub

Thanks in advance for the help and the time!!!!
Question by:gigifarrow
    LVL 61

    Expert Comment

    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.

    Author Comment

    no i dont delete new data the data just replaces the old data in the folders.
    LVL 61

    Accepted Solution

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

    Author Comment

    So that means I need a delete command to delete the folders and replace it with new files? How would I write that code.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    760 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

    13 Experts available now in Live!

    Get 1:1 Help Now