PROBLEM SENDING EMAIL FROM EXCEL

many mail clients including outlook, will set a limit to the address qty that could be used on a single mail.
One option is to count the email address and send an email every 20-30 accounts..

This code will send every 20 addresses but still we need to add and end marker at the end of the list
for when they are less than 20 address..



Private Sub CommandButton1_Click()

  Dim cell As Range
    Dim strto As String
   
    Dim RecipientsCount As Integer
   
    RecipientCount = 0
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("E10:E57")
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & "; "
            RecipientCount = RecipientCount + 1
        End If
       
       
        If RecipientCount = 20 Then
           Recipient = "roberto@xxx.com"
           Recipientbcc = strto
           Hlink = "mailto:" & bcc & "?"
           Hlink = "mailto:" & Recipient & "?" & "bcc=" & Recipientbcc
           ActiveWorkbook.FollowHyperlink (Hlink)
           strto = ""
           RecipientCount = 0
        End If
           
   
    Next cell
         
    End Sub

Hi avalenzuela,

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.

Thank you for your time,
Roberto.

Roberto,

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.

Patrick

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.

Roberto,

Let me tackle the 'same message to everyone' part of the question first. I'll be back later.

Patrick

Hi Patrick,

When running the code it hang and show: error Nº 2147220973 (80040213). " Transport error when connecting to the server"
And higlighted line 49 (.send)

I understand that the text that will be in the body of the email should be writed in the VB editor ?...
It is possible to only open an empty email that I'll write and attach some files in it ?

Thank you very much for your help.

Roberto.

Roberto,

Have you:

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.

Patrick

Roberto,

In the attached file you can edit a message in cell A1 on the 'Message' worksheet. Provided everything else has been set up correctly the body of the email will be that message.

Hope that gets you to the next stage.

Patrick
Pabilio-CDO-02.xls

Hi Patrick,

The code works perfectly...the problem was a typo in the password.

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.

Sorry for the "particular" problems I'm giving you.
I really appreciatte your help.
Roberto.

Roberto,

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.

Patrick

Hi Patrick,

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.

Thank you very much,
Roberto.

Roberto,

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.

Patrick

Patrick,

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.

Thank you again
Roberto.

Roberto,

I can give you a new routine that should allow you to send fully formatted text in an email. I'll see what I can do and will post it here if it works as expected.

Meantime thanks for the grade.

Patrick

Roberto,

The new code is in the attached file. You will need to edit the code exactly as the previous one and update the recipients on Sheet1. The Message sheet is hopefully self explanatory.

Patrick
Pabilio-CDO-03.xls

Patrick,

This is simply perfect !!!.... I wish I could grant you another 500 points  :-D

Thank you A LOT for your help and patience.

Cheers,
Roberto.

Roberto - It's a pleasure - pleased it helped. - Patrick