How to send email to multiple recipients in Excel

[merz]

I have email address in column C in Excel, and would like to send the attached Excel document to those recipients in Column C.

How on earth do I do that? I've looked for the answer for hours. I found the simple icon, or File "send to", however, it will not allow me to send to all the recipients in Column C in the Excel spreadsheet.

Please save me!

 

[VoG]

Instructions here http://www.mrexcel.com/forum/showpost.php?p=2752123&postcount=10

 

[Dazzawm]

Both VoG and myself have explained how to add the code.

 

[merz]

To: Vog:

Here is the request verbatim --
Please create a email distribution list to email a form to all the email addresses that I have on the attached spreadsheet.

I understand how to insert the code, and followed your directions, and closed it. Thank you! But now what?

I guess I'm confused how inserting the code will know where to pull the email addresses from Column B ?????

What is my next step?

 

[VoG]

Press ALT + F8, click on Mail_ActiveSheet, click the Run button.

 

[merz]

Thanks VoG:

After pressing Alt+F8 to send the document via email, how will following these steps know to grab the email addresses from my Excel spreadsheet, Column B, which is where the email addresses are located.

 

[VoG]

In your original question you said they were in column C. If they are in column B then

Sub Mail_ActiveSheet()
'Working in 97-2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim I As Long
    Dim LR As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2010, we exit the sub when your answer is
            'NO in the security dialog that you only see  when you copy
            'an sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With

    '    'Change all cells in the worksheet to values if you want
    '    With Destwb.Sheets(1).UsedRange
    '        .Cells.Copy
    '        .Cells.PasteSpecial xlPasteValues
    '        .Cells(1).Select
    '    End With
    '    Application.CutCopyMode = False

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " _
                 & Format(Now, "dd-mmm-yy h-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        LR = Range("B" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        For I = 1 To LR
            .SendMail Range("B" & I).Value, _
                      "This is the Subject line"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[merz]

To: VoG

Many thanks VoG! Appreciate your help and and patience. I know it's frustrating when you have to explain everything in detail to people someone like me who's not an Excel expert, and for that I apologize. But that's why it's so great having a site like this!!!!

Have a super day!