Consider layout selections in Macro - Qlik Community

Anonymous · ‎2016-09-15

Dear All,

We have following macro to export a chart and send email. I want to pre select few list boxes for some specific values before the chart get's exported. How can we do that in Macro script??

One option I can see is, we can make the selection in the same trigger of document properties on post reload. Since these selections are document level, it will apply to all charts. Since we have many users have their own selections I cannot duplicate QVWs for each user selection.

Please suggest.

Macro details:

Sub ExportEmail
Set obj = ActiveDocument.ActiveSheet.SheetObjects("CH01")
obj.ExportBitmapToFile "C:\TestImageJPG.jpg"

' MsgBox "Exported"
' Object creation
Dim objEmail

Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
Const cdoBasic = 1
Const cdoNTLM = 2
Const SMTPServer = "SMTPServer"
Const SMTPPort = 25
Const SMTPTimeout = 60

' 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 = "receiver email id"
objEmail.From = "sender email id"
objEmail.Subject = "Embedded Mail Subject"

HTML = "<!DOCTYPE HTML PUBLIC ""-//IETF//DTD HTML//EN"">" & chr(13) & chr(10)
HTML = HTML & "<html>"
HTML = HTML & "<head>"
HTML = HTML & "<meta http-equiv=""Content-Type"" content=""text/html; charset=iso-8859-1"">"
HTML = HTML & "<title>Automated Emails!</title>"
HTML = HTML & "</head>"
HTML = HTML & "<body bgcolor=""#FFFFFF"">"

'html tag to include image
HTML = HTML & ""
HTML = HTML & " <img src=""cid:TestImageJPG.jpg"" > "
HTML = HTML & " <img src=""cid:TestImageJPG1.jpg"" > "
HTML = HTML & ""
HTML = HTML & "</body>"
HTML = HTML & "</html>"

'code to embed the image by uploading into the mail body
Set objBP = objEmail.AddRelatedBodyPart("C:\TestImageJPG.jpg", "TestImageJPG.jpg", CdoReferenceTypeName)
objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<TestImageJPG.jpg>"
objBP.Fields.Update

objEmail.HTMLBody = HTML

objEmail.Send

Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing

' MsgBox ("Test Mail Sent")

End Sub

marcus_sommer · ‎2016-09-15

Here a few examples from the APIGuide.qvw:

ActiveDocument.Fields("Month").Select "September"

ActiveDocument.Fields("Month").Select "J*"

ActiveDocument.Fields("Sales").Select ">1500"

- Marcus