Email an excel file from Access

[dunner44]

I am using Microsoft Office 2000. In an Access form, I wan to email an Excel file. Here is the code I used...

DoCmd.SendObject acSendNoObject, "U:\Filepath\EcxelSheet.xls", , , , , "email subject"

When it runs, a new Outlook email message pops up, but no object is attached. That is because of the SendNoObject...I think.

Any suggestions?

TIA

 

[jbpez]

SendObject does not support attachments to email.

I use something like this:

Public Function SendEMail(ByVal aSubject As String, _
                    ByVal aRecipients As String, _
                    Optional ByVal aAttachments As String = "") As Boolean
    
    Dim olApp As Object
    Dim mobjNewMessage As Object
    Dim sRecipient As String
    Dim sAttachment As String
    Dim sDisplayName As String
    Dim iMarker, iMarker2 As Integer
    
    Set olApp = Outlook.Application
    Set mobjNewMessage = olApp.CreateItem(0)
    
    mobjNewMessage.Subject = aSubject

    ' Loop through ; separated recipients
    Do
        iMarker = InStr(1, aRecipients, ";", vbTextCompare)
        If iMarker = 0 Then
            sRecipient = aRecipients
        Else
            sRecipient = Mid(aRecipients, 1, iMarker - 1)
            aRecipients = Mid(aRecipients, iMarker + 1)
        End If
        If Len(sRecipient) <> 0 Then mobjNewMessage.Recipients.Add sRecipient
    Loop While iMarker <> 0
    
    ' Loop through ; separated attachments
    Do
        iMarker = InStr(1, aAttachments, ";", vbTextCompare)
        If iMarker = 0 Then
            sAttachment = aAttachments
        Else
            sAttachment = Mid(aAttachments, 1, iMarker - 1)
            aAttachments = Mid(aAttachments, iMarker + 1)
        End If
        If Len(sAttachment) <> 0 Then
            iMarker2 = InStr(1, sAttachment, "***", vbTextCompare)
            If iMarker2 <> 0 Then
                sDisplayName = Mid(sAttachment, iMarker2 + 3)
                sAttachment = Mid(sAttachment, 1, iMarker2 - 1)
                If StrComp(Dir(sAttachment), "", vbTextCompare) <> 0 Then mobjNewMessage.Attachments.Add sAttachment, , , sDisplayName
            Else
                If StrComp(Dir(sAttachment), "", vbTextCompare) <> 0 Then mobjNewMessage.Attachments.Add sAttachment
            End If
        End If
    Loop While iMarker <> 0
    
    'mobjNewMessage.Send 'send message automatically
    mobjNewMessage.Display 'pops up message
    
    Set mobjNewMessage = Nothing
    Set olApp = Nothing
    SendEMail = True

End Function

Call it like this:

SendEmail("Email subject","blabla@bla.com","U:\Filepath\EcxelSheet.xls")

John Borges

 

[jvhazelbaker]

You need to creat a SendObject macro. You will enter that the Object Type is a Report. Enter the name of the Excel file (extension not needed), the format as Excel from the drop-down, and so on. Put a button on your form to Run Macro and select the macro name. When button is selected, it sends the report. There is no need for code at all.