Sending email from an UserForm (VBA Word)

Avoiding a mail client you can use CDO, see http://www.rondebruin.nl/cdo.htm for a clear and concise guide to the usage.

CHris

Here is some code that runs from an Access form; it could run equally well from a VBA UserForm, though you would need to declare the listbox as (I think) MSFormsListbox.  You need to set a reference to the Outlook object model for this code:

Private Sub cmdSendEmailwithFile_Click()
'Created by Helen Feddema 5-19-2000
'Last modified 13-Jun-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim strFileName As String
   Dim strFilePath As String
   Dim lst As Access.ListBox
   
   Set lst = Me![lstFiles]
   strFilePath = "G:\Documents\"
   strFileName = strFilePath & lst.Value
   Debug.Print "File name and path: " & strFileName
   
   'Create new mail message and attach text file to it
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = "John Doe"
      .Subject = "The file you requested"
      .Body = "This file was created on " & _
         Date & "." & vbCrLf & vbCrLf
      .Attachments.Add strFileName
      .Display
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

The first solution saying: Avoiding a mail client you can use CDO. Well, this I think is to advanced for me.
The second solution seems to be okay, but it doesn't function on my machine with only Outlook Express. There is no reference to Outlook Express in my VBA.
Neither does it function with Windows Mail or Windows Live Mail.

Perhaps there is a simple solution which find out what kind of email-client I have on my machine, and thereafter uses that? That would be perfect for my need!

What do you find difficult about the CDo option ... basically you copy the code and apply your server/mail details and off you go.

WHatever you use then you will need a mail server account, and that's all thedetails you need ... just as you use to set up the account in outlook for example.

Chris

Perhaps I didn't get it right, but on my computer i only have Outlook Express and I didn't manage to set it up properly I suppose.
I have made a quite simple UserForm in VBA Word. In the UserForm there is a ListBox showing different kind of files (e.g. doc, docx, pdf, xls, xlsx, ppt) I've got in a special folder. I also have a CommandButton in the UserForm. I wish to accomplish the following: First I select one or several files from the list. Then I want to show my Email-program with the files as attachments. So I will write the recipients address and thereafter click Send to send the message with the attachments to the recipient! It would be incredible cool you manage this!!!

BAsically CDO will use the default mail account settings where they exist and where they do the lines:

    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

Replace that default.  It may they are required for outlook express I honestly don't know.

Try the basic code below in a code module and see if it works in sending a mail message ... replace fred@fred.com in teh to field with an address you can receive it at.  Assuming it does we can tune it to your needs, if it doesn't refine the fields till it does.

Chris

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "fred@fred.com"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <ron@something.nl>"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With

End Sub

Select allOpen in new window

Fantastic! It worked! How come? This was very interesting! I used your code like this:
Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "a-maehle@online.no"
        .CC = ""
        .BCC = ""
        .From = """akne"" <a-maehle@online.no>"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With

End Sub

BUT: I don't know what to do with this part:
    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

AND: I don't know how to first show the email-program with the attachment from the Listbox I talked about before I actually send it? Do the user have to fill in the SMTP server before he use the solution?

It is possible the users will need to enter the data in teh With Flds block, but certainly with outlook setup it will always use that default.  I honestly do not know if it will do with others.

If it doesn't then as I say teh optional fields are populated as for example:
I have shown the two fields sometimes required , (account & password) but they aren't normally required but for example as a google mail requirement they would be.

Chris

        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        .item("http://schemas.microsoft.com/cdo/iConfiguration/sendusername") = "doris@googlemail.com"
        .item("http://schemas.microsoft.com/cdo/iConfiguration/sendpassword") = "Secret Squirrel"
        End With

Select allOpen in new window

My Outlook VBA code won't work with Outlook Express, as it does not support Automation (no object model to work with).  I checked with an actual VBA UserForm, and the syntax for declaring the listbox is MSForms.Listbox, as I thought.  You can pick up info from textboxes on the form, as well, for sending to the email message, though I don't know how to reference the specific fields using CDO.

A kind of conclusion must be that the suggeted cdo code with work with outlook but not with outlook express, windows mail or windows live mail because they don't support Automation!
This was very difficult. I thought it would be something like using shellexecute or something and go ahead. When you right-click in the browser you get the Send to a mailrecipient - option. There must be som code under that option... but I don find it!

You don't need any application if you have a mail account and populate the details in teh CDO script ... but I made a mistake as ther update was not applied to the username and password, corrected below.

Chris

        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .item("http://schemas.microsoft.com/cdo/iConfiguration/sendusername") = "doris@googlemail.com"
            .item("http://schemas.microsoft.com/cdo/iConfiguration/sendpassword") = "Secret Squirrel"
            .Update
        End With

Select allOpen in new window

In your solution you have to hard code different kind of information to get the whole running. Is there a way around this hardcoding?

It's not my solution as such it's Ron's but no .... ish.

The only tweak I have is to use variables and set them somewhere else, for example in the open app in thisdocument codemodule, but the basic answer is if you want it work wherever and whatever the mail client then I do not know of any other alternative.

Chris

I don't quite understand the complete solution for this. Is it possible to present the full solution so that I can paste it into my simple solution?

Glad it helped.

Chris