• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

VB6 - Send specific Microsoft access table into XLS file format by email


I have the below code that send to email the full Microsoft Access dbase by email. The thing is that i would like to only send specific tables inside the access dbase, and in XLS format.

How can i do this?

If i want to send by email 2 table out of 10, and in xls format, can i send all 5 in one single Excel file with 5 sheets based on the 5 tables or it can only be be separated by 5 different Excel file, one per table name?

Thanks again for your help

Thanks again

This is what i use now:
        Dim patha As String
        Dim result As Integer
        Dim displaymessage As Boolean
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        patha = dta_base_link_audit_tms.Text
        displaymessage = True
        ' 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("email address")
            objOutlookRecip.Type = olTo

            ' Add the CC recipient(s) to the message.
            'Set objOutlookRecip = .Recipients.Add("")
            ' objOutlookRecip.Type = olCC

            ' Add the BCC recipient(s) to the message.
            '  Set objOutlookRecip = .Recipients.Add("")
            ' objOutlookRecip.Type = olBCC

            ' Set the Subject, Body, and Importance of the message.
            .Subject = "dbase backup on " & Format(Now, "mmm dd yyyy hh:mm")
            .Body = "This is the audit dbase backup updated on " & Format(Now, "mmm dd yyyy hh:mm") & vbCrLf & vbCrLf
            .Importance = olImportanceHigh  'High importance

            ' Add attachments to the message.
            'If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(patha)
            'End If

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

            ' Should we display the message before sending?
            If displaymessage Then
            End If
        End With
        Set objOutlook = Nothing

Open in new window

2 Solutions
Which tables do you want to send?

Do you want them all to be sent as attachments to the same message?
Wilder1626Author Commented:
out of 10 tables, only 5 of them needs to be sent in an email.

Table names are:

If they could be sent in the same email, that would be great. If it can only be 1 file per email, it can also be good, but the email subject will have to have the table name inside.
It's going to look something like this
vTabNames = Array("Employes", "Items", "Sales", "Prices", "Vendors")
set oXL = CreateObject("Excel.Application")
set oWkb = ActiveWorkbook

For Each vItem In vTabNames
	Set rs = dbEngine(0)(0).OpenRecordset(vItem, dbopendynaset)
	set oWks = oWkb.Worksheets.add
	oWks.name = vItem
	oWks.range("A1").CopyFromRecordset rs
oWkb.close true
set oWkb = nothing
set oXL = Nothing

Open in new window

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Insert this code into Line 33 of your code.  It will open the Access DB , copy the Tables to an Excel sheet, and then close the DB.  you can then send the strXL_Export_File   (Tested code and it works...)

            'Tools --> References --> Microsoft Access ##.0 Object Library
            Dim strDB_Path
            Dim strXL_Export_Path
            Dim strXL_Export_File
            Dim objAccess As Access.Application
            strDB_Path = "C:\Temp\Test.mdb"
            strXL_Export_Path = "C:\Temp\"
            strXL_Export_File = strXL_Export_Path & "Export_" & Format(Date, "yyyyMMdd") & ".xls"
            Set objAccess = CreateObject("Access.Application")
            objAccess.OpenCurrentDatabase strDB_Path, True
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", strXL_Export_File, True
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", strXL_Export_File, True
            Set objAccess = Nothing

Open in new window

One more thing,  This code will create one Workbook with each sheet set to the table name so you only have to send one email with one XL file.
Wilder1626Author Commented:
This is very good. thanks again for all the help. Now able to send an xls in attachment with only for tables i need.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now