How to Use VB macros to send Email through Outlook in QlikView

[olaoyesunday1]

Hi all,

Please, can anyone explain or send me a link that explain step by step on how to us VB Script Macros to Send email through Outlook In QlikView?

Thanks 

[marcus_sommer]

I doubt that Outlook is really suitable as mail-client for an automated purpose because it was already a quite poor matter 20 years ago directly in Office with VBA, for example with an inbuilt delaying-logic of 30 seconds and similar stuff to prevent spam. If you really want to use Outlook go to the MS Office communities and create with their help and examples a working logic with VBA. After that you could translate/adapt it to VBS in QlikView.

More common is to avoid any mail-client and using CDO.Message, see:

Solved: Sending mails macro vbs - Qlik Community - 390730

[olaoyesunday1]

Hi Marcus,

 Thanks for the insight but please, can you guide me on how to use CDO.Message step by step.

I checked the link you sent above, I don't really understand configuration area, I checked some links, the pages have been removed. 

please, can you guide me step by step I am still a beginner in QlikView.

Thanks,

Sunday.

[marcus_sommer]

There is not much more as those few script-lines in the provide link. You need just to know the mail-server name/ports which you may derive from the Outlook settings or asking your IT guys and then a bit playing with the code and various settings in a dummy-application. It's the way I got this stuff working. 

[olaoyesunday1]

Okay, I will try it and revert.

[olaoyesunday1]

Please, can anyone help me re-write the below code to be sending report to the recipient every 20mins.

I used these macros which work perfectly: 

sub mSendMail

Dim objOutlk 'Outlook : This line declares a variable named objOutlk as an object. It's intended to represent an instance of the Outlook application.

Dim objMail 'Email item : This line declares a variable named objMail as an object. It's intended to represent an email item that will be created using Outlook.

Const olMailItem = 0 'This line declares a constant named olMailItem and assigns it the value 0. This constant is used to specify the type of Outlook item being created, in this case, an email.

Set objOutlk = CreateObject("Outlook.Application") ' This line creates a new instance of the Outlook application and assigns it to the `objOutlk` variable.

Set objMail = objOutlk.createitem(olMailItem) 'This line creates a new email item using the Outlook application instance stored in objOutlk, based on the constant olMailItem which specifies that it's an email item.

' Recipient's email address
objMail.To = "example@yahoo.com"

objMail.subject = "Testing " & Date() 'This line sets the subject of the email to "Testing" followed by the current date.

objMail.attachments.add("C:\Test\Pdf Report.pdf")

objMail.HTMLBODY = "Body of the email, This is an automatic generated email from QlikView."

objMail.Send 'This line sends the email.

Set objMail = Nothing 'This line releases the memory associated with the objMail object

Set objOutlk = Nothing 'This line releases the memory associated with the `objOutlk` object

'Msgbox " Mail delivered"

end sub

I used the following macros to output the PDF through BullZip printer 

' How to use Macros to Export QlikView report to Pdf Using BULLZIP printer

Sub CreateReport

vReport = "RP01" 'Set report

vName = "Pdf Report" 'Name of output pdf

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

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

MyPrintPDFWithBullZip(reportFile) 'Call pdf printer

MyPrintPDFWithBullZip_Transaction(File_Transaction) 'Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

ActiveDocument.Save

ActiveDocument.GetApplication.Sleep 5000

End sub

FUNCTION MyPrintPDFWithBullZip(pdfOutputFile)

'set obj = CreateObject("Bullzip.PDFPrinterSettings")

set obj = CreateObject("Bullzip.PdfSettings")

obj.PrinterName = "Bullzip PDF Printer"

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

msgbox "PDF Created"

END FUNCTION

please can any one help me re-write it to be sending report every 10mins.

[marcus_sommer]

You may apply something like this:

for i = 1 to 3
   sleep 1000 * 60 * 20;
   YourMacroCode
next

and maybe increasing it to more a 3 iterations and/or adjusting the sleep-times and/or including further conditional stuff in regard to weekdays or hours.

Be aware that such approach will block the application for the entire run-time. Therefore an outside-trigger per QMC, Windows Tasks or third party tools might be more suitable.

[olaoyesunday1]

@marcus_sommer Thanks.

I wrote the code the way it pasted below , I also attached it to this reply but it is not sending mails every 20mins unless I go to the code to click Test before it would send. Do I need to use window Tasks?

Sub SendEmailAndExportPDF()

    Dim intervalMinutes 

    intervalMinutes = 20 ' Set the interval in minutes

    Do

        ' Call subroutine to export PDF

        CreateReport

        ' Wait for 20 minutes

        Application.Wait (Now + TimeValue("00:20:00"))

    Loop

End Sub

Sub CreateReport()

    Dim vReport 

    Dim vName 

    Dim reportFile 

    vReport = "RP01" ' Set report

    vName = "Pdf Report" ' Name of output pdf

    ' Print report

    ActiveDocument.PrintReport vReport, "Bullzip PDF Printer", False

    ' Setting output name

    reportFile = "C:\" & vName & ".pdf"

    ' Export to PDF

    MyPrintPDFWithBullZip reportFile

    ' Sleep for 5 seconds

    ActiveDocument.GetApplication.Sleep 5000

    ' Call subroutine to send email with the exported PDF as attachment

    mSendMail reportFile

End Sub

Sub mSendMail(pdfFilePath)

    Dim objOutlk 

    Dim objMail 

    Const olMailItem = 0

    ' Create a new instance of Outlook application

    Set objOutlk = CreateObject("Outlook.Application")

    ' Create a new mail item

    Set objMail = objOutlk.createitem(olMailItem)

    ' Recipient's email address

    objMail.To = "example@yahoo.com"

    ' Subject of the email

    objMail.Subject = "Testing " & Date()

    ' Body of the email

    objMail.HTMLBody = "Body of the email, This is an automatic generated email from QlikView."

    ' Add attachment (use the generated PDF file)

    objMail.Attachments.Add pdfFilePath

    ' Send the email

    objMail.Send

    ' Release resources

    Set objMail = Nothing

    Set objOutlk = Nothing

End Sub

Function MyPrintPDFWithBullZip(pdfOutputFile)

    Dim obj 

    'set obj = CreateObject("Bullzip.PDFPrinterSettings")

set obj = CreateObject("Bullzip.PdfSettings")

obj.PrinterName = "Bullzip PDF Printer"

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" 

    ' Write settings

    obj.WriteSettings True

    ' Show message box indicating PDF creation

    MsgBox "PDF Created"

End Function

[marcus_sommer]

I'm not very familiar with do-loops and therefore I'm not sure if one is valide without any exit-condition respectively iterator. Beside this I doubt that Application.Wait is valide statement for the QlikView implementation of VBS. Wait isn't listed within the APIGuide.qvw which is an important hint although not everything is documented. Further the syntax of the call is probably not correct because a correct sleep-statement (my example above missed it also) would contain a reference to the document and calling it per GetApplication:

rem ** let QV sleep for 10 seconds **
ActiveDocument.GetApplication.Sleep 10000

and the same logic/syntax would be needed here.

All the above could be easily checked and adjusted by reducing the time-span and just calling a few msgbox - in the loop as well as within any called routine.

Beside this I think you will need an external trigger because like mentioned the macro-run will block everything which means also any update of the data or in other words it would always plot the same prints. Whereby you may also include a reload-stament within the macro but the application itself will be blocked against all other accesses.

[olaoyesunday1]

@marcus_sommer  Thanks,

I tried using Window Schedular to automate the process since the Do loop aspect is not working but it did not run. here is what I did in my batch file, : C:\Program Files\QlikView\Qv. exe  "C:\Test\sendingMails.qvw" /vCreateReport. 

"C:\Test\sendingMails.qvw" is the name of my document where I wrote the macros.

Sub CreateReport () - is the subroutine that create the Create Report in my VBScript Macros that I want to run.

Can anyone help me to know what I did wrong?