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.
Bookmarks