Need to send attachment in XLSX format using CDO mail

Question

Hi Iam using follwing code as a sample to send a mail with attachment via SMTP, but the attachment what it send is in XLSM format i need that to be in XLSX (non macro) format. Kindly help me to fo this.

Option Explicit

'This procedure will mail the whole workbook
'You can 't send a Workbook that is open with CDO.
'That's why it use SaveCopyAs to save it with another name and send that file.

Sub CDO_Mail_Workbook()
'Working in 2000-2007
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim iMsg As Object
    Dim iConf As Object
    '    Dim Flds As Variant

Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
    If wb.FileFormat = 51 And wb.HasVBProject = True Then
        MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
               "Save the file first as xlsm and then try the macro again.", vbInformation
        Exit Sub
    End If
End If

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

'Make a copy of the file/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1)))

wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

'    iConf.Load -1    ' CDO Source Defaults
'    Set Flds = iConf.Fields
'    With Flds
'        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here"
'        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'        .Update
'    End With

With iMsg
    Set .Configuration = iConf
    .To = "ron@debruin.nl"
    .CC = ""
    .BCC = ""
    .From = """Ron"" <ron@something.nl>"
    .Subject = "This is a test"
    .TextBody = "This is the body text"
    .AddAttachment TempFilePath & TempFileName & FileExtStr
    .Send
End With

'If you not want to delete the file you send delete this line
Kill TempFilePath & TempFileName & FileExtStr

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

score 0 · Answer 1 · answered May 15 '14 at 15:24

0

I think what you'll need to do, is have this code reside in an add-in. That way you aren't trying to send the existing file with code via email.

answered May 15 '14 at 15:24

guitarthrower

5,624
3
29
37

score 0 · Answer 2 · answered May 15 '14 at 15:25

You are sending workbook which you are running code from so it must be .xlsm and so you send it.
You must either create a copy of your workbook without macros and then send this copy or move your macro to PERSONAL (assuming macro you posted is the only code contained in your workbook)

Need to send attachment in XLSX format using CDO mail

2 Answers2