Excel Attachment in Email Becomes Corrupt
-
I have a DTS package that runs and attaches an Excel file to an email before it sends out the email. The file seems to become corrupt when the DTS package attaches it. When I browse to where the file is stored on the server the file is fine. It seems like somehow the package corrupts the excel file when it attaches it to the email. Any ideas on how to fix or troubleshoot this?
-
If the the email is arriving with a corupted attachment it could well be the email server. We had some problems with Exchange 2000 not using the correct encoding nad the Excel files used to show as a whole pile of gibberish in the body of the message.
Can't recall what the exact fix was and we haven't had any problem of the sort since we've been running on Exchange 2003.
--------------------
Colt 45 - the original point and click interface
-
Look at the header of the received email and check the MIME type used for encoding. Usually it should be delivered as base-64 encoded otheres may use octet-stream that has other encoding. If you are trying to attach it as a text file.. it will always be corrupt on delivery.
-Mike Gercevich
-
Here is what it is delivered as I believe.
Field Name: X_MimeOLE
Data Type: Text List
Data Length: 49 bytes
Seq Num: 1
Dup Item ID: 0
Field Flags:
"Produced By Microsoft MimeOLE V6.00.2800.1409"
-
Are you using the built-in Send Email task in DTS? or are you using an Active-x Script that uses a third party SMTP Emailer?
If you are using the built-in task... you may want to check the file extension of your Excel attachment. It appears that it is attempting to attach a "text list" which would encode the file wrong. Make sure it is (.xls). If you are attaching a comma seperated value (csv) that is compatible with Excel... be sure the file has a .csv extension.
Hope this helps,
-Mike Gercevich
-
I'm sending the mail using an Active X Script. See code below:
'**************************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Send email with file attachment
Dim objMail'Mail Object
Dim strFilePath, strSubject, strHeader, strEmailBody, strFooter
strFilePath = DTSGlobalVariables("gvEmailFileName").Value
strSubject = "Dimension Comparison Report"
strHeader = "*************************************************"
strFooter = (Chr(13)) + (Chr(13)) + (Chr(13)) & _
strHeader + (Chr(13)) & _
"User = " + DTSGlobalVariables("gvUSER").Value + (Chr(13)) & _
"AppSet = " + DTSGlobalVariables("gvAPPSET").Value + (Chr(13)) & _
"Application = " + DTSGlobalVariables("gvAPP").Value + (Chr(13)) & _
"OLAPServer = " + DTSGlobalVariables("gvOLAPSERVER").Value + (Chr(13)) & _
"SQLServer = " + DTSGlobalVariables("gvSQLSERVER").Value + (Chr(13)) & _
"SQLUser = " + DTSGlobalVariables("gvSQLUSER").Value + (Chr(13)) & _
"AppPath = " + DTSGlobalVariables("gvAPPPATH").Value + (Chr(13)) & _
"DataPath = " + DTSGlobalVariables("gvDATAPATH").Value + (Chr(13))
Set objMail = CreateObject("CDONTS.NewMail")
objMail.To = DTSGlobalVariables("gv_EM_TO").Value
objMail.CC = DTSGlobalVariables("gv_EM_CC").Value
objMail.From = DTSGlobalVariables("gv_EM_FROM").Value
objMail.Subject = strSubject
strEmailBody = "Attached is the Dimension Comparison Report. It was run by " & _
"the user id - "+DTSGlobalVariables("gvUSER").Value & _
" on the server - " +DTSGlobalVariables("gvSQLSERVER").Value & _
" in the AppSet - "+DTSGlobalVariables("gvAPPSET").Value & _
" on the Application - "+DTSGlobalVariables("gvAPP").Value & _
". This report can also be found in the directory: " & _
DTSGlobalVariables("gvFileReportDirectory").Value + "on the Server -" & _
DTSGlobalVariables("gvSQLSERVER").Value & _
strFooter
objMail.AttachFile (strFilePath)
objMail.Body = strEmailBody
objMail.Send
Set objMail = Nothing
Main = DTSTaskExecResult_Success
End Function
-
You may want to use the CDO object from an active-x script to send your message and attachment:
Here is a link that can easily be adapted to a DTS Active-X Script Task... http://www.asp101.com/articles/john/cdosmtprelay/default.asp
-Mike Gercevich
-
Since you are using CDONTS.. which I believe is only supported on NT4 (yikes) (cdonts was deprecated and you now should be using CDO), you need to set your MailFormat property to 0 and your AttachFile Encoding Method to a 1:
Add in the following....
objMail.BodyFormat = 1
objMail.MailFormat = 0
objMail.AttachFile strFilePath, , 1 '** will set the Encoding Method to Base-64
-Mike Gercevich
-
That works. Only thing is now it attaches two copies of the Excel File. Do you know why that happens?
Thanks for your help.
-
I figured it out. I was attaching the file in two places. Thanks again for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply