How to Open Outlook App using VBScript Macros in QlikView

[olaoyesunday1]

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.

[marcus_sommer]

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.

[olaoyesunday1]

@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". 

[olaoyesunday1 · Accepted solution]

@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