I have a macro which copies a sheet to a new workbook and then saves the workbook and attaches it to an e-mail.
When I open the attachment in the e-mail, it shows the following error:
"the file that you are trying to open is in a different format than specified by the
file extension. Verify that the file is not corrupted and is from a trusted source before
opening the file. Do you want to open the file now?"
When I click "yes" to open the file, the file is fine. However, when the email is sent to customers, the error scares them a bit and a lot of them do not wish to open the file.
My code is as follows:
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
Sheets("Custom").Copy
Set Destwb = ActiveWorkbook
With Destwb
FileExtStr = ".xls"
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 = "Confirmation Letter - Ref " & Ref & ""
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.LogOn
Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr
With OutMail
' Compose the message
Msg = EmailBody
.To = "" & Email_To & ""
.CC = ""
.BCC = ""
.Subject = "" & Email_Subject & ""
.Body = Msg
.Attachments.Add Destwb.FullName
If Attachment1 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment1.Value
End If
If Attachment2 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment2.Value
End If
If Attachment3 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment3.Value
End If
If Attachment4 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment4.Value
End If
If Attachment5 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment5.Value
End If
If Attachment6 = "" Then
' Do Nothing
Else
.Attachments.Add "c:\HBPOS\Letters\" & Attachment6.Value
End If
.Send
End With
.Close SaveChanges:=False
End With
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
Thanks in advance
Bookmarks