Solved

CDO objmessage - How to remove attachment before a SEND

Posted on 2007-03-23
8
3,128 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
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:jsvor
Comment Utility
How about setting objMessage.Attachment  to NULL or ""?
0
 

Author Comment

by:Steve Moland
Comment Utility
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
Comment Utility
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Steve Moland
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
The CDO 2000 version should work just fine.

The code I provided 04.04 should work with just CDO2
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Can I legally transfer my OEM version of Windows to another PC?  (AKA - Can I put a new systemboard in my OEM PC?) Few of us are both IT and legal experts but we all have our own views of Microsoft's licensing rules and how they apply.  There are…
If you have done a reformat of your hard drive and proceeded to do a successful Windows XP installation, you may notice that a choice between two operating systems when you start up the machine. Here is how to get rid of this: Click Start Clic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now