Solved

CDO objmessage - How to remove attachment before a SEND

Posted on 2007-03-23
8
3,327 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:Steve Moland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:jsvor
ID: 18781782
How about setting objMessage.Attachment  to NULL or ""?
0
 

Author Comment

by:Steve Moland
ID: 18781868
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
 

Author Comment

by:Steve Moland
ID: 18782907
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18827882
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
 

Author Comment

by:Steve Moland
ID: 18846618
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 250 total points
ID: 18847472
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
 

Author Comment

by:Steve Moland
ID: 18850938
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18854321
The CDO 2000 version should work just fine.

The code I provided 04.04 should work with just CDO2
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question