Create excel files & emails

zain16nib — Wed, 06 Jan 2016 06:12:33 GMT

Dear,

Given below attachment excel file has 5 BM's client data with BM email addresses.

First requirement: create excel files as per BM in define location for eg: c:\BM\06-01-16\ B-1.xlsx B-2.xlsx ...........so on.

Second requirement: email particular BM excel file to given email address.

kindly guide me how to achieve above requirement through macro or else, without nprinting.

Regards,

Zain.

Re: Create excel files & emails

Wed, 06 Jan 2016 11:25:12 GMT

Hi,

For loading data from excel file:

Exemple...

[B1]:

LOAD

Client_Code,

BM_Code,

BM_EMAIL,

Amt

FROM (ooxml, embedded labels, table is Sheet1)

Where BM_Code = 'B-1';

===================================================================

For exporting to xlsx:

See this post: Re: QV to excel 2010?

===================================================================

For sending emails use this macro (gmail):

Sub SendGMail()

'strDate = Year(Date()) &"-"& month(date()) &"-"& Day(Date())

folderAttach1 = trim("C:\yourFolder\B1.xlsx")

folderAttach2 = trim("C:\yourFolder\B2.xlsx")

Set objMsg = CreateObject("CDO.Message")

Set msgConf = CreateObject("CDO.Configuration")

'Server Configuration

'msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

'msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

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") = 465

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "user" 'type your mail id

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" 'Type your acccount Password

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

msgConf.Fields.Update

objMsg.To = "fernando.martinig@gmail.com;fernando.martini@divinaprovidencia.org.br;raul.weber@divinaprovidencia.org.br" ' type to mail id

objMsg.From = "youremail@gmail.com" 'type from mail id

objMsg.Subject = "Teste Envio Automatico BI"

objMsg.HTMLBody = "Texto do email"

objMsg.AddAttachment folderAttach1

objMsg.AddAttachment folderAttach2 ' Attachement object

objMsg.Sender = "Qlikview"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

'Msgbox("Email send ok")

' Clear

Set objMsg = nothing

Set msgConf = nothing

' ActiveDocument.Save

' Application.Quit

End Sub

Re: Create excel files & emails

zain16nib — Wed, 06 Jan 2016 11:44:09 GMT

Dear Fernando,

I want macro to create automatically excel files group by BM-code. so my question is where i define loop and where clause.

and some for the email. how we pick file and send to BM as defined email.

flow is

loop fetch

pick automatically BM-1 data from object, create excel file and sent to BM-1,

pick automatically BM-2 data form object, create excel file and sent to BM-2

pick automatically BM-3 data form object, create excel file and sent to BM-3 and so on.

end loop

i want macro or other solution for it.

Regards,

Zain.

topic Create excel files & emails in QlikView

Create excel files & emails

Re: Create excel files & emails

Re: Create excel files & emails