We help IT Professionals succeed at work.

Sending email from an UserForm (VBA Word)

How is itpossible to send an email from an UserForm made in VBA in Word?
I suppose I have to detect which email-program the system is using (e.g. Windows Mail, Outlook Express, Outlook etc.) before I can send the email.
I want to select a file in a ListBox and get it up like an enclosure in the email-rpogram before I write  the email recipient address and the click Send.
Comment
Watch Question

Top Expert 2011

Commented:
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
Top Expert 2009

Commented:
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

Open in new window

Author

Commented:
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!
Top Expert 2011

Commented:
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

Author

Commented:
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!!!
Top Expert 2011

Commented:
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

Open in new window

Author

Commented:
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?

Top Expert 2011

Commented:
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

Open in new window

Top Expert 2009

Commented:
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.

Author

Commented:
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!
Top Expert 2011

Commented:
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

Open in new window

Author

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

Commented:
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

Author

Commented:
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?
Top Expert 2011
Commented:
The code that worked is as below but to enable it to work without outlook account(s) installed needs to be told where to get the account info from.  THis is what the lines here do, (comment prefix removed:

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

When the server mail details are added the CDO code uses that information to send the email rather than the default mail application hence this bit should make it independant.

Lines to edit for the first try have a comment line before them in the above extract.

Chris
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
' Your server for the email account
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' YOur email account on the server
            .item("http://schemas.microsoft.com/cdo/iConfiguration/sendusername") = "doris@googlemail.com"
' Your password for the email account on the server
            .item("http://schemas.microsoft.com/cdo/iConfiguration/sendpassword") = "Secret Squirrel"
            .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

Open in new window

Top Expert 2011

Commented:
Glad it helped.

Chris