Page 1 of 2 12 LastLast
Results 1 to 10 of 18

sending email to multiple recipients

This is a discussion on sending email to multiple recipients within the Excel Questions forums, part of the Question Forums category; hello all, I would like to create a hyperlink or a button to send an email to all of my ...

  1. #1
    New Member
    Join Date
    Mar 2006
    Location
    oklahoma
    Posts
    40

    Default sending email to multiple recipients

    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

  2. #2
    New Member
    Join Date
    Mar 2006
    Location
    oklahoma
    Posts
    40

    Default

    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

  3. #3
    New Member
    Join Date
    Jan 2006
    Location
    Indianapolis, In
    Posts
    38

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2006
    Location
    oklahoma
    Posts
    40

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2005
    Location
    Redmond, WA
    Posts
    27

    Default

    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!

  6. #6
    New Member
    Join Date
    Mar 2005
    Location
    Redmond, WA
    Posts
    27

    Default

    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"" "
            .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

  7. #7
    New Member
    Join Date
    Mar 2005
    Location
    Redmond, WA
    Posts
    27

    Default

    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"" "
            .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

  8. #8
    New Member
    Join Date
    Jan 2006
    Location
    Indianapolis, In
    Posts
    38

    Default

    Looks pretty cool I'll give it a try on my data after my meeting.

    THANKS!!
    Chad

  9. #9
    New Member
    Join Date
    Jan 2006
    Location
    Indianapolis, In
    Posts
    38

    Default

    Looks pretty cool I'll give it a try on my data after my meeting.

    THANKS!!
    Chad

  10. #10
    New Member
    Join Date
    Mar 2005
    Location
    Redmond, WA
    Posts
    27

    Default

    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!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com