Access 97 Sending object

I am sending a table in excel format through e-mail.  The table will then be linked into another database (don't ask - client preference).  I am using Access 97.  The send to function works fine, but when we save the table and try to import it is says it is in the wrong format.  I know access had a problem with the outputto command doing this and the workaround was to use the transferspreadsheet option - does anyone know of something similar I can use to e-mail a table in the correct format?  Can the sendto add an external table to an e-mail?
Who is Participating?
PsychoDazeyConnect With a Mentor Commented:
Change this line:
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
Set objOutlookRecip = .Recipients.Add(Me!FieldName)
You can use a multiple select list box to pass more than 1 recipient to an e-mail, just make sure you put a ; between each name.
I am travelling this week, but I will check in.
try using an outlook object instead of the sendto.  That will allow you to add an attachment, such as an excel workbook.  


Have you tried sendobject (excuse the formatting?

The SendObject action has the following arguments.

Action argument Description
Object Type The type of object to include in the mail message. Click Table (for a table datasheet), Query (for a query datasheet), Form (for a form or form datasheet), Report, Module, or Data Access Page, Server View, or Stored Procedures in the Object Type box in the Action Arguments section of the Macro window. You can't send a macro. If you want to include the active object, select its type with this argument, but leave the Object Name argument blank.  
Object Name The name of the object to include in the mail message. The Object Name box shows all objects in the database of the type selected by the Object Type argument. If you leave both the Object Type and Object Name arguments blank, Microsoft Access sends a message to the mail application without any database object.
  If you run a macro containing the SendObject action in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
Output Format The type of format you want used for the included object. You can click HTML (*.html), Microsoft Excel (*.xls), MS-DOS Text (*.txt), Rich Text Format (*.rtf), or Microsoft Access Data Access Page (*.html) in the Output Format box. Modules can be sent only in MS-DOS text format. Data access pages can only be sent in HTML format. If you leave this argument blank, Microsoft Access prompts you for the output format.
  Note   You can't include objects in Microsoft Active Server (*.asp) or Microsoft IIS (*.htx, *.idc) format, although these selections are displayed in the list  
To The recipients of the message whose names you want to put on the To line in the mail message. If you leave this argument blank, Microsoft Access prompts you for the recipients' names.
  Separate the recipients' names you specify in this argument and in the Cc and Bcc arguments with a semicolon (;) or with the list separator set on the Number tab of the Regional Settings Properties dialog box in Windows Control Panel. If the mail application can't identify the recipients' names, the message isn't sent and an error occurs.
Cc The recipients of the message whose names you want to put on the Cc ("carbon copy") line in the mail message. If you leave this argument blank, the Cc line in the mail message is blank.
Bcc The recipients of the message whose names you want to put on the Bcc ("blind carbon copy") line in the mail message. If you leave this argument blank, the Bcc line in the mail message is blank.
Subject The subject of the message. This text appears on the Subject line in the mail message. If you leave this argument blank, the Subject line in the mail message is blank.
Message Text Any text you want to include in the message in addition to the database object. This text appears in the main body of the mail message, after the object. If you leave this argument blank, no additional text is included in the mail message. If you leave the Object Type and Object Name arguments blank, you can use this argument to send a mail message without a database object.
Edit Message Specifies whether the message can be edited before it's sent. If you select Yes, the electronic mail application starts automatically, and the message can be edited. If you select No, the message is sent without providing a chance to edit the message. The default is No.
Template File The path and file name of a file you want to use as a template for an .html file. The template file is a file containing HTML tags.


Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.


How about an exmaple

docmd.SendObject acSendTable,"<your tablename here>","Microsoft Excel (*.xls)","",,,"<Your subject here>","PLease find attached a spreadsheet",false

Good luck

My preference would be to use the DoCmd.TransferSpreadsheet command.  This command allows you to specify the version of Excel to use for the output file.

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,"TableName","FileName.xls",[Has field names, true/false],[Range(used for Import only])
I agree that using outlook may be the best method.  I have an application that will recieve e-mails, save the attachments as files and transfer the files into tables.  Let me know if you are interested in the code.
Since you want to have it attached to an email I think you will want to use the object library of your email program.  If you're using Outlook, then I would agree that it would be the best bet to accomplish your goals.  My previous suggestion of using TransferSpreadsheet will get you the Excel file in a compatible format but it doesn't address the issue of attaching it to an email.

Are you using Outlook or some other email program?
TheClickerAuthor Commented:
I would LOVE to have the outlook code to do this.  It is one of the strangest errors I have ever seen, and the current way they are fixing it is to open the table from the attachment in excel, hit save, and then it is fine.  Strange.  Using transferspreadsheet won't work alone, but it would work if I could use an outlook call to attach an external file to an e-mail.  Any help is greatly greatly appreciated.
This code sends an e-mail, withan optional attachment:
Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    ' 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("Nancy Davolio")
         objOutlookRecip.Type = olTo
         ' Add the CC recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Michael Suyama")
         objOutlookRecip.Type = olCC
        ' Add the BCC recipient(s) to the message.

         Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
         objOutlookRecip.Type = olBCC
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is the body of the message." & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance
        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then

            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If
        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
        ' Should we display the message before sending?
        If DisplayMsg Then
        End If

    End With
    Set objOutlook = Nothing
End Sub
Here is another method for sending an attachment.  What I did next was use the transfer text method to read throughthe e-mails and import into a table.  If you would like this code also, Let me know.
Dim objNewMail As MailItem
    Dim objRecip As Recipient
    Dim strRecipients As String
    Dim strAttachments As String
    Dim blnResolved As Boolean
    Dim query
    On Error GoTo SendMail_Err
    DoCmd.Hourglass True
    ' Set global Application and NameSpace
    ' object variables, if necessary.
    If golApp Is Nothing Then
        If InitializeOutlook = False Then
            MsgBox "Unable to initialize Outlook Application or Namespace object variables!"
        End If
    End If
    ' Create new MailItem object.
    Set objNewMail = golApp.CreateItem(olMailItem)
    ' Add recipients to MailItem object's Recipients collection.
    With objNewMail
        .Recipients.Add frm!txtTo
         If Not IsNull(frm!txtCC) Then
            If InStr(frm!txtCC, ";") = 0 Then
                Set objRecip = .Recipients.Add(frm!txtCC)
                objRecip.Type = olCC
                strRecipients = frm!txtCC
                ' Parse recipients and add them to objects
                ' Recipients collection.
                    Set objRecip = .Recipients.Add(Left(strRecipients, InStr(strRecipients, ";") - 1))
                    objRecip.Type = olCC
                    strRecipients = Trim(Mid(strRecipients, InStr(strRecipients, ";") + 1))
                Loop While InStr(strRecipients, ";") <> 0
                If Len(strRecipients) > 0 Then
                    Set objRecip = .Recipients.Add(strRecipients)
                    objRecip.Type = olCC
                End If
            End If
        End If
        ' Let Outlook verify that these are valid recipients.
        blnResolved = .Recipients.ResolveAll
        ' Add attachments to MailItem object's Attachment's collection.
        If Not IsNull(frm!txtAttachments) Then
            If InStr(frm!txtAttachments, ";") = 0 Then
                .Attachments.Add frm!txtAttachments
                strAttachments = frm!txtAttachments
                ' Parse Attachments and add them to the Attachments collection.
                    .Attachments.Add Left(strAttachments, InStr(strAttachments, ";") - 1)
                    strAttachments = Trim(Mid(strAttachments, InStr(strAttachments, ";") + 1))
                Loop While InStr(strAttachments, ";") <> 0
                If Len(strAttachments) > 0 Then .Attachments.Add strAttachments
            End If
        End If
        ' Set MailItem object's Subject and Body properties.
        .Subject = Nz(frm!txtSubject, "")
        .Body = Nz(frm!txtMessage, "")
        ' Set MailItem object's Importance property.
        Select Case frm!ctlImportance
            Case olImportanceHigh
                .Importance = olImportanceHigh
            Case olImportanceNormal
                .Importance = olImportanceNormal
            Case olImportanceLow
                .Importance = olImportanceLow
        End Select
        ' Send or Display MailItem depending on user's choice.
        If frm!ctlViewMail.Value = 1 Or blnResolved = False Then
        End If
    End With
    SendNewMail = True
    DoCmd.Hourglass False
    Exit Function
    SendNewMail = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Resume SendMail_Bye
End Function
TheClickerAuthor Commented:

I like the first one, but I need it to run without adding specific recipients, they may send it to different people at different times - how do I manage that.  I am SO new to calling Outlook in code - also, the searching of e-mails for a specific attachment interests me very much.


TheClickerAuthor Commented:
Sorry this took so long - I have been avoiding this app since I graduated!  Thanks for your help!
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.