vba send userform in readable format

[Noz2k]

I have a userform which on completion sends the relevant data to the relevant cells in excel. However I also need to send the form inputs to a person via email, in a readable format, and have no idea how to do this.

Do I need to set the layout in a word document, or can this be done in excel?

Any help would be appreciated, as would a pointing in the right direction, or to any helpful links.

Thanks

 

[AlphaFrog]

This link should help...
Example Code for sending mail from Excel

 

[Noz2k]

Thanks, that does look like a useful link, however I don't think I am quite at that stage yet.

From my understanding (which could well be wrong), that will only send the data as it currently appears in Excel like e.g.

First Name   Last Name    Date     Time
Clint       Eastwood   30/03/2010  15:45

As there are about 50 columns I am trying to get it so the data appears in an easier to read format e.g.

First Name     Last Name
Clint          Eastwood
 
Date           Time
30/03/2010     15:45

and so that it only shows the relevant information. As many fields are not visible on the form dependant on other textbox values. However these appear as blank cells in the rows where their visibility is not true. I don't want the email recipient seeing this irrelevant data.

 

[BrianExcel]

It will take some time, but you can code which values you want in the email body. How is the data being displayed in the userform? Is each value in it's own textbox, combobox, etc.?

 

[BrianExcel]

You can also use a separate, hidden sheet to arrange the cells in the same format as you would want to send.

Then you would do a button or some other action event to set the values in your textboxes, combo boxes, etc., and transfer them onto the sheet.

The final step would be setting the range as the cells on that sheet.

Make sense?

 

[Noz2k]

It will take some time, but you can code which values you want in the email body. How is the data being displayed in the userform? Is each value in it's own textbox, combobox, etc.?

Headings aside, all the values on sheet1 are derived from the userform. Textboxes and Comboboxes have exactly the same value, and optionbuttons/checkboxes produce either a "yes" or "no" value.

You can also use a separate, hidden sheet to arrange the cells in the same format as you would want to send.

Then you would do a button or some other action event to set the values in your textboxes, combo boxes, etc., and transfer them onto the sheet.

The final step would be setting the range as the cells on that sheet.

Make sense?

Yes, I thought this might be the way. I thought about using Sheet 2 to just hold the newest row in sheet 1, in a more user friendly reabable way. If I use VBA to post the headings as well as the textbox(etc) values, then I think I should be able to use if statements to not show the invisible items.

Thought there may have been a simpler way though. Guess not.

Will give this a try, thanks for your help

I also need them to automatically fill certain controls on a larger form. My CommandSend Button is going to be very long by the end of all this.

 

[Noz2k]

Ok, so I have the code all set-up so that it sends the data in a nice easy to read format on sheet2 on the workbook. However I cannot figure out how to send sheet2 as an email, either in the body or as an attachment.

This will be a document that multiple users will have access to. The email should always go to the same recipient, but will be sent from different people (so I'm guessing some kind of login would need to be generated?).

The company uses googlemail. email address of the format firstname.lastname@companyname.com

I can figure out how to send email if outlook was set up, but neither I nor the majority of users have Outlook setup anymore, where as every employee has a gmail account.

Any help would really be appreciated. Thanks

 

[AlphaFrog]

Mail one Sheet With SendMail

 

[Noz2k]

Doesn't that method only work with Outlook?

I don't recieve an error message but I also don't recieve an email at all.

There is a cbo method on that website which I think should work with gmail but I cannot get it to run successfully

 

[Noz2k]

Any help would be really appreciated, have been stuck on this for a while now, and it's very frustrating as well as unproductive, so any ideas would be very much appreciated.

This is the code that I think I should be using from the link that AlphaFrog posted

Sub CDO_Mail_Small_Text1()
    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") _
                           = "smtp.gmail.com"
            .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 = "MyName@Company.com"
        .CC = ""
        .BCC = ""
        .From = """MyName"" <MYNAME@Company.com>"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With
End Sub

I have also tried this including the lines

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MyEmail"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MyPassord"

Either way I get an error

"Run-time error '-2147220973 (80040213)':

The transport failed to connect to the server."