How do I attach a file to email?

Hi,

I'm trying to automate attaching an excel document to Outlook email. I've seen quite a few suggestions but cant seem to attach the document. I have included my code below.

Could someone please help me?

THANKS in advance, Christa

P
rivate Sub CommandButton_Click()
'ActiveWorkbook.SaveAs "C:\testsurvey.xls"
Dim email
Set email = CreateObject("Outlook.Application")
With email.CreateItem(olMailItem)
.to = "[email protected]"
.CC = " "
.BCC = " "
.Subject = "Customer Satisfaction Survey "
.Body = "Attached you will find the Customer Satisfaction Survey."
.Attachment.Add = "C:\testsurvey.xls"
.Display
'.Send
End With
MsgBox "Survey sent! Thank you!", , "Send Message"

Set email = Nothing
End Sub

try this:


'########### Add Reference to MS CDO 1.21 ##########
set objSession = CreateObject("MAPI.session")
objSession.Logon "", "", false, false, 0
set objNewMessage = objSession.Outbox.Messages.Add
set objRecipient = objNewMessage.Recipients.Add

set objAttachment = objNewMessage.Attachments.Add
objAttachment.Position = 0
objAttachment.Source = "C:\testsurvey.xls"

objRecipient.Type = CdoTo
objRecipient.Name = "[email protected]"
objRecipient.Resolve
objNewMessage.Subject = "Customer Satisfaction Survey"
objNewMessage.Text = "Attached you will find the Customer Satisfaction Survey."
objNewMessage.Update
objNewMessage.Send

MsgBox "Survey sent! Thank you!", , "Send Message"
set objSession = nothing
set objAttachment = nothing
set objNewMessage = nothing
set objRecipient = nothing




[email protected]

Actually the correct way to do this in outlook is

Private Sub CommandButton_Click()
ActiveWorkbook.SaveAs "C:\CustomerSurvey.xls"
SendMessage False, "C:\CustomerSurvey.xls"
MsgBox ("Your Survey Has Been Sent!" & vbCrLf & "Thank You!")
End Sub

Sub SendMessage(DisplayMsg, Optional AttachmentPath)
Dim objOutlook
Dim objOutlookMsg
Dim objOutlookRecip
Dim objOutlookAttach

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

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
.to = "[email protected]"
.Subject = "Completed Customer Satisfaction Survey"
.Body = "This is a completed Customer Satisfaction Survey." & vbCrLf & vbCrLf

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

If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Sub