How to send Excel contents as PDF attachment with the email using CDOSYS

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.

 Sub SendMail()

 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

     Range("A1:I22").Select

     Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

         filepath, _

         Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _

         :=False, OpenAfterPublish:=False

  'Setting up CDOSYS configuration to send out the email 

 Set iMsg = CreateObject("CDO.Message")

     Set iConf = CreateObject("CDO.Configuration")

     Set Flds = iConf.Fields

     With Flds

         .Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'send via port

         .Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "ServerName" 'TODO:update the SMTP server name here

         .Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

         .Update

     End With

     With iMsg

         Set .Configuration = iConf

         .From = "xyz@domain.com" 'TODO:change email address here

         .To = "abc@domain.com" 'TODO:change email address here

         .Subject = "Test message with PDF Attachment"

         .HTMLBody = "Please find the attache excel pdf contents report"

         .AddAttachment (filepath)

         .Send

     End With

     Set iMsg = Nothing

     Set iConf = Nothing

 End Sub

Hope this helps.

Please feel free to write me if you have question related to Microsoft Messaging APIs.

Comments

• Anonymous
  April 27, 2009
  Well thanks a very helpful tutorial indeed.
• Anonymous
  April 27, 2009
  Hey thanks Brijs for sharing this with us, this is a very helpful tutorial and the content is very good. Useful information about how to send Excel content as PDF attachment, thanks again.