We help IT Professionals succeed at work.

PROBLEM SENDING EMAIL FROM EXCEL

890 Views
Last Modified: 2013-12-24
Hi...

I need to send an email to more than 80 people at a time from an excel file when pressing a Command Button.
Email adresses are in column E..from row 10 to row 100

I copied some code from Ron de Bruin that It works perfectly when the email goes to a smaller group... but when I need to send an email to the complete group it hangs...

It is possible to edit the code, or there is another way to create an empty email (with all the email addresses in the bbc field) when pressing the Command Button ?

Thank you for your time.
Attached is the code I´m using.
Private Sub CommandButton1_Click()

  Dim cell As Range
    Dim strto As String
    For Each cell In ThisWorkbook.Sheets("CUOTAS").Range("E10:E50")
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & "; "
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
            Recipient = "roberto@xxx.com"
            Recipientbcc = strto
                       
             HLink = "mailto:" & bcc & "?"
             HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" & Recipientbcc & "&"
          ActiveWorkbook.FollowHyperlink (HLink)
          
    End Sub

Open in new window

Comment
Watch Question

avalenzuelaIT Manager
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Author

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.