How to send Crystal Report 7 report as email attachment(excel or pdf)

Today I was asked to find out a solution for sending   crystal report 7 reports in our vb6 application as pdf attachment through outlook. I googled a loot and finally arrive at a solution for the same. I thought it will be good if I share this, so that some others can also get help. The only drawback that I see in this is, as our application using crystal reports 7.Using using crystal reports 7 we will not be able to export to pdf ,so I decided to do the code  to covert the report to excel and attach it to an outlook mail (Programmatically).I also learned after googling that exporting to Pdf pragmatically is not supported if the report is in version 7.PDf exporting is only supported by crystal report from 8.5 onwards.

For achieving these follow the below steps

Open vb6 and open a project.

Add a reference to Crystal report Engine 7 Object Library

Add the controls “Microsoft MAPI Controls 6.0” to the vb6 tool bar
Drag the controls “MAPIMessages” and “MAPISession” to the form.

Add a module and declare the constants as below.

Public Const WS_MINIMIZE = 536870912 ' Make a window of minimum size.

Public Const WS_VISIBLE = 268435456 'Make a window that is visible when it first appears (for overlapping and pop-up windows).
Public Const WS_DISABLED = 134217728 'Make a window that is disabled when it first appears.
Public Const WS_CLIPSIBLINGS = 67108864 'Clip child windows with respect to one another.
Public Const WS_CLIPCHILDREN = 33554432 ' Exclude the area occupied by child windows when drawing inside the parent window.
Public Const WS_MAXIMIZE = 16777216 'Make a window of maximum size.
Public Const WS_CAPTION = 12582912 'Make a window that includes a title bar.
Public Const WS_BORDER = 8388608 'Make a window that includes a border.
Public Const WS_DLGFRAME = 4194304 'Make a window that has a double border but no title.
Public Const WS_VSCROLL = 2097152 'Make a window that includes a vertical scroll bar.
Public Const WS_HSCROLL = 1048576 'Make a window that includes a horizontal scroll bar.
Public Const WS_SYSMENU = 524288 'Include the system menu box.
Public Const WS_THICKFRAME = 262144 ' Include a thick frame that can be used to size the window.
Public Const WS_MINIMIZEBOX = 131072 ' Include the minimize box.
Public Const WS_MAXIMIZEBOX = 65536 ' Include the maximize box.
Public Const CW_USEDFAULT = -32768
'Const CFies_Preview = WS_MAXIMIZE Or WS_CAPTION
Public Const CFies_Preview = WS_MAXIMIZE Or WS_BORDER Or WS_CAPTION Or WS_MAXIMIZEBOX Or WS_MINIMIZEBOX Or WS_SYSMENU Or WS_THICKFRAME

Coming back to the form add controls as shown below and put the below code in respective control and form events.

Private CRPApplication As CRPEAuto.Application 'for reports

Dim crpPrintWindowObj As CRPEAuto.PrintWindowOptions

Private CRPReport As CRPEAuto.Report

Private CRPParameterFieldDefs As CRPEAuto.ParameterFieldDefinitions

Private CRPParameterFieldDef As CRPEAuto.ParameterFieldDefinition

Dim reportname As String

Public Sub WIndowsprint(sendmail As Boolean)

Dim strname As String

strname = "rptchartofaccounts.rpt"
strname = App.Path + "\reports\" + strname
Set CRPApplication = CreateObject("Crystal.CRPE.Application")

CRPApplication.LogOnServer "pdsodbc.dll", "odbcname", "databasename", "username", _

"password" 'ODBC login infomrtaion 

Set CRPReport = CRPApplication.OpenReport(strname)
Set crpPrintWindowObj = CRPReport.PrintWindowOptions
Set CRPParameterFieldDefs = CRPReport.ParameterFields
If sendmail Then

If txtRecepient.Text = "" Then

MsgBox ("Enter recipients it you want yo send this report as a mail")

txtRecepient.SetFocus
Exit Sub
End If
lblStataus.Caption = "Your report is being genereate,Please wait a moment........"
DoEvents
'section for generating report and exporting to excel
Set CrystalOptions = CRPReport.ExportOptions
CrystalOptions.DestinationType = crEDTDiskFile
CrystalOptions.DiskFileName = App.Path + "\Temp\" + reportname + ".xls"

CrystalOptions.FormatType = crEFTExcel50 'cahnge to crEFTPortableDocFormat for pdf, but supported on 'crystal report 8.5 ans above

lblStataus.Caption = "Signing on to your mail software(Outlook)"
CRPReport.Export (False)
' build and send the email and attachment file to outbox
MAPISession1.UserName = ""
MAPISession1.SignOn

thisSessionHandle = MAPISession1.SessionID

MAPIMessages1.SessionID = thisSessionHandle
With MAPIMessages1
.Compose
.RecipType = mapToList
.RecipDisplayName = txtRecepient.Text
.RecipAddress = "SMTP:" + txtRecepient.Text
.MsgSubject = txtSubject.Text
.MsgNoteText = "Body content of your email message "
'this section is for attaching the excel as attachment with the mail
.AttachmentIndex = 0
.AttachmentType = 0
.AttachmentPathName = App.Path + "\Temp\" + reportname + ".xls" '"C:\WINDOWS\TEMP\xxxx.PDF"
.AttachmentPosition = Len(.MsgNoteText) - 1
.AttachmentName = reportname + ".xls"
.Send True 'chnage to false if you want to send the mail with out showing the compose window to user(Directly to outbox)
End With
MAPISession1.SignOff
lblStataus.Caption = ""
Else
With crpPrintWindowObj
.HasExportButton = True
.HasPrintButton = True
.HasPrintSetupButton = True
.HasSearchButton = True
End With

CRPReport.Preview "Contra", 0, 0, CFies_Preview

End If
Exit Sub
End Sub

Private Sub cmdShowReport_Click()

WIndowsprint False

End Sub

Private Sub dmSesndEmail_Click(Index As Integer)

WIndowsprint True

End Sub

Private Sub Form_Load()

reportname = "chartofaccounts"

txtSubject.Text = reportname

End Sub