Exporting data to email. Data keeps exporting same table unless you delete it from the folder.
Posted on 2012-08-27
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)
'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
Set objOutlook = Nothing
Thanks in advance for the help and the time!!!!