Hi All!
I'm using a macro who sends excel/png files to users.
I want to send different reports to different users and also in some cases to send the same report with different selections to different users.
Can someone please advise?
thanks!
The macro I'm using ) :
'==============================================================
' File created by Jakub Szurogajlo - QlikPad.com
' as a learning material for article Qlikview Scheduled Reports
' Part 1: http://qlikpad.com/qlikview-scheduled-reports-sending-excel-via-email/
' Part 2: http://qlikpad.com/qlikview-scheduled-reports-sending-excel-via-email-part-2/
'==============================================================
'==============================================================
'================= Generate Excel Attachment =================
'==============================================================
Sub GenerateMailReport
'First we are reading from variable the path to a folder where Excel file will be saved:
set v = ActiveDocument.Variables("vPath")
vPath = v.GetContent.String
'Initialize Excel object:
set XLApp = CreateObject("Excel.Application")
'below command hides Excel window. In other words you will not see Excel application showing up, everything will be done in the background
'If you want to see what Excel is doing (during testing phase for example), change it from false to true.
XLApp.Visible = true
'Add a Workbook to Excel file:
set XLDoc = XLApp.Workbooks.Add
'Clear all filters (you may not need this line, remove it if you have pre-defined filters selection in report
'In future posts I will show you how to select filters via macro.
ActiveDocument.ClearAll True
'Here we are telling Excel into which Worksheet we will paste QV data:
set XLSheet = XLDoc.Worksheets(1)
XLSheet.Activate
'Tell QV which object to copy from (in our case it is a Chart object, which ID = "FOR_SCHEDULE":
ActiveDocument.Fields("location").Select "UNMAPPED"
set table=ActiveDocument.GetSheetObject("New Report")
'Copy content of this object:
table.CopyTableToClipboard true
'And paste into Excel
XLSheet.Paste
'Let's also change Sheet's name to something more fancy:
XLSheet.Name = "New Report"
''Now, let's select F1 cell (this is where second object will be pasted):
'XLSheet.Range("F1").Select
'lets repeat previous steps to copy and paste contents of table to Excel
'set table=ActiveDocument.GetSheetObject("FOR_SCHEDULE")
'table.CopyTableToClipboard true
'XLSheet.Paste
'Let's add one more Sheet to Excel
'XLDoc.Worksheets.Add()
'As it will be added before current Sheet, lets move if to second position:
'XLDOC.Sheets(2).Move XLDOC.Sheets(1)
'Select second tab and acticate it:
'set XLSheet = XLDoc.Worksheets(2)
'XLSheet.Activate
'set table=ActiveDocument.GetSheetObject("FOR_SCHEDULE")
'table.CopyTableToClipboard true
'XLSheet.Paste
'XLSheet.Name = "Expr Max"
'Now, let's select first sheet and A1 cell (so that report's recipient will not have switch himself):
'set XLSheet = XLDoc.Worksheets(1)
'XLSheet.Activate
'Define a Range for which formatting will happen
set TitleRange = XLSheet.Range("A1:H1")
'Text formatting example:
'TitleRange.Font.Underline = True
'TitleRange.Font.Color = RGB(255,0,0)
'TitleRange.Font.Size = 12
'Changing Column Width and Row height:
TitleRange.ColumnWidth = 15
TitleRange.RowHeight = 30
'Excel VBA uses constants (for example xlCenter in below example). Those are not defined in QlikView VBScript engine, and we must define them, so that Excel understands it:
Const xlCenter = -4108
'Center vertically and Horizontally:
TitleRange.HorizontalAlignment = xlCenter
TitleRange.VerticalAlignment = xlCenter
'Other positioning variables used in Excel:
'Const xlCenter = -4108
'Const xlNone = -4142
'Const xlDown = -4121
'Const xlRight = -4152
'Const xTop = -4160
XLSheet.Range("A1").Select
'Now me must save report to previously set destination folder,
XLDoc.SaveAs vPath
'Close Workbook
XLDoc.close
'And finally close whole Excel file
XLApp.Quit
End Sub
'==============================================================
'============== Helper functions defined below ===============
'==============================================================
'Read variable
Public function getVariable(var)
set v = ActiveDocument.Variables(var)
getVariable = v.GetContent.String
end function
'Delete file
Public Function DeleteFile(vfile)
Set obj = CreateObject("Scripting.FileSystemObject") 'Calls the File System Object
obj.DeleteFile(vfile) 'Deletes the file.
End Function
'==================================================================================
Sub ExportEmail
Set obj = ActiveDocument.ActiveSheet
obj.ExportBitmapToFile "C:\Users\*\Desktop\Scheduler\test2.jpg"
MsgBox "Exported"
' Object creation
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")
' Server Configuration
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "***@gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "***"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
msgConf.Fields.Update
' Email
objMsg.Subject = "Hi ALl"
objMsg.To = "***"
objMsg.From = "mcereporting@gmail.com"
objMsg.HTMLBody = "<html><body>Report Attached<br><img src=""E:\\TestImageJPG.jpg""></body></html>"
objMsg.AddAttachment "C:\Users\***\Desktop\Scheduler\test2.jpg"
objMsg.AddAttachment "C:\Users\***\Desktop\Scheduler\Test.xlsx"
Set objMsg.Configuration = msgConf
' Send
objMsg.Send
Msgbox("Email Sent")
' Clear
Set objMsg = nothing
Set msgConf = nothing
End Sub
