Solved

CDO objmessage - How to remove attachment before a SEND

Posted on 2007-03-23
8
3,265 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OLD TECH - PKZIP Only Zips 7 Files 17 113
Protecting vb6 & .Net code Obfuscation 18 184
Transfer configuration between Windows XP installations 4 85
extend monitor issues 6 67
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

730 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