Thread: Attachment Gets Skipped Code Becomes Slow

Hello Forum,
I am using the following code to generate 5000 E mails with a PDF attachment which lives on my desktop. Problem is:
1/ Attachment does not show up when I use "SEND “or "DISPLAY". After few e mails are generated.
2/The code is too slow to respond and takes good amount of time to generate e mails, after a while, and slows down too much ,excel also crashes, is it because of the size of database, as I am using it to generate around 5000 e mails at a go .Any assistance will be highly appreciated.
Here is the code for review:

Option Explicit
Sub Mail_ActiveSheet()
'Working in 2000-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 OutApp As Object
    Dim OutMail As Object
    Dim Rng As Range
    Dim LR As Long
    Dim eRng As Range
    Dim eCell As Range
    LR = Range("L" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    Set eRng = Range("L2:L" & LR)
    
    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 2000-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")

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

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            For Each eCell In eRng
    If eCell.Value <> "" Then
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = eCell.Value
    .CC = "My Claims"
    .Subject = eCell.Offset(0, -5)
            '.Attachments.Add Destwb.FullName
            'You can add other files also like this
            .Attachments.Add ("C:\Users\nutty\Desktop\Invites.pdf") 
            ' change it as per your content location 
            '.Send   'or use
            .Display
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    

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

    Set OutMail = Nothing
    Set OutApp = Nothing

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

Hi N
You've asked me via PM to look at this Thread. Sending that many emails at "a go" (your term) sounds like spam to me...and perhaps to your Email Client also.

My Email client (Roadrunner) allows me to send up to 100 email addresses in an email. I've built a work around that creates multiple "Distribution Lists" such that I can send the same email to more than 100 (in my case, 120).

I can't imagine a legitimate business reason to send 5000 emails at "a go". You'll need to make a STRONG argument to get help on this issue.

Yes J you are right here even I might have suspected anyone who is crazy to be sending 5000( now it 7242,as per the last count ) e mails at a go..But If you remember I mentioned sendind e mails to vendors requesting them to care for enviornment and opt for electronic document in place of physical one and I need to send them sample for what documents gonna look like,to get them to approve it.Please refer to : http://www.excelforum.com/outlook-pr...ttachment.html Post # 1