+ Reply to Thread
Results 1 to 2 of 2

Excel sending duplicate emails through Outlook

Hybrid View

  1. #1
    Registered User
    Join Date
    02-09-2013
    Location
    Alberta, CA
    MS-Off Ver
    O365
    Posts
    80

    Excel sending duplicate emails through Outlook

    I've scoured the web on this issue but have not been able to find a solution. There was a mention of it from a poster back in 2009 and he did not receive any replies. I've asked about this a year ago and nothing then either so hoping something new has come around since.

    Basically what happens is that I will press the email button on the sheet, it does its thing and then the recipient receives 1-3 emails usually. It has gotten to the point no one wants to use it anymore due to customers constantly asking why we keep sending them multiple emails. I can't quite figure it out as even when I disable the auto send and stop at the email compose screen it only shows the recipient listed once and not multiple times.

    This is the email code I am using.

    Sub Email_Minutes()
        Sheets("Email").Visible = True
        Worksheets("Email").Select
        Call Compile
        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 = Range("I4").Value & " File"
    
        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 = "[email protected]"
                .CC = ""
                .BCC = ""
                .Subject = Range("I4").Value & " Text"
                .Body = "Hello," & vbNewLine & vbNewLine & "more text"
                .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
        
        Sheets("Email").Visible = False
        Sheets("Formulas").Visible = False
        Sheets("Minutes").Select
        
    End Sub
    Any ideas or suggestions?

    Edit: I should make mention that we use Gmail for business as the mail server if that makes any difference.

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Excel sending duplicate emails through Outlook

    hi,
    I tested and I rcvd one email only. (I deleted "call complie")
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sending Outlook emails from Excel; Limits to three emails only?
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2013, 06:53 AM
  2. Sending Outlook emails containing Excel data
    By Richard Buttrey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2009, 10:24 AM
  3. Can I prevent Outlook from sending duplicate emails
    By joe41 in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 06-09-2009, 07:18 AM
  4. Replies: 1
    Last Post: 09-20-2008, 05:34 PM
  5. [SOLVED] Sending emails from Excel with Outlook Signature
    By Erik in forum Excel General
    Replies: 1
    Last Post: 04-24-2006, 02:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1