Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-08-27
Medium Priority
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
  • 2
  • 2
LVL 61

Expert Comment

ID: 38340233
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

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

Accepted Solution

mbizup earned 2000 total points
ID: 38340326
<< 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

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

864 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