issue in field value required in email through mac... - Qlik Community

Report Inappropriate Content · ‎2015-10-01

I have created a macro which does the following step :

1. Select the value in field name

2. select the value in field week.

after these selection it export the dashboard into PPT (screenshot ) and then email it to the required people dependent on selection .

Issue 1... is when I want to add the subject in email . its not taking the Week. Find below the Code .I have highlighted the variable I am passing the Value .

Issue 2... I have Pivot table as I am taking screenshot the pivot get truncated . Is there any way to include the whole data of pivot or can we create an excel file and attach it with email

sub exportppt

Dim Week_No

FilePathPPT = "C:\aaaa_QV\Reports"
FilePathExcel = "C:\aaaa_QV\Reports"

set AMM_Name = ActiveDocument.Fields("AMM_Name").GetPossibleValues
set ToEmailId = ActiveDocument.Fields("AMM_Email").GetPossibleValues
set CcEmailId = ActiveDocument.Fields("CC_EmailID").GetPossibleValues
Set WeekName = ActiveDocument.Fields("WeekName").GetPossibleValues
FieldValueAMM = AMM_Name.Item(0).text
FieldValueToEmailId = ToEmailId.Item(0).text
FieldValueCcEmailId = CcEmailId.Item(0).text
FieldValueWeekName= WeekName.item(0).text

Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add
PPSlideNo = 1
For i = 0 to activedocument.noofsheets -1
Set PPSlide = objPresentation.Slides.Add(PPSlideNo,12)
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.CopyBitmapToClipboard
PPSlide.Shapes.Paste

objPPT.ActiveWindow.Selection.ShapeRange.Top = 54 'This line sets the top location of the image
objPPT.ActiveWindow.Selection.ShapeRange.Left = 20 'This line sets the left location

'These lines set the image Width and Height (if the image is set to Lock Aspect ratio, you need to set only one of these

objPPT.ActiveWindow.Selection.ShapeRange.Width = 676
objPPT.ActiveWindow.Selection.ShapeRange.Height = 406

Set PPSlide = Nothing

Set PPPres = Nothing

Set PPApp = Nothing

activedocument.nexttab

PPSlideNo = PPSlideNo + 1

NEXT

'FilePathPPT = FilePathPPT &"\" & FieldValueAMM &"_" & FieldValueWeekName ".pptx"

FilePathPPT = FilePathPPT &"\" & FieldValueAMM & "_" & FieldValueWeekName & ".pptx"
objPresentation.SaveAs(FilePathPPT)
objPresentation.Close
'Msgbox "File(s) saved at: " & CHR(13) & CHR(13) & FilePathPPT

CALL SendMail(FieldValueToEmailId,FieldValueCcEmailId,FilePathPPT,FilePathExcel,FieldValueAMM,WeekName)
Msgbox "Mail has been sent Successfully."
End Sub

set AMM_Name = ActiveDocument.Fields("AMM_Name").GetPossibleValues
set ToEmailId = ActiveDocument.Fields("AMM_Email").GetPossibleValues
set CcEmailId = ActiveDocument.Fields("CC_EmailID").GetPossibleValues
Set WeekName = ActiveDocument.Fields("WeekName").GetPossibleValues

FUNCTION SendMail(To_Id,CC_Id,FilePathPPT,FilePathExcel,name,WeekName)
dim output_
output_ = "<html><head><style>.body{font-family:Arial;font-size:11px;}</style></head>"
output_ = output_ & "<div class=""body"">Hi <b>"& name &",</b> <br/><br/><br/><br/>Please find attached the weekly report for your team for Week_"& FieldValueWeekName &"."
output_ = output_ & "<br/><br/><br/><br/>Thanks,<br/><b>aaaaaaabbbbbbbbb</b><br/>aaaaaaaaaaaaaaaaa<br/>2345tgrddff,<br/>"
output_ = output_ & "xxxxxxxxxxxxxxxxxxxxxxx <br/>email : ccccccc@infinityeservices.com<br/>Landline - (666666666<br/>"
output_ = output_ & "</div></html>"
Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0)
With MyItem
.to = To_Id
.cc=CC_Id
.Attachments.add(FilePathPPT)
.Subject = "MetaMorph:Weekly Report for Week_" & FieldValueWeekName & "."
.ReadReceiptRequested = False
.HTMLBody = output_
End With
MyItem.Send
SET MyApp=Nothing
END FUNCTION

issue in field value required in email through macro