CDO objmessage - How to remove attachment before a SEND

In VBA I'm sending multiple emails  using the CDO object and it works very well.

I just started adding attachments and that works well, TOO well.
The source of email addresses and attachment file names are in rows in an Excel sheet.
The logic sends the first email and then loops back and only refreshes the TO and ATTACHMENT, does an UPDATE, then a SEND.

The problem is, once a message is sent with an attachement then the next email may not need an attachment, but the attachment defined will be the same until it is replaced. Replacing works fine, but when there is NO attachment I can't figure out how to remove the attachment which got staged in the previouse message.  See the code for where I need to be able to so the equivilent of:

objmessage.Attachment.Delete

I've tried every combination I can think of and I can't seem to find a reference for the complete object model with the names of the Methods and Properties

======================================================================
'Send more than one email
    With Sheets("emailwork")
    For Row = 2 To 2000
    If IsEmpty(.Cells(Row, 1)) Then Exit For
        objMessage.To = .Cells(Row, 4)
        If Trim(.Cells(Row, 5)) <> "" Then
            em_attachment = Range("PDF_temp_files") & .Cells(Row, 5)
            objMessage.AddAttachment ("file://" & em_attachment)
        Else
            '      I want to delete the existing staged attachment HERE
               objMessage.Attachment = Nothing    <--this does not work. code failure
        End If
        objMessage.Configuration.Fields.Update
        objMessage.Send
    Next
    End With
Steve MolandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jsvorCommented:
How about setting objMessage.Attachment  to NULL or ""?
0
Steve MolandAuthor Commented:
objMessage.Attachment = Null  fails as not being a valid method

I'm not even sure what the proper syntax for the object is at this level

".Attachment" is questionable

I've tried:
objmessage.Attachment.Delete
objmessage.Attachment.Item(0).Delete
Set objmessage.Attachment = various values

Attachments are added using:
objmessage.Addattachment (file specs)
0
Steve MolandAuthor Commented:
BTW, I've found plenty of information here on EE over the last few years but this is first time I've had to post a question.

I didn't mean to be so cheap in the point, it was more that I was looking at the descriptions for giving points.

Every description had difficult or hard associated with it and 50 points had "EASY" next to it.

While it was Hard for ME if figured it would be a piece of cake for the Experts given the depth of knowledge I've seen in answers in the past.

I suppose that the points I give should also recognize how much time Experts spent obtaining their knowledge.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Robberbaron (robr)Commented:
Attachments is a collection.

http://msdn2.microsoft.com/en-us/library/ms526345.aspx

'Send more than one email
    With Sheets("emailwork")
    For Row = 2 To 2000
    If IsEmpty(.Cells(Row, 1)) Then Exit For
        objMessage.To = .Cells(Row, 4)
'--->>>
        objMessage.Attachments.Delete     'delete all attachments from collection

        If Trim(.Cells(Row, 5)) <> "" Then
            em_attachment = Range("PDF_temp_files") & .Cells(Row, 5)
            objMessage.AddAttachment ("file://" & em_attachment)


         '--- but i would have thought you had to use objMessage.Attachments.Add ( ...)

          End If
'<---
        objMessage.Configuration.Fields.Update
        objMessage.Send
    Next
    End With



'what object exactly is objMessage ?
0
Steve MolandAuthor Commented:
for whatever reason the normal collection method is not support.

I tried .attachments.delete and that code failed as not valid.

Here is the code that works for the first message. During each loop another attachment get added until the last message has all the attachements.
====================================

Sub Send_Email_Via_CDO(em_source As String, em_subject As String, em_textbody As String, _
                        Optional em_to_address As String, Optional em_attachment As String)
'   This will send ONE email
'   OR
'   Multiple emails, getting data for each from rows in a sheet.
Dim objMessage As Object
Dim Fromwork As String
Dim Msgwork As String
Dim passwork As String
' Create the message object.
Set objMessage = CreateObject("CDO.Message")

Fromwork = "" & Range("em_from_name") & "<" & Range("em_from_email") & ">"
objMessage.From = Fromwork
objMessage.Subject = em_subject
objMessage.TextBody = em_textbody
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Range("em_smtp_server")   ' Or "mail.server.com"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Range("em_login")
passwork = Range("em_pass_1") & Range("em_pass_2")
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = passwork
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'   Send only one email
If LCase(em_source) = "one" Then
    objMessage.To = em_to_address
    If Trim(em_attachment) <> "" Then objMessage.AddAttachment ("file://" & em_attachment)
    objMessage.Configuration.Fields.Update
    objMessage.Send
Else
'   Send more than one email
    With Sheets("emailwork")
    For Row = 2 To 2000
    If IsEmpty(.Cells(Row, 1)) Then Exit For
        objMessage.To = .Cells(Row, 4)
        If Trim(.Cells(Row, 5)) <> "" Then
            em_attachment = Range("PDF_temp_files") & .Cells(Row, 5)
            objMessage.AddAttachment ("file://" & em_attachment)
        Else
            ' DELETE CODE NEEDED HERE
        End If
        objMessage.Configuration.Fields.Update
        objMessage.Send
    Next
    End With
End If
Set objMessage = Nothing
End Sub
0
Robberbaron (robr)Commented:
ok. You are using CDO for Exchange.....  I have been using CDO1.21 which is older but has a different and sometimes better API.

"The AddAttachment method adds the attachment by first retrieving the resource specified by the Uniform Resource Locator (URL) and then adding the content to the message's Attachments collection within a BodyPart object"   from http://msdn2.microsoft.com/en-us/library/ms526983.aspx

The Attachments property is the collection of message attachments.

'use this to delete attachements
  Dim collAtts as CDO.IBodyParts         'or dim collAtts as Object
  Set collAtts = objMsg.Attachments
  If collAtts.Count = 0 Then
    'no attachments    
  Else
    'MsgBox "Message has " & collAtts.Count & " attachments"
     collAtts.DeleteAll
  End If


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve MolandAuthor Commented:
robberbaron, your solution was better than you know.

Had I stumbled on the code you sent on my own I would not have tried it because, as you mentioned, it was for a different version of CDO.

To try it I had to add a reference to "Microsoft CDO 1.21 Library" and since I had already referenced "Microsoft CDO for Windows 2000 Library" I would have thought having both word really upset VBA.

The fact that a lower level object from one library works inside a higher level object in another library, opens up lots of other possibilities in my poor understanding of the object models.

Not only did you provide me with the "fish" that a man needs for a day, but you also taught me a little about "fishing" that a man needs for life.  (to borrow from "education" metaphor.)
0
Robberbaron (robr)Commented:
The CDO 2000 version should work just fine.

The code I provided 04.04 should work with just CDO2
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.