One of my customer would like to send Excel 2007 worksheet contents as email using CDOSYS. We are facing issues regarding formatting of the contents in the resultant emails on the different email clients. Then we decided to send contents as PDF attachment to avoid such issues.
Here is the sample code VBA snippet used:
NOTE: Following programming examples is for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This sample code assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. This sample code is provided for the purpose of illustration only and is not intended to be used in a production environment.
Dim filepath As String
filepath = "\\server\test\Excel 2007 Chart.pdf" 'TODO:change filepath for the temp pdf file
'Exporting range of the excel contents which need to sent out
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
'Setting up CDOSYS configuration to send out the email
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'send via port
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "ServerName" 'TODO:update the SMTP server name here
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Set .Configuration = iConf
.From = "email@example.com" 'TODO:change email address here
.To = "firstname.lastname@example.org" 'TODO:change email address here
.Subject = "Test message with PDF Attachment"
.HTMLBody = "Please find the attache excel pdf contents report"
Set iMsg = Nothing
Set iConf = Nothing
Hope this helps.
Please feel free to write me if you have question related to Microsoft Messaging APIs.