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

Tue, 22 Oct 2013 21:53:22 GMT

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!!!

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

vikasmahajan — Wed, 23 Oct 2013 09:29:30 GMT

PFA SAmple Hope this will help you.

Vikas

topic Macro: Send an excel to an email list acording to a selection in QlikView

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

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