sending email to multiple recipients

ny92mike

New Member
Joined
Mar 28, 2006
Messages
40
hello all,

I would like to create a hyperlink or a button to send an email to all of my recipients. I started out with using the formula

=hyperlink(concatenate("mailto:",A1,", ",A2),"email all")

but I have ran into the problem that my email list exceeds the amount the formula accepts (14).

Any answers to this is greatly appreciated.

Thanks
Mike
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I've done some research and found this code:

Code:
Sub TestFile()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Sheets("email").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(olMailItem)
            With OutMail
                .To = cell.Value
                .Subject = "hello"
                .Body = "Attention:" & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
                        "Please contact me via email asap"
                .Display
            End With
            Set OutMail = Nothing
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

but this isn't exactly what I'm looking for.  This code opens an individual  message for each recipient that contains a yes beside the email.  All I need is a code that emails all recipients in the list (column b) using one message rather than an individual message for each recipient.  I don't need it to display a subject or have a greeting as I can add that once the message is displayed.  

Thanks for the help in advance.

Mike
 
Upvote 0
Mike,

Thank you, I have been searching for something along these lines and I am sure someone out here can resolve this for you (and me too)!

Chad
 
Upvote 0
Chad,

I really hope so another week of searching for the answer I'll be bald and dancing around in circles in the nut house....lol Well I'm already bald but I'll be swimming laps around everyone else in the nut house.

Mike
 
Upvote 0
Terhune5 & NYMike (and all):

I found a possible solution in my "excel scripts" file & will post in a minute - I need to describe part of the process that is shown with a screenshot (unless this board takes attachments)

Cheers!
 
Upvote 0
Weeeeellll -

I may have posted too soon & didn't read the entire message (too much multitasking): This takes a set list and e-mails with attachment if needed - but does not read from any cell values. Hope this can be at least *some* help..

Code:
Sub send_e_mail()

'==== DELAYS for 30 seconds to give file time to write to server
sngStartTimer = Timer   ' Notes the current time in seconds
Do Until Timer - sngStartTimer >= 30 ' 30 sec delay
Str (Timer - sngStartTimer)
Loop


    Dim iMsg As Object
    Dim iConf As Object
    '    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "yourserverdomain"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    With iMsg
        Set .Configuration = iConf
        'EDIT LIST TO SEND TO APPROPRIATE RECIPIENTS
        .To = "person1; person2; person3; person4"
        .CC = " person1; person2; person3; person4"

        .BCC = ""
        .From = """SENDER"" <sender@email.com>"
        .Subject = "Subject_Here"
        .HTMLBody = RangetoHTML
        .AddAttachment "c:\file_location\"
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub
 
 
Public Function RangetoHTML()
'Creates temp HTML file from data contained on shopAmex template
'
    Sheets("HTML_mail_format").Select
    Range("A1:A13").Select
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    With ActiveWorkbook.PublishObjects.Add( _
                                           SourceType:=xlSourceRange, _
                                           Filename:=TempFile, _
                                           Sheet:=ActiveSheet.Name, _
                                           Source:=Selection.Address, _
                                           HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile
End Function
 
Upvote 0
Weeeeellll -

I may have posted too soon & didn't read the entire message (too much multitasking): This takes a set list and e-mails with attachment if needed - but does not read from any cell values. Hope this can be at least *some* help..

Code:
Sub send_e_mail()

'==== DELAYS for 30 seconds to give file time to write to server
sngStartTimer = Timer   ' Notes the current time in seconds
Do Until Timer - sngStartTimer >= 30 ' 30 sec delay
Str (Timer - sngStartTimer)
Loop


    Dim iMsg As Object
    Dim iConf As Object
    '    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "yourserverdomain"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    With iMsg
        Set .Configuration = iConf
        'EDIT LIST TO SEND TO APPROPRIATE RECIPIENTS
        .To = "person1; person2; person3; person4"
        .CC = " person1; person2; person3; person4"

        .BCC = ""
        .From = """SENDER"" <sender@email.com>"
        .Subject = "Subject_Here"
        .HTMLBody = RangetoHTML
        .AddAttachment "c:\file_location\"
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub
 
 
Public Function RangetoHTML()
'Creates temp HTML file from data contained on shopAmex template
'
    Sheets("HTML_mail_format").Select
    Range("A1:A13").Select
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    With ActiveWorkbook.PublishObjects.Add( _
                                           SourceType:=xlSourceRange, _
                                           Filename:=TempFile, _
                                           Sheet:=ActiveSheet.Name, _
                                           Source:=Selection.Address, _
                                           HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile
End Function
 
Upvote 0
Oh $#((#(- double post - apologies to the Board Powers that Be - my browser timed out and I hit refresh 'stead of stop

Terhune5 - ping me ofline if you need any assistance - kinda busy day for me but I am glad to assist - I have gotten so much from this board that I can finally give somethin' back!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top