How to Attach XLS to Outlook mail item through Access VBA

[Guest_imported]

I'm trying to email a copy of an Excel spreadsheet (formatted with linked data) through a command button on an Access form. I can open the XLS with a prompt to update linked data, and I can open an email item through Access VBA, but I'm having trouble attaching the xls to the open mail item... Any pointers?

 

[RobertT687]

Here's a routine I use to attach any file to an OutLook message. Be sure to have the appropriate Outlook reference checked.

Function SendMessage(DisplayMsg As Boolean, Optional
AttachmentPath, Optional MailTo, Optional MailCC, Optional
MailBCC, Optional MailSubject, Optional MailBody)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

On Error GoTo Exit_SendMessage

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(MailTo)
objOutlookRecip.Type = olTo

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

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

' Set the Subject, Body, and Importance of the message.
.Subject = MailSubject
.Body = MailBody & 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
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
Exit_SendMessage:
Exit Function
End Function

 

[scking]

Let's see; Access VBA could be used for SendObject, Microsoft Outlook, CDA, Lotus, or whatever. Are you using the SendObject from VBA. Numerous threads have asked this same question. If you are using Outlook as you mail provide use Outlook to send the mail.

Dim oMailApp As Outlook.Application
Dim oMail As Outlook.MailItem

Set oMailApp = CreateObject("Outlook.Application&quot
Set oMail = oMailApp.CreateItem(olMailItem)
With oMail
.TO = MAILTO
.Subject = "Error " & Me.intErrNbr & " Notice! "
.Body = "Body Stuff"
.Importance = olImportanceHigh
.Sensitivity = olPrivate
.Attachments.Add "C:\Filename.txt"
.Display
End With Growth follows a healthy professional curiosity