Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
For loading data from excel file:
Exemple...
[B1]:
LOAD
Client_Code,
BM_Code,
BM_EMAIL,
Amt
FROM
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
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.