Use data from a UserForm to send email

**tanktata** · 06-06-2011, 06:18 PM

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.

**jaslake** · 06-07-2011, 12:08 PM

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.

**tanktata** · 06-07-2011, 01:28 PM

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.

**jaslake** · 06-07-2011, 01:51 PM

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.

Will the body of the email be Range B1-->B3?
Where will the email address come from? Do you want a prompt to enter the email address?
Where will the email subject come from? Do you want a prompt to enter the subject?

**jaslake** · 06-07-2011, 02:44 PM

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).

**tanktata** · 06-07-2011, 02:46 PM

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.

**jaslake** · 06-07-2011, 02:59 PM

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.

**tanktata** · 06-07-2011, 03:30 PM

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?

**jaslake** · 06-07-2011, 03:38 PM

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.

**tanktata** · 06-07-2011, 04:05 PM

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.

**jaslake** · 06-07-2011, 04:19 PM

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.

**tanktata** · 06-07-2011, 04:47 PM

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.

**jaslake** · 06-07-2011, 04:51 PM

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

**tanktata** · 06-09-2011, 04:18 PM

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.

**jaslake** · 06-09-2011, 11:01 PM

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.

**tanktata** · 06-10-2011, 04:07 PM

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.

**jaslake** · 06-10-2011, 04:27 PM

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.

**tanktata** · 06-11-2011, 02:00 PM

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?

**jaslake** · 06-11-2011, 02:13 PM

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?

**tanktata** · 06-11-2011, 02:45 PM

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.

**jaslake** · 06-11-2011, 03:11 PM

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

Use data from a UserForm to send email

LinkBack

Thread Tools

Rate This Thread

Display

Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Re: Use data from a UserForm to send email

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions