How do I attach a file to email?

[ChrissyB]

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 = "jdoe@tele.com"
.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

[enigmaos]

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 = "jdoe@tele.com"
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




enigmaos@yahoo.com

[ChrissyB]

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 = "jdoe@test.com"
.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