Macro to export AND mail report as PDF

[#1]

2 days... that's what took me to get it right !! Here's how I did it :

1. Install Bullzip PDF Printer (I tried CutePDF, PDF X-change, PdfCreator,.. bullzip is the only one that works)

2. Macro :

Sub Dagrapport
vReport = "QVreportname" 'Set report
vName = "reportname" 'Name of output pdf
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport
reportFile = "reportpath" & vName &".pdf" 'Setting outputname
MyPrintPDFWithBullZip(reportFile) 'Call pdf printer
ActiveDocument.GetApplication.Sleep 5000
zendDagrapport
End sub
FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)
set obj = CreateObject("Bullzip.PDFPrinterSettings")
obj.SetValue "Output" , pdfOutputFile
obj.SetValue "ConfirmOverwrite", "no"
obj.SetValue "ShowSaveAS", "never"
obj.SetValue "ShowSettings", "never"
obj.SetValue "ShowPDF", "no"
obj.SetValue "RememberLastFileName", "no"
obj.SetValue "RememberLastFolderName", "no"
obj.SetValue "ShowProgressFinished", "no"
obj.SetValue "ShowProgress", "no"
obj.WriteSettings True
END FUNCTION
'===========================================================================
'===========================================================================
function zendDagrapport()
Dim objEmail
Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM
SMTPServer = "IP Mailserver"
Const SMTPPort = 25 ' Port number for SMTP
Const SMTPTimeout = 60 ' Timeout for SMTP in seconds
'Sending mail
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
'---------------------------------------------------------------------
' SMTP server details
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout
.Update
'---------------------------------------------------------------------
End With
objEmail.To = "to emailaddress" 'Email Recipient
objEmail.From = "from emailaddress" 'Email Sender
objEmail.Subject = "emailsubject" ' Subject
objEmail.TextBody = "emailbody" 'Text Body
objEmail.AddAttachment "link to file" ' Attachement
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
' msgbox ("Test Mail Sent")
end function

I don't know how to post code in colour...
But it WORKS !!!!

My next step :

Make it work, so I have nothing to do anymore ! (I think I will use JIT and the commandline to open the QVW and execute the macro)

[kris_vliegen]

thanks for the post, but how can I send the PDF to more than 1 aderes? Or to an addres outside our company? (Gmail or something?)

Regards,

Kris

[Anonymous]

Hello Evrybody / Jochen,

My macro fails. Every time when  I start it up, it goes into a loop and I got the message : "Macro Running ..."

Sub Dagrapport

vReport = "RP01" 'Set report

vName = "test" 'Name of output pdf

ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport

reportFile = "C:\temp\" & vName &".pdf" 'Setting outputname

MyPrintPDFWithBullZip(reportFile) 'Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

End sub

FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)

set obj = CreateObject("Bullzip.PDFPrinterSettings")

obj.SetValue "Output" , pdfOutputFile

obj.SetValue "ConfirmOverwrite", "no"

obj.SetValue "ShowSaveAS", "never"

obj.SetValue "ShowSettings", "never"

obj.SetValue "ShowPDF", "no"

obj.SetValue "RememberLastFileName", "no"

obj.SetValue "RememberLastFolderName", "no"

obj.SetValue "ShowProgressFinished", "no"

obj.SetValue "ShowProgress", "no"

obj.WriteSettings True

END FUNCTION

A almost finishd my work, I need to generate one pdf, and all time fails.

Regards

Jacek.

[Anonymous]

Yes, you can, you need to use ";" between email.

objEmail.To = "to emailaddress;to emailaddress;to emailaddress;to emailaddress;to emailaddress;to emailaddress;to emailaddress"

[#5]

Hello Jochen,

Thank you very much for the code. I have list of 100 users associated with specific region and I want to send pdf to them via email. They should only see their data. Is it possible with this macro?

Thank you

Parth

[Anonymous]

Hello Parth,

Yes, you can specify selection by chooseing them in macro eg:

ActiveDocument.UnlockAll
ActiveDocument.ClearAll true
ActiveDocument.Fields("Year").Select YearSelector
ActiveDocument.Fields("Date").Select DaySelector
ActiveDocument.Fields("Currency").Select CurrencySelector
ActiveDocument.Fields("Country").Select CountrySelector
ActiveDocument.Fields("Ownership").Select OwnershipSelector

Regards

Jacek.