topic Re: Implementing Macro on Local in QlikView

Implementing Macro on Local

Anonymous — Mon, 20 Oct 2014 07:42:14 GMT

Hello Expert,

I have created a macro to send a email on local system.

Is it required any additional settings?

To send a mail from my local system, what setting I would required like SMTP etc?

Pls suggest.

Regards,

Balraj

Re: Implementing Macro on Local

Peter_Cammaert — Mon, 20 Oct 2014 07:51:59 GMT

To send an email, you need an email server (MTA) that will figure out where your addressees are located and how to reach their mailboxes, and subsequently deliver your email to the program that will distributed this email to the recipients. Such a program is probably not running on your own PC but runs on a company server or at your ISP. These servers dictate what parameters you'll need to enter in your macro, like server domainname / ip-address, account name and password. The portnumber will probably be the default one: 25 (= SMTP)

Best,

Peter.

Re: Implementing Macro on Local

Anonymous — Mon, 20 Oct 2014 07:55:56 GMT

okay,

If I am putting SMTPServer and Port Details in my macro code, is it sufficient?

Re: Implementing Macro on Local

Peter_Cammaert — Mon, 20 Oct 2014 08:03:51 GMT

That depends on whether your Email server needs you to login to send email. In that case, you'll need to supply an account name and a password.

Peter

Re: Implementing Macro on Local

Peter_Cammaert — Mon, 20 Oct 2014 08:07:51 GMT

Supplying a valid From-address will also help in getting your email delivered. In some situations it doesn't need to be an existing emailaddress, but that's for you to figure out. Try first with a valid one (your own for eample)

Re: Implementing Macro on Local

Anonymous — Mon, 20 Oct 2014 08:34:06 GMT

Peter,

this is my macro code:

Sub ExcelExpwCaption

'Set the path where the excel will be saved

filePath = "C:\MacroTest\Test.xls"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

Set curSheet = curWorkBook.WorkSheets(1)

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("CH01")

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

'Get the caption

chartCaption = tableToExport.GetCaption.Name.v

'MsgBox chartCaption

'Set the first cell with the caption

curSheet.Range("A1") = chartCaption

'Paste the rest of the chart

curSheet.Paste curSheet.Range("A2")

excelFile.Visible = true

'Save the file and quit excel

curWorkBook.SaveAs filePath

curWorkBook.Close

excelFile.Quit

'Cleanup

Set curWorkBook = nothing

Set excelFile = nothing

msgbox("Done")

call SendReport

End Sub

sub SendReport

Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP

Const cdoAnonymous = 0 'Do not authenticate

Const cdoBasic = 1 'basic (clear-text) authentication

Const cdoNTLM = 2 'NTLM

SMTPServer = "XYZ"

Const SMTPPort = 25 ' Port number for SMTP

Const SMTPTimeout = 60 ' Timeout for SMTP in seconds

'Sending mail

Set objEmail = CreateObject("CDO.Message")

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

'---------------------------------------------------------------------

' SMTP server details

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout

.Update

'---------------------------------------------------------------------

End With

objEmail.To = "abhay.singh@9abusiness.com" 'Email Recipient

objEmail.From = "balraj.ahlawat@9abusiness.com" 'Email Sender

objEmail.Subject = "Test Mail For Macro "

ActiveDocument.GetApplication.Sleep 2000

objEmail.AddAttachment "C:\MacroTest\Test.xls"

'ActiveDocument.GetApplication.Sleep 2000

objEmail.Send

ActiveDocument.GetApplication.Sleep 200

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

msgbox("Mail Sent")

end sub

Re: Implementing Macro on Local

Anonymous — Mon, 20 Oct 2014 08:35:04 GMT

I am using valid email id but not getting any success, pls suggest.

I have pasted my macro code as well....