Re: Sending Emails using Macro - Qlik Community

Report Inappropriate Content · ‎2013-03-13

Hi guys

I want to send the mail from my company's EmailID to the someone's else company 's Email id using macro .

Right now i can send the mail to all the Employee's of my company using macro, but how to send the mail to someone who is not in my company.

vikasgupta · ‎2013-03-13

Hi

Do you want to send individual mail to any mail id ? please explain more !!

Thanks

Report Inappropriate Content · ‎2013-03-13

Yes ....

vikasgupta · ‎2013-03-13

Hi

Try this script !!!

--------------------------------------------------------------------------

SendGMail

ActiveDocument.Save

set objExcel=nothing

set strDate=nothing

set filepath=nothing

End Sub

Sub SendGMail()

MsgBox "Report Exported Successfully"

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

filepath="D:\Excel_"&strDate&".xls"

Set objMsg = CreateObject("CDO.Message")

Set msgConf = CreateObject("CDO.Configuration")

'Server Configuration

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

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

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "500vikas@gmail.com"

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

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

msgConf.Fields.Update

'End With

objMsg.To = "Sender Mail ID"

objMsg.From = "Xyzz@gmail.com"

objMsg.Subject = "Test mail"

objMsg.HTMLBody = "QlikView Test Mail"

'objMsg.AddAttachment "D:\Image.jpg"

objMsg.AddAttachment filepath ' Attachement

objMsg.Sender = "Mr. Name"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

Msgbox("Email Sent Successfully")

' Clear

Set objMsg = nothing

Set msgConf = nothing

End Sub

-----------------------------------------------------------------------------

Just edit your maild and password and reciver mail id .

Thanks

Vikas

Report Inappropriate Content · ‎2013-03-13

hi vikas

it is not working ......

Report Inappropriate Content · ‎2013-03-13

error is coming on

objMsg.Send

line

vikasgupta · ‎2013-03-13

Hi

Use this working sample first edit the macro i commented where changes required . This applictaion can convert any object in excel and send it by mail .If any error will come please let me know .

Thanks

Vikas

Report Inappropriate Content · ‎2013-03-13

hi vikas

the code is working when i am sending to the person within my company.

but when i am sending outside my company (say xyz@othercomapnyname.com) then the module is getting opened and no error message is coming

But when i debuged the macro i came to know that the error is at objMsg.Send

Gethyn · ‎2013-03-13

Hi Manish,

I use these two macros assigned to a button. It pulls the send to e-mail address for the relevent record selected befor pressing the button.

Sub ExportTable()

Dim strvDBA

Dim strvDate

Set vDBA = ActiveDocument.Fields("Debtor Billing Agency").GetPossibleValues

strvDBA = vDBA.item(i).Text

Set vDate = ActiveDocument.Fields("Transaction Date").GetPossibleValues

strvDate = vDate.item(i).Text

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.DisplayAlerts = False

set chart = ActiveDocument.GetSheetObject ("CH104")

chart.SendToExcel

Set WB = objExcel.ActiveWorkbook

WB.SaveAs "\\belvan\Belvan_Systems\Monthly_Processing\"&strvDBA&" - NCA Backing Data for "&strvDate&".xls"

WB.Close

objExcel.Quit

chart.Minimize

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

End Sub

SUB openMail

Dim objEmail

Dim strMailTo

Dim appname

Dim strvDBA

Dim strvDate

Set vDBA = ActiveDocument.Fields("Debtor Billing Agency").GetPossibleValues

strvDBA = vDBA.item(i).Text

Set vDate = ActiveDocument.Fields("Transaction Date").GetPossibleValues

strvDate = vDate.item(i).Text

Set objSelected = ActiveDocument.Fields("Email contact").GetPossibleValues

Set vOlApp = CreateObject("Outlook.Application")

Set vMessage = vOlApp.CreateItem(olMailItem)

Set myAttachments = vMessage.Attachments

myAttachments.Add "\\belvan\Belvan_Systems\Monthly_Processing\"&strvDBA&" - NCA Backing Data for "&strvDate&".xls"

For i = 0 to objSelected.Count-1

strMailTo = objSelected.item(i).Text

vMessage.Subject = strvDBA&" - Billing Backing Data for "&strvDate

vMessage.To = strMailTo

vMessage.BCC = "xyz@nhs.net"

vMessage.Display

Hope this helps,

Gethyn.

vikasgupta · ‎2013-03-13

Hi

Did you configure smtp server port ? sample is working on gmail smtp server configuration so you need to configure your compnay smtp server configuration .

Thanks

vikas