Multiple Email Attachments not wanted

Pete2507

New Member
Joined
Oct 15, 2014
Messages
1
Hi Everyone
I have a code that works pretty sweet except it attachs multiple attachment to one email. i would like it to attach each new file to a new email and send via range below.
Can anyone help??

Many Thanks
Pete
Code:
Sub EmailTechProgress()


    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rngCell As Range

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    Set Source = Nothing
    On Error Resume Next
    Set Source = ws.Range("sendout").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, " & _
            "please correct and try again.", vbOKOnly
        Exit Sub
    End If

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

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls"
        FileFormatNum = -4143
    Else
        'You use Excel 2007-2013
        FileExtStr = ".xlsx"
        FileFormatNum = 51
    End If

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    ' Loop through source of dropdown list - *** modify as needed ***
    For Each rngCell In wb.Worksheets("totals").Range("A177:A179")
        ' Set the value of A1
        ws.Range("c2").Value = rngCell.Value
        Set Dest = Workbooks.Add(xlWBATWorksheet)

        Source.Copy
        With Dest.Sheets(1)
            .Cells(1).PasteSpecial Paste:=xlPasteColumnWidths
            .Cells(1).PasteSpecial Paste:=xlPasteValues
            .Cells(1).PasteSpecial Paste:=xlPasteFormats
            .Cells(1).Select
            Application.CutCopyMode = False
        End With

        TempFilePath = Environ$("temp") & "\"
        TempFileName = "High 5 Compliance"

        With Dest
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .To = ws.Range("email")
                .CC = ""
                .BCC = ""
                .Subject = "Test"
                .Body = "Tester"
                .Attachments.Add Dest.FullName
                .Display   'or use .Display
            End With
On Error GoTo 0
            .Close SaveChanges:=False
        End With

        Kill TempFilePath & TempFileName & FileExtStr
    ' On to the next cell
        Next rngCell

    Set OutMail = Nothing
    Set OutApp = Nothing

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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,214,952
Messages
6,122,457
Members
449,083
Latest member
Ava19

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