Sending mail from excel with CDO

  • Thread starter Thread starter Christy
  • Start date Start date
C

Christy

Can anyone tell me where I am going wrong here. I get
errors on the .To line and the .AddAttachment line
(doesn't like the variables I guess) I also get an error
on the Kill line.


For i = 1 To k
'get the address of the intended receipient
name = Cells(rnum + i, 3).Value
& "@email.usps.gov"
Debug.Print name
' get the name of the file to attach
aname = Cells(2, 4).Value & Cells(rnum + i,
8) & ".rft"
Debug.Print aname

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

With iMsg
Set .configuration = iConf
.To = "name"
.From = "(e-mail address removed)"
.Subject = "WEEKLY FLASH REPORT"
.Textbody = Cells(rnum + 1, 6).Value & vbNewLine
& "Please forward your comments/changes to Dan Sorenson
or Gary Bilyk."
.AddAttachment = "aname"
.Send
End With

Kill "aname"


Next i


Thanks
Christy
 
Should the file extension be "rtf"?
Make sure you are not confusing variable names with
variable contents, e.g
aname is the variable
Its content is: Cells(2, 4).Value & Cells(rnum + i,
8) & ".rft"
Click to expand...
"aname" is a string whose value is: "aname"
Similarly, should be
.To = name
not
.To = "name"

You could also add code such as
If Dir(aname) <> "" Then
Kill aname
End If

to confirm file existence before you delete

Kevin Beckham
 
Thanks Kevin,

Yes, it was supposed to be rtf not rft
Taking the quotes from around name seems to be working
and kill suggestion worked also.

Now my only problem is the .AddAttachment
I can't get this to work with or without the quotes. Any
suggestions?

You help is greatly appreciated.
Christy
 
Christy

Try
.AddAttachment aname

as .AddAttachment is a function, not a property

Kevin Beckham
 
Back
Top