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
    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("") = 2 'send via port
        .Item("") = "ServerName" 'TODO:update the SMTP server name here
        .Item("") = 25
    End With
    With iMsg
        Set .Configuration = iConf
        .From = "" 'TODO:change email address here
        .To = "" 'TODO:change email address here
        .Subject = "Test message with PDF Attachment"
        .HTMLBody = "Please find the attache excel pdf contents report"
        .AddAttachment (filepath)
    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 (2)

  1. makchal041 says:

    Well thanks a very helpful tutorial indeed.

  2. peterandrew011 says:

    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.