Email macro sending Two emails for each marco execution

[Vlad717]

Hi everyone,

I've been using a macro from rondebruin.nl and so far it has been working great with one exception. One of the recipients mentioned they are getting everything twice and upon checking... sure enough, everyone has been getting the emails twice and hasn't bothered mentioning it.

This is the code with only a couple fields tweaked. The To: field, CC: mostly. I can't quite figure out why it is sending duplicate emails though. Tried it from a couple machines and both do it so I don't think it is a setting in excel or outlook. Any ideas?

Sub Email_PODRAWB()
    'Working in Excel 2000-2013
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Sheets("Assignments").Visible = True
    Worksheets("Assignments").Select
    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

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

    Set Sourcewb = ActiveWorkbook

    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    With Destwb
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        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 With
        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False

    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
            .to = Range("L49").Value
            .CC = Range("M49").Value
            .BCC = ""
            .Subject = Range("J49").Value & " ABCD " & Range("B6").Value & " " & Range("K49").Value
            .Body = "Hello" & vbNewLine & vbNewLine & "Text One " & Range("J49").Value & " Text Two " & Range("B6").Value & "." & vbNewLine & vbNewLine & "Text Three"
            .Attachments.Add Destwb.FullName
            .Send
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Worksheets("Assignment").Select
    Sheets("Assignments").Visible = False
    Sheets("Assignment").Select
    ActiveSheet.Unprotect "Sara"
    ActiveWorkbook.Unprotect "Sara"
    Range("O45").FormulaR1C1 = "Powered via Calgary"
    ActiveSheet.Protect Password:="Sara", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("C7").Select
End Sub

[TMS]

There's no obvious duplication in the code so maybe it's in the source data. Can you post a sample workbook with any sensitive information removed?


Regards, TMS

[Vlad717]

I appreciate the help TMS. I've uploaded the workbook with company data removed.

Basically how it works... employees enter the data in sheet called "Assignment". Anything entered is being also entered under the "Assignments" sheet. Unhidden presently. Reason being is that the company we submit to requests the submitted sheet is unaltered although it is rather unsightly of a sheet so if I was adding the macro to it I might as well change it so it isn't so large. Then its also only women who use this specific sheet by chance and they wanted it a bit more color to brighten up their day. A bit redundant, I know.

Besides that one step though, the macro pulls from the assignments sheet only.

Example Workbook.xlsm

[TMS]

Seems to work OK for me. I changed the .Send to .Display so I could see what was generated without actually sending it.

Only one email created with one email address.

I don't suppose you're duplicating the email address in the .cc box?

Regards, TMS

[Vlad717]

I changed it to .Display and it does in fact show just the one address.

Although when I change it back to .Send and actually send it, I do in fact get two instances of it.

We use Gmail for Business and I've tried it on a couple machines. At this moment I'm trying it on a fresh install of windows 7 with outlook 2010. When I use .Display, it shows one email as the send to:. Although when I send it out, it shows 2 emails in the sent folder and I get 2 emails on the receiving end.

At this point I don't have any email in the .cc box, sending it to one email only. Very puzzling.