cdo woes

[gemma-the-husky]

Having spend a while on this, I can create and send an smtp message, and add atttachments.


A2016

For some reason I am unable to declare

set MyMessage as cdo.message
I tried to add cdosys.dll, but it won't add it.

However, I can late bind an object with
set mymessage = createobject("cdo.message")

but now I can't use intellisense with the object.

so I have an email with an attachment(s) added with addattachment …

So, I am trying to iterate the attachments collection and retrieve the attachment details, but I just cant seem to get the right syntax

MsgBox MyMessage.attachments.count & " attachments" returns 1 attachment.

I've tried attachments(1).various properties without success
I've tried assigning an "attachment" record type, but that rejects any of the listed properties.
I've tried assigning to an object, but that won't iterate any properties.
I've tried assigning to a variant.

Everything I've tried just reports "doesn't support that method"

So what's the right syntax to iterate the attachment(s)?

 

[isladogs]

Hi Dave

I have a CDO EMail Tester example utility which you are welcome to use. You can use it to check settings & send test emails with or without an attachment. If for any reason, the email doesn't send, it includes a diagnostic tool to sort out issues

I do not add a reference to cdosys.dll

Hope that helps

 

[gemma-the-husky]

Hi colin.

I think I downloaded it earlier on. I will take a look.

However, I can send emails - but I just want to unwrap the cdomessage object to ask the user to confirm they want to send, or amend it.


I just looked again, and found this

https://docs.microsoft.com/en-us/previous-versions/exchange-server/exchange-10/ms526983(v=exchg.10)

["https://docs.microsoft.com/en-us/previous-versions/exchange-server/exchange-10/ms526983(v=exchg.10)")

Edit. clicking the above link gives a 404 error, put pasting the URL below into google takes you to the right page.

which gives this syntax for the attachment add … but I can't work out how to de-assign the URL from this syntax.

Function AddAttachment(
ByVal URL as String,
[ByVal UserName as String],
[ByVal Password as String]
) as IBodyPartHRESULT AddAttachment(
BSTR URL,
[BSTR UserName,]
[BSTR Password,]
IBodyPart** pVal
);

 

[isladogs]

I use this procedure for plain text emails where all the values have been set on the email settings form:

Sub SendEMailCDO(aTo, aCC, aSubject, aTextBody, aFrom, aPath)

'==========================================
'Original code by Jeff Blumson
'Adapted by Colin Riddington to include file attachments
'Date: 25/08/2007
'==========================================

On Error GoTo err_SendEMailCDO

Const CdoBodyFormatText = 1
Const CdoBodyFormatHTML = 0
Const CdoMailFormatMime = 0
Const CdoMailFormatText = 1

Dim Message As Object
'Create CDO message object
Set Message = CreateObject("cdo.Message")
With Message.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = txtSendUsing
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = txtPort
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = txtServer
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = txtAuthenticate
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = txtUserName
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = txtPassword
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = intTimeout
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = txtSSL
    
    'code for STARTTLS
    If txtPort = 587 Then
        .Item("http://schemas.microsoft.com/cdo/configuration/sendtls").Value = True
    End If
    .Update
    
End With

With Message
        .To = aTo                                   'Set email adress
        .Subject = aSubject                         'Set subject
        .TextBody = aTextBody                       'Set body text
        If Len(aCC) > 0 Then .CC = aCC              'Set copy to
        If Len(aFrom) > 0 Then .From = aFrom        'Set sender address if specified.
        If Len(aPath) > 0 Then .AddAttachment (aPath) 'Attach this file
        .Send    'Send the message
End With

'Debug lines
    'Debug.Print txtSendUsing, txtPort, txtAuthenticate, intTimeout
    'Debug.Print txtServer, txtUserName, txtPassword
    'Debug.Print aTo, aCC, aFrom
    'Debug.Print aSubject
    'Debug.Print aTextBody
    'Debug.Print aPath

'Show message
MsgBox "The email message has been sent successfully.  ", vbInformation, "EMail message"

'Clean up
Set Message = Nothing

exit_SendEMailCDO:
    Exit Sub

err_SendEMailCDO:
'MsgBox "Error # " & str(err.Number) & Chr(13) & err.Description

strMsg = "Sorry - I was unable to send the email message(s).   " & vbNewLine & vbNewLine & _
    "Error # " & str(Err.Number) & Chr(13) & Err.Description

MsgBox strMsg, vbCritical, "EMail message"

strMsg = ""

Resume exit_SendEMailCDO

End Sub

 

[gemma-the-husky]

@colin

It's going the other way that's my issue.

you have the code snippet

with message
    If Len(aPath) > 0 Then .AddAttachment (aPath) 

[COLOR="Red"]'so at this point is it possible to retrieve apath from the message?[/COLOR]

    .Send    'Send the message
end with

I suppose I could use my array of attachments that I added to the email, but these are in various places. 1 location for the main attachment. Additional locations for some other inclusion files.

I just thought there would be an easy way to go the other way, and recover the attachment paths directly from the message, but I can't see how to do it.

 

[isladogs]

Assume you mean retrieve the path from the message BEFORE sending?
If so, the attachment(s) are saved to a textbox on the sending form so could easily be retrieved as variables.
However, why would you want to do so?

If you mean can the recipient retrieve the original path from the message once opened ...I think and hope that's impossible

I use CDO for email in ALL my Access apps. The main disadvantage of CDO is of course that you don't have a copy in your Sent Messages folder. For that reason, I normally provide the option for users to send a copy to their own email address

 

[Gasman]

Dave,
What properties are you looking for?
Whilst I can find the file name, it does not seem to store the path.?
Presumably as it has already copied the file into the message.?

 

[gemma-the-husky]

Dave,
What properties are you looking for?
Whilst I can find the file name, it does not seem to store the path.?
Presumably as it has already copied the file into the message.?

Hi. As far as I can see now, although you are adding an attachment, it doesn't actually seem to be stored in the cdo.message as an attachment. (see the link I referenced in #3 above)

I just don't see to be able to find the right cdo property to address anything.

How do you pick out the file name? That would do me.

 

[gemma-the-husky]

@colin

Assume you mean retrieve the path from the message BEFORE sending?
If so, the attachment(s) are saved to a textbox on the sending form so could easily be retrieved as variables.
However, why would you want to do so?

If you mean can the recipient retrieve the original path from the message once opened ...I think and hope that's impossible

I use CDO for email in ALL my Access apps. The main disadvantage of CDO is of course that you don't have a copy in your Sent Messages folder. For that reason, I normally provide the option for users to send a copy to their own email address

I have a form that is trying to send an email in respect of some data entered. There are a few options to the mechanism to send the email. One is outlook, which works fine. Another is going to be smtp/cdo. The users don't type in the email - all the bits of the email are collected from various parts of the open form, and from background information not on the form. eg - the email address of the customer, and (for smtp/cdo) the senders email - which is coming from reading the ActiveDirectory, a copy to list which is stored elsewhere, and a number of standard attachments for the email, depending on the nature of the email.

so, for the smtp/cdo message - (ignore any syntax errors or field name errors below)

set mymessage = createobject("cdo.message")

mymessage.to=variable
mymessage.from = variable
mymessasge. copyto = variable
mymessage.subject = variable
mymessage.bodytext = variable
mymessage.addattachment (filepath)
mymessage.addattachment (filepath) 'as required
mymessage.addattachment (filepath) 'as required

Now what I wanted to do for cdo was show the user his email so he could confirm it was what he wanted to send. (or cancel it). I don't need to do that for outlook, as outlook can display or save the message, and the user can check it in outlook.

So I assumed I could just open a form, and then simply take the mymessage structure, and populate controls on the form with the relevant parts of the message - which is easy, except that I just can't see a way to retrieve the attachment file names from the cdo.message structure. It looks like somewhere in the cdo.message is a substructure with a URL, and a pointer to the attachment data, for each attachment but I can't seem to get the right syntax to pick it out. Obviously I could go back to the code that added the attachments, and pick the file paths I added, (and assume they DID get added to the message) but I can't believe I need to do that.

I hope that makes sense.

 

[isladogs]

Path details for the selected attachment file(s) are stored in a textbox on the email form. Although I don't bother to save these in access after sending the email, there is clearly nothing to prevent you doing so (along with the rest of the email if you wish)

The two screenshots illustrate what I mean.
The first is from the email tester which is only intended for sending 1 attachment.
The second is from one of my schools apps and multiple attachments can be sent - in this case two files are being sent with paths separated by a semicolon. In other words, just the same as is done for sending emails to multiple recipients

If you haven't already done so, I would look at the code in my example app

 

[gemma-the-husky]

@colin

I can see exactly what you are doing - I just want to try to go the other way.
I am not generating the email by inputting directly to fields. Instead, I am preparing a sales invoice, say , but when it's prepared I want to save it as a pdf, and then assemble an email including some text, and then attach the invoice pdf, some standard T&C, some other attachments, and then send it to the customer, and maybe copy it to some other users.

I then want to display the email, and ask for confirmation.

eg, given a cdo message ready to send.

dim att as object  'the attachments
'dim att as variant 'or even as variant

        Set att = MyMessage.attachments
        If Not att Is Nothing Then
            MsgBox "Attachments assigned to att"
        End If

This code snippet assigns the cdo attachments to the object att, but I just can't find any syntax/properties/methods to dissect the object. Everything fails with "error 438 this method is not supported, or a different error message "


eg msgbox att(1).text
msgbox att(1).URL
msgbox.URL

I can't iterate any properties of the att object





I could simple re-iterate the attachments I added, but I assumed I could easily get the attachments that were added from some part of the cdo message.

I hope that makes sense.

 

[Gasman]

Dave,
? objemail.Attachments.Item(1).filename shows me my filename
refunds.png (I only added one file, but can do more if you wish?)

Each Item appears to have 5 Fields which also have Items that start at 0
? objemail.Attachments.Item(1).Fields.Item(0).UnderlyingValue or Value shows the filename as well.

HTH

 

[gemma-the-husky]

Eureka

I decided to create an outlook object, and see what properties that offered, and I then tried the same syntax for the cdo.message. The property/attribute I needed was FileName, which I hadn't treied before.

so

mySMTPMessage.attachments(x).Filename returns the file name.

 

[gemma-the-husky]

@gasman.

thanks for that. I saw a link to an image of that structure, but it wasn't interactive, so I couldn't expand the properties. I discovered I needed filename independently by reviewing the outlook message object.

Thanks for showing me all the other possibilities.

I will be OK now, after hours of research!


There's no thanks button on your post for some reason, so I thanked you for another post, and also clicked the scales to add to your reputation. It's a shame I can't give you more than 1 point.

 

[isladogs]

Glad you're now sorted.
I may be missing the point but when the PDF is created as a file, why couldn't you just save the file name / path at that time?

 

[Gasman]

Thank you Dave.
I also learnt something from this experience.

@gasman.

thanks for that. I saw a link to an image of that structure, but it wasn't interactive, so I couldn't expand the properties. I discovered I needed filename independently by reviewing the outlook message object.

Thanks for showing me all the other possibilities.

I will be OK now, after hours of research!


There's no thanks button on your post for some reason, so I thanked you for another post, and also clicked the scales to add to your reputation. It's a shame I can't give you more than 1 point.

 

[gemma-the-husky]

Glad you're now sorted.
I may be missing the point but when the PDF is created as a file, why couldn't you just save the file name / path at that time?

late reply.

I could have used the pdf names, but I didn't think I should need to keep track of all the attachments I was adding to the email.

I thought the DisplayEMail Form should be like a black box. Pass it the cdo email structure, and ask it to display the contents. I shouldn't have to store and keep track of bits of the structure elsewhere. It's much cleaner not to have to do that. You don't do that with outlook. An outlook message is self-contained including all its attachments.