Sending emails from Excel

Hi Leith - Good to see your name again. I have been going to call you, but when I do you will understand what I've had to go thru with my oldest daughter.

Broro133 - Sorry, I don't think I know you, but I'm sure you are also very good.

Well, both of you probably won't believe it, but I was going to put in a thread either tonight or tomorrow for about the same thing.

Previously, I asked and was advised to look at Ron's and I couldn't find help for me.

Mine may be a little simpler than Harlequin's.

I need to take a list of email addresses on my workbook and send an email out with a message to each email address.

I am a little open on how the messages should be composed or stored or whatever so that the program will automatically select the message and place it into the eMail.

I'm really open for ideas.

Matt

How do you want to send the email? This solution shows you how to do it with Outlook. It takes an array of email addresses, a subject line, message body and an optional pathname for an attachment. You need to add the code that will build the array of email addresses, and determine what the message body is.

Please Login or Register  to view this content.

Hi 6StringJazzer . . .

Looks very interesting.

Here are my questions & statements regarding the Use of Outlook:

1. I can supply a range name of eMail addresses on my spreadsheet if that is usable.
2. I would like the message to be in a text box in Excel. I would also like the option that a Word document could also be included as an attachment.
3. Subject line would be located in a particular cell in Excel along where the text box is located.
4. I want only 1 email sent via TO: and the balance of emails via BBC: My customers and myself do not want our lists set out to others. The 1 going via TO: could also come from a cell in Excel. This would probably always be the email address of the sender, such as mine.

Naturally, if any or all of these would not work, please suggest other.

NOTE:
I see the possibility of needing 2 seperate programs. This is my overview of what I want if possible.

Many of my customers need to automatically send out emails to people on their eMail tracking program, which I supply to them. Their needs would normally be much smaller than mine, in regarding the number of emails to be sent.

I believe that most users will not be using Outlook and this could cause difficulty for them.
For these customers, it would be excellent if they could use Hotmail. As I am sure most people know that, Hotmail and others limit the number of emails you can send at one time. Personally, I can only send 100 emails per send in Hotmail.

PLEASE NOTE: MANY OF MY CUSTOMERS DO NOT HAVE THEIR CONTACT LIST IN HOTMAIL OR ANY OTHER EMAIL PROGRAM DUE TO THE POSSIBILITY OF EMAIL ATTACKS. WHICH I HAVE EXPERIENCED NUMEROUS TIMES. I have a program that tracks email addresses, names, phone #, address, city, st, zip, plus over 20 sortable columns which automatically selects the desired emails & places them on the clipboard for emailing. Using my program does not change the number of emails that can be sent out on one email.

I have heard, true or not, that if I used Outlook, I could send many more emails at one time.

With Hotmail & Outlook, I want to be able to automate the sending of emails from my program.

If your suggested code works automatically when I supply the list and data etc, that is what I want to do.

I know I'm being long winded, but details and questions are necessary.

Originally Posted by Launchnet

1. I can supply a range name of eMail addresses on my spreadsheet if that is usable.

That is usable but you would have to modify the Sub I provided to handle that. You can rewrite it to accept a range instead of an array, and the logic would be very similar. You would set up a For loop to visit every cell in the range, building the To list as you go.

2. I would like the message to be in a text box in Excel. I would also like the option that a Word document could also be included as an attachment.

Not sure what you mean. Do you mean that you want to be able to prompt the user for the message? Or add a text box to a worksheet and take the message from that? Or put up a userform with a textbox and take the message from that? My Sub includes the ability to include an attachment, as described.

3. Subject line would be located in a particular cell in Excel along where the text box is located.

You just need to pass the contents of the cell to the Sub as the subject parameter.

4. I want only 1 email sent via TO: and the balance of emails via BBC: My customers and myself do not want our lists set out to others. The 1 going via TO: could also come from a cell in Excel. This would probably always be the email address of the sender, such as mine.

I would have to look that one up. Feel free to beat me to it.

I believe that most users will not be using Outlook and this could cause difficulty for them.
For these customers, it would be excellent if they could use Hotmail. As I am sure most people know that, Hotmail and others limit the number of emails you can send at one time. Personally, I can only send 100 emails per send in Hotmail.

I know there are ways to send mail from Excel without using Outlook, but I have not done it. However, a solution has been posted on this board within the last few months. You might try searching for "email SMTP". That solution does not literally use the Hotmail interface in any way, IIRC, but allows your From and ReplyTo address to be whatever you want, including a Hotmail address. I can't remember if you have to specify the address of an SMTP server.

Hi 6StringJazzer . . .

Quote:
2. I would like the message to be in a text box in Excel. I would also like the option that a Word document could also be included as an attachment.

You:
Not sure what you mean. Do you mean that you want to be able to prompt the user for the message? Or add a text box to a worksheet and take the message from that? Or put up a userform with a textbox and take the message from that? My Sub includes the ability to include an attachment, as described.

Me:
Part of my question was can data for text can be drawn from a Excel text box. I don't know how data would be supplied. I wouldn't want it to be required to be typed into the html code.
If the text box were to be empty, which I think should always at least say something like "Hi" the text area of the email would be blank or whatever was in the Excel Text Box.. Actually, I think that the text box would always be used and that optional attachments (files - such as Word documents) could be used and placed in the attachment box just like a normal email.


Quote:
4. I want only 1 email sent via TO: and the balance of emails via BBC: My customers and myself do not want our lists set out to others. The 1 going via TO: could also come from a cell in Excel. This would probably always be the email address of the sender, such as mine.

You:
I would have to look that one up. Feel free to beat me to it.

Me:
Any idea where I could look?


Quote:
I believe that most users will not be using Outlook and this could cause difficulty for them.
For these customers, it would be excellent if they could use Hotmail. As I am sure most people know that, Hotmail and others limit the number of emails you can send at one time. Personally, I can only send 100 emails per send in Hotmail.
I know there are ways to send mail from Excel without using Outlook, but I have not done it. However, a solution has been posted on this board within the last few months. You might try searching for "email SMTP". That solution does not literally use the Hotmail interface in any way, IIRC, but allows your From and ReplyTo address to be whatever you want, including a Hotmail address. I can't remember if you have to specify the address of an SMTP server.

You:
I know there are ways to send mail from Excel without using Outlook, but I have not done it. However, a solution has been posted on this board within the last few months. You might try searching for "email SMTP". That solution does not literally use the Hotmail interface in any way, IIRC, but allows your From and ReplyTo address to be whatever you want, including a Hotmail address. I can't remember if you have to specify the address of an SMTP server.

Me:
This thought is far beyond me. If this can be done and made simple, many people would use it.

Items 1 & 3 seem doable.

Thanks Again For Your Help & Ideas

Originally Posted by Launchnet

Part of my question was can data for text can be drawn from a Excel text box.

My question is, basically, what do you mean by "text box"? Describe what the user will see.

You:
I know there are ways to send mail from Excel without using Outlook, but I have not done it. However, a solution has been posted on this board within the last few months. You might try searching for "email SMTP". That solution does not literally use the Hotmail interface in any way, IIRC, but allows your From and ReplyTo address to be whatever you want, including a Hotmail address. I can't remember if you have to specify the address of an SMTP server.

Me:
This thought is far beyond me. If this can be done and made simple, many people would use it.

I suggest you do the search and look at the solution that was posted. It may be sufficiently simple for you. If I get a little extra time I will look as well.

On any worksheet you can place a Text Box. You can size it & color it. You can type volumes of info into it. The Text Box is found on the Drawing Tool Bar.

I just looked this morning and there is no way that I can see of naming it, so without some type of identification, I can not use the Text Box to supply text to the eMail.

Could the text in a word document be used for extracting the text in to the text field of the email ?

I know that a Word Doc can be attached to the email.

Thanks for your continued input.

To add address for bcc, use the code I provided and add:

Please Login or Register  to view this content.

where AddressList is a String that has a list of emails, separated by semicolons.

Originally Posted by Launchnet

On any worksheet you can place a Text Box. You can size it & color it. You can type volumes of info into it. The Text Box is found on the Drawing Tool Bar.

Thanks for the clarification. The term "text box" is also used for the control found on the Control Box toolbar (Developer bar in 2007). It's unfortunate that Microsoft calls them both the same thing.

I just looked this morning and there is no way that I can see of naming it, so without some type of identification, I can not use the Text Box to supply text to the eMail.

It is possible to refer to a specific text box in VBA code, but the user can't name it. You have to determine the name then use that in VBA. For example, this code finds all the shapes on a worksheet, then shows the text for each.

Please Login or Register  to view this content.

If you only have one text box in the worksheet, and no other graphical shapes, you can extract the text like this:

Please Login or Register  to view this content.

Could the text in a word document be used for extracting the text in to the text field of the email ?

Yes; Excel VBA can import the object library for Word so that you can include code in Excel VBA to manipulate Word documents. I have some experience with that but not enough to do this quickly and easily.