+ Reply to Thread
Results 1 to 21 of 21

Use data from a UserForm to send email

  1. #1
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Use data from a UserForm to send email

    I was wondering if someone could help with my issue.

    Is it possible to create a userform, then send the data entered in the userform to an email recipient?

    Sorry if this is in the wrong section, but figured some programming was likely to be necessary.
    Last edited by tanktata; 06-11-2011 at 03:18 PM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tankata

    See these links for an example of using UserForm data to send emails in Outlook http://www.excelforum.com/outlook-pr...ok-2007-a.html and http://www.excelforum.com/outlook-pr...e-mailing.html

    I'd assume these examples won't fill your needs but perhaps give you some ideas. If you're using Outlook and need further assistance, attach a sample of your workbook with the UserForm and a description of what it is you wish to accomplish.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    Thanks for the response.

    I looked at the attached examples but they all seemed a bit too advanced for me.

    I have attached a copy of an example of what I am attempting. A simple userform, with 3 fields. Once the data is entered, pressing the email button will send the details to a recipient, in the same format shown on sheet1. The main thing is sending the details in the correct format.

    Thanks again.
    Attached Files Attached Files

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata
    I see that you have a UserForm that has three textboxes for user input. I see that the UserForm Email details command button writes the values of the textboxes to Range B1-->B3.


    Now you want to send an email using Outlook.
    1. Will the body of the email be Range B1-->B3?
    2. Where will the email address come from? Do you want a prompt to enter the email address?
    3. Where will the email subject come from? Do you want a prompt to enter the subject?

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    I've attached two files. The first file writes a range of cells to the body of the email and prompts for an email address and subject.

    The second file grabs all required information from your UserForm (modified).

    Let me know which you wish to build upon (if either).
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    In answer to the 3 questions,

    1. I would like the body to be range A1:B3
    2. The email address will always be the same so it could be written as a value in any of the empty cells.
    3. The subject will be the values from cells A3 and B3 with some added text.

    Will I be able to use the worksheet function CONCATENATE and assign this to an empty cell to use as the email subject?

    Thanks.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    Regarding this
    1. I would like the body to be range A1:B3
    Easily done...simply change the range reference in Module 2.

    Regarding this
    2. The email address will always be the same so it could be written as a value in any of the empty cells.
    If it's ALWAYS the same, simply hard code the email address in Module 2.
    Regarding this
    3. The subject will be the values from cells A3 and B3 with some added text.
    There are a couple different ways to approach this...you could
    use the worksheet function CONCATENATE and assign this to an empty cell to use as the email subject
    or you could do the concatenation in Module 2.

    Using Version 1 of the files I attached to my previous thread would be the starting point. Do you want to attempt the changes? Do you want me to make the changes? Up to you...let me know.

  8. #8
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    Cheers Jaslake.

    I will have a go at making the adjustments and see if I can get it working.

    Would it be ok to keep the thread going if I need more advice?

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    Keep it open until you can mark it solved. I'm attaching a working version that does as I believe you described. Open it when you're ready.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    Thanks once again Jaslake. I managed to get it working myself, not quite as efficiently as your code tho, so I think I will use yours

    Is it possible to send the email without the user having to press send in the email client?. It opens up outlook then seems to change to MS word to send the email, although I think it could be something to do with how I have my MS Office set up.

    One last thing, will this work ok in the 2003 version of excel? The people who will be using this only have that version.

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    The code has been tested in both Excel 2000 and Excel 2007...so, I'd think it should work in Excel 2003. However, I don't have Excel 2003...so, you'll have to test it.

    To send the Email in Outlook without having to click "Send" change this code in Module 2
    Please Login or Register  to view this content.
    Let me know of issues.

  12. #12
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    That all works well on my home set up. Just have to try it in the work environment now.

    I will leave it open until I have tested it tomorrow.

    Thanks once again Jaslake, much appreciated.

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Glad to help...let me know how you make out.

  14. #14
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    Hi Jaslake,

    Have tested the macros in a work situation. I noticed a few small issues.

    The first one is that the MsgBox doesnt pop up if the user doesnt enter any data.

    The second issue, which is likely to be because I havent copied the code correctly, is that the data in the email shows in the centre of the mail instead of indented to the left.

    Neither is a critical issue but it would be good to get it working perfectly.
    I will retype the macros tomorrow to try and solve the second issue, but do you have any ideas why the MsgBox doesnt show up?

    Thanks.

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    Regarding this
    MsgBox doesn't pop up if the user doesn't enter any data
    Yes, there's no error checking in the code...that's easily added. I'll post code that accommodates that.

    Regarding this
    the data in the email shows in the centre of the mail instead of indented to the left.
    I'd need to see a simulation of your "real" data. My tests on your sample file do not exhibit this result.

  16. #16
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    Hi Jaslake.

    I have resolved the second issue, I had spelt CENTER incorrectly in this line;

    Please Login or Register  to view this content.
    Very annoying!!

    Regarding the error correction, I thought this code was there to check for no data being entered,

    Please Login or Register  to view this content.
    Cheers once again.

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    Sorry, I had house guests for the last several days and forgot to post back. Replace the code in the UserForm Email Details Button with this
    Please Login or Register  to view this content.
    If any of the TextBox Items is blank, the user will get an error message. Let me know of issues.

    Regarding this
    If rng Is Nothing Then
    ...
    We've defined the range in the code...it's still a range even if there's no data in the range.
    Last edited by jaslake; 06-10-2011 at 04:33 PM.

  18. #18
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    I really appreciate all your help with this Jaslake. Everything works as I had hoped.

    I have, however, been looking at trying to bypass the outlook security message that pops up using this code, with no success. I read that this code may not work with all PC's

    Please Login or Register  to view this content.
    Is it possible to bypass that security message using VB code?

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    What security message are you receiving? On my platform, using Outlook 2007, I receive no security message. The emails are either sent or displayed, depending on what the procedure says to do.

    What is this code intended to do
    Please Login or Register  to view this content.
    What version of Excel and Outlook are you using?
    Last edited by jaslake; 06-11-2011 at 02:19 PM.

  20. #20
    Registered User
    Join Date
    01-07-2004
    Location
    Manchester UK
    MS-Off Ver
    Office 2010
    Posts
    73

    Re: Use data from a UserForm to send email

    I am using the 2003 version of office.

    When the macro was run, outlook would display a security message saying that another program was trying to send an email. I would then have to manually press send which was annoying.

    This web page shows the message, and outlines a few work arounds;

    HTML Code: 
    I have managed to find a line of code which sends without the message popping up. The code is;

    Please Login or Register  to view this content.
    Thanks once again Jaslake. You have been a great help.

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use data from a UserForm to send email

    Hi tanktata

    Glad it works for you. If that satisfies your need, please mark your thread as "Solved".

    To mark your thread solved do the following:
    - Go to your first post on the thread
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solved
    - Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1