Emailing with Attachment from Excel

[JD]

I have code to send an email but I would like it to send
the cuurent file, i.e. I want the user to fill in some
data then hit a button to email me the file.

I can get the email piece to work but I'm having trouble
getting any attachment let alone the current file to go
with the email.

I get a runtime error when it gets to the attachment part.

here's the code (trying to pull a file from my hard drive
as a test, I have tried many things, this is one version.


Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Set myOlapp = CreateObject("Outlook.Application")
Set myItem = myOlapp.CreateItem(olMailItem)
myItem.Save
Set myAttachments = myItem.Attachments
myAttachments.Add "c:\program files\2002profiles.xls", _
olByValue, 1, "text associated"

With objMail
.To = "(e-mail address removed)"
.Subject = "Subject text"
.Body = "This a test of the automated Schedule from
Excel. " & _
"Here is the test group rates: "
.Attachments = ("myattachments")
.Display
.SaveSentMessageFolder = True

End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

 

[Bob Phillips]

JD,

This isn't the way I use Outlook, but this version seemed to work for me.
You were startuing 2 Outlook sessions, unnecesary, and you trid adding the
attachment after you had alraedy added it. Anyway, try it.

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Dim myAttachments

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Set myAttachments = objMail.Attachments
myAttachments.Add ActiveWorkbook.FullName, _
olByValue, 1, "text associated"

With objMail
.To = "(e-mail address removed)"
.Subject = "Subject text"
.Body = "This a test of the automated Schedule from Excel. " & _
"Here is the test group rates: "
.Display
.SaveSentMessageFolder = True

End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

 

[Dick Kusleika]

JD

Here's some more example code for sending with attachments: