topic How to Open Outlook App using VBScript Macros in QlikView in Qlik Automate

How to Open Outlook App using VBScript Macros in QlikView

olaoyesunday1 — Mon, 22 Apr 2024 15:08:22 GMT

Hi All,

Please can anyone explain to me how to amend my code below to open outlook app on desktop before sending the report. If the Outlook Desktop App is not opened, the report would be in outbox until the day you open outlook on your desktop before it would send.

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 = "abcd@example.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

What should I add to open the desktop App if it has not opened?

Thanks.

Re: How to Open Outlook App using VBScript Macros in QlikView

marcus_sommer — Tue, 23 Apr 2024 09:50:53 GMT

You may try something like:

objOutlk.Visible = TRUE

which is a working approach by Excel.

If it doesn't work you will get here some ideas:

[RESOLVED] Stop / Starting Outlook via VBScript-VBForums

and here surely some more:

outlook vbs open - Google Suche

Beside this if you are performing these tasks in general with a logged user you may just manually open Outlook.

Re: How to Open Outlook App using VBScript Macros in QlikView

olaoyesunday1 — Tue, 23 Apr 2024 10:13:04 GMT

@marcus_sommer Thanks for the help. I did not know you have replied me here and I have sent another message to you on the former topic I opened "How to Use VB macros to send Email through Outlook in QlikView".

Re: How to Open Outlook App using VBScript Macros in QlikView

olaoyesunday1 — Tue, 23 Apr 2024 15:32:36 GMT

@marcus_sommer thanks I got the solution from this link [RESOLVED] Stop / Starting Outlook via VBScript-VBForums . The code here that he was having issue with

Sub StartOutlook()
    Dim oShell
    set oshell = CreateObject("Wscript.Shell") 
    oShell.run "cmd.exe /K ""C:\Program Files (x86)\Microsoft Office\root\Office16\OUTLOOK.EXE"" "
End Sub

I modified the code a little bit and it does not open the command prompt at all but open Outlook strait away. The below is the full code for anybody that is looking for the similar solution:

Sub StartOutlook()

Dim oShell

Set oShell = CreateObject("Wscript.Shell")

oShell.Run "cmd.exe /K ""C:\Program Files\Microsoft Office\root\Office16\OUTLOOK.EXE"" ", 0, False

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 (5,000 milliseconds)

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

' Check if Outlook is already running

On Error Resume Next

Set objOutlk = GetObject(, "Outlook.Application")

On Error GoTo 0

' If Outlook is not running, start it

If (objOutlk = "") Then

StartOutlook

' Wait for 5 seconds for Outlook to start

ActiveDocument.GetApplication.Sleep 5000

' Create a new instance of Outlook application

Set objOutlk = CreateObject("Outlook.Application")

End If

' 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