Solved

PROBLEM SENDING EMAIL FROM EXCEL

Posted on 2010-09-06
18
767 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

0
Comment
Question by:Pabilio
  • 10
  • 7
18 Comments
 
LVL 3

Expert Comment

by:avalenzuela
ID: 33614411
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
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33615782
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33616031
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
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33616468
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33616496
Roberto,

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

Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 33618040
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 celle

End Sub

Open in new window

Pabilio-CDO-01.xls
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33619222
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33620757
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33621625
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
0
How your wiki can always stay up-to-date

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

 
LVL 5

Author Comment

by:Pabilio
ID: 33622362
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33623048
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
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33627529
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33627581
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
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33628615
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33629095
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33629510
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
0
 
LVL 5

Author Comment

by:Pabilio
ID: 33630226
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33630420
Roberto - It's a pleasure - pleased it helped. - Patrick
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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 …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now