The code you posted could work but as you said still need the end marker for the last email when is not 20 the qty of email addresses.
(Much better if it could be a single email, but I could live copying-pasting 3 times per email.)

If this help in any way, I'm working with windows 7 and Windows Live Mail.

I can and will provide you with an email system working within Excel but I need to know what sort of email you want to send. Is it the same text message to everyone or is it a part of or a whole Excel file?

My solution will have no limits to the number of recipients and will require no intervention from you once you press the button to send the emails.

Hi Patrick,
It will be the same email message to everyone.
Normally it will be only text, but some times I need to attach a file in the email (pdf, word or Excel)....
The excel file that I will attach is not the same where the send email button is located...
The excel file whre the command button is located works as a Data Base with the email addresses in Column E.
Any other question please let me know.
Thank you very much for your time.
Roberto.

Please read the code below carefully and make sure you provide the correct information in the code before you attempt to run it. Having done that then add the email addresses to Sheet1 column E and put any alpha-numeric character in column A if you want that person to receive the email.

It's all in the attached file.

Patrick

Sub CDO_Mail_Small_Text() 'This works well...ex Ron de Bruin Dim iMsg As Object Dim iConf As Object Dim strbody As String Dim Flds As Variant Dim rng As Range Dim celle As Range With Sheets("Sheet1") Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) End With Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds 'If and when you also get the Authentication Required Error you can add this three lines.' .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1' .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"' .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'edit the next line so that the SMTP mail server is correct 'to get the SMTP mail server details - in Outlook go to 'Tools/EmailAccounts/View/Change and the detail you need 'is in the box entitled "Outgoing mail server (SMTP)" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _ = "smtpmail.poundflower.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With strbody = "Our financial performance in August was a bit average. Let's see if we can do better in September" For Each celle In rng If celle <> "" Then With iMsg Set .Configuration = iConf 'edit the next line .To = celle.Offset(0, 4) ' "fred.bloggs@newwaves.com" .CC = "" .BCC = "" 'edit the next line .From = """Patrick""<patrick@poundflower.com>" 'edit the next line .Subject = "Latest" .TextBody = strbody .Send End With End If Next celleEnd Sub

1. Changed the details of the code that I indicated you needed to change?

2. Made sure that you have put the username and password if that is required in your working environment?

You can't have a simple open email which you send to a huge number of people. However you can have a text that you can have in Excel and that is used by the email macro automatically. That will save you having to edit the VBA code.

Let me know your responses first then when that has been dealt with we can think about attachments.

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

The problem with this code is that I can not easily to format the text (which is important for the kind of emails I must send)...
For the kind of emails that I must send the text should be like a letter, cannot be a single block of words...Any idea ?.
That's why I was asking to open an empty email... I need to format the body.

Please upload an Excel file with the sort of message you wish to send. Even if you cannot give me the precise words lay it out as if they were the correct words. That will help enormously.

If you cannot achieve it in Excel then we may well be stuck and unable to go any further. The reason is that I do not know how to automate Outlook. If you need to work only with Outlook then it may well be necessary for you to re-start this question in a different zone.

I agree with you... the kind of answer I'm looking for is not only an excel question...

Your solution works, just the problem with format. I don't think that giving you an example file could work due that some times the email could be similar to the example, but in other cases it will go with photos, colors, etc...

I will close this question and grant you the points... I'll use your solution to send the plain text emails.

I understand where you're coming from on this question. It's just that from within Excel I think only so much can be achieved. However when it comes to formatting data within a cell do remember that you can insert a new line by using ALT+ENTER. That can be very useful. Also it's worth remembering that you can select a word or words within a cell and make them bold or italic etc. So I think you can achieve probably much more in Excel than at first sight.

The Alt+Enter is new for me... thanks for the tip... it change A LOT the point of view I had of Excel regarding writing and text presentation.

I tried sending emails with your code with different color words, bold, italic, etc... and it arrived as plain text (No format)... the insert a new line it works fine with the email.
But with the Alt+Enter Tip I will use this code more than I was thinking.

Regarding the attachments and the especial formatting in some emails I'll do it with the code that I posted at the beginning and scratching it in order to open 3 emails instead of three.. any help on this part is very welcome.

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …

The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability.
Flipping Coins in Excel: Enter =RAND() into cell A2:
Recalculate the random variable…

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …