Solved

CDO objmessage - How to remove attachment before a SEND

Posted on 2007-03-23
8
3,195 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

770 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