Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Macro: Send an excel to an email list acording to a selection

Hello QlikView Gurus, I'm here againh with anoter question, this one regarding a macro we have.

We have a document we want to automatize, so every morning sends an excel with the information about yesterday sales to a email list. The thing is, we want to send each person an excel with the info from their sucursal only, nothing else. We can filter by sucursal, but the thing is, I haven't found a way to use something like a "for" or "foreach" cycle to automatize the sucursal selection. This is my Macro script:

sub ExcelVentas

Set v = ActiveDocument.Variables ("vEmail")

  activedocument.fields("SUCURSAL_DESC").select "1102 - CONSTITUCION"

  Email=v.GetContent.String

Path = "C:\temp\"

FileName = "Test_Constitucion.xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH04").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Columns("A:W").EntireColumn.AutoFit

XLDoc.Sheets(1).Name = "Export"

XLDoc.SaveAs Path & FileName

XLApp.Quit

Set myApp = CreateObject ("Outlook.Application")

Set myMessage = myApp.CreateItem(olMailItem)

myMessage.BodyFormat = 3 'Outlook.OlBodyFormat.olFormatRichText

myMessage.To = Email

myMessage.Attachments.Add "C:\temp\" & FileName

myMessage.Subject = "Test File " & Date()

myMessage.Send

Set myMessage = Nothing

Set myApp = Nothing

Set myInspector = Nothing

Set myDoc = Nothing

end sub

As you can see, on this part: "activedocument.fields("SUCURSAL_DESC").select "1102 - CONSTITUCION" we pass the macro automatically the sucursal we want to use, and on this one: "Set v = ActiveDocument.Variables ("vEmail")" we use a variable, vEmail, so the user can write the list of people who are going to recibe the excel. The main goal is, having a list of people for each sucursal, use ana utomated script so, for example, mail1@xyz.com reciebes the info from the sucursal 1 already filtered, mail2@xyz.com reciebes info from sucursal 3, and so on.

Any help you can give me? Im not very good programming on VB or Java, so any help is apreciated.

Thanks in advance!!!

1 Reply
vikasmahajan
Esteemed Contributor

Re: Macro: Send an excel to an email list acording to a selection

PFA SAmple Hope this will help you.

Vikas