Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
avantime
Creator II
Creator II

Send email to multiple people with relevant data

Hi,

I have the following table:

NAMEEMAILRELEVANT DATA 1RELEVANT DATA 2RELEVANT DATA 3

Name 1

name1@company.com123456789
Name 2name2@company.comabcdefghi

I need to send relevant data to each user like:

To: name1@company.com

RELEVANT DATA 1: 123

RELEVANT DATA 2: 456

RELEVANT DATA 3: 789

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

To: name2@company.com

RELEVANT DATA 1: abc

RELEVANT DATA 2: def

RELEVANT DATA 3: ghi

You get the point, maybe you have an idea.

Thank you!

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

actually you just need to add one more variable

vData=

'RELEVANT DATA 1:  '&[RELEVANT DATA 1]&Chr(10)&

'RELEVANT DATA 2:  '&[RELEVANT DATA 2]&Chr(10)&

'RELEVANT DATA 3:  '&[RELEVANT DATA 3]&Chr(10)&

and use this variable in your  macro as MailBody

See attached qvw.

I tested it and for me it works with gmail.

View solution in original post

10 Replies
Clever_Anjos
Employee
Employee

avantime
Creator II
Creator II
Author

Hi, thank you for your reply but that is not what I want.

The app in that thread sends the same info to all recipients, I want the data to be filtered for each recipient and sent in the body of the email, not Excel.

I also don`t want to use Outlook as means of sending the email, but that is no issue as I already have a script that sends email via a SMTP connection.

Frank_Hartmann
Master II
Master II

see attached file.

i tested it with gmail and it works

If you are using gmail for sending the emails make sure that the following option is enabled:

Account settings

hope this helps

avantime
Creator II
Creator II
Author

Hi Frank!

Thank you very much for the effort you put into this, I really do appreciate it.

Do you think it is possible to add the data in the body of the email instead of an Excel file?

I modified the macro to suit my configuration like this:

Public Sub ExportandMail

Dim dteWait

Const cdoSendUsingPort = 2

Const cdoBasicAuth = 1

Const cdoTimeout = 60

SET Doc = ActiveDocument

SET Field = Doc.Fields("EMAIL").GetPossibleValues

FieldCount = Field.Count

FOR p=0 to Field.Count -1

Doc.Fields("EMAIL").SELECT Field.Item(p).Text

Set v = ActiveDocument.Variables("vEmailadress")

Timestamp="-"&Year(Now())&"-"&Month(Now())&"-"&Day(Now())&"--"&Hour(Now())&"-"&Minute(Now())&"-"&Second(Now())

Selection=v.GetContent.String

strDate = CDate(Date)

strDay = DatePart("d", strDate)

strMonth = DatePart("m", strDate)

strYear = DatePart("yyyy", strDate)

If strDay < 10 Then

strDay = "0" & strDay

End If

If strMonth < 10 Then

strMonth = "0" & strMonth

End If

GetFormattedDate = strMonth & "-" & strDay & "-" & strYear 

Path = "E:\Test\"

FileName = "Test_" & GetFormattedDate  & Timestamp &".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Columns("A:K").EntireColumn.AutoFit

XLDoc.Sheets(1).Name = "Export"

XLDoc.SaveAs Path & FileName

XLApp.Quit

mailto = Selection

mailSubject = "Report"&Timestamp

mailBody = "Attached the current Report"

Set objMsg = CreateObject("CDO.Message")

        Set msgConf = CreateObject("CDO.Configuration")

        Set objFlds = msgConf.Fields

        With objFlds

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

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

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

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

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

        msgConf.Fields.Update

        End With

objMsg.To = mailto

objMsg.From = x

objMsg.Subject = mailSubject

objMsg.HTMLBody = mailBody

dteWait = DateAdd("s", 3, Now())

Do Until (Now() > dteWait)

Loop

objMsg.AddAttachment "E:\Test\" & FileName

Set objMsg.Configuration = msgConf

        objMsg.Send

Set objMsg = nothing

        Set msgConf = nothing

next

End sub

I get the following error: At least one of the From or Sender fields is required, and neither was found.

Somehow the email does not pass on.

Frank_Hartmann
Master II
Master II

actually you just need to add one more variable

vData=

'RELEVANT DATA 1:  '&[RELEVANT DATA 1]&Chr(10)&

'RELEVANT DATA 2:  '&[RELEVANT DATA 2]&Chr(10)&

'RELEVANT DATA 3:  '&[RELEVANT DATA 3]&Chr(10)&

and use this variable in your  macro as MailBody

See attached qvw.

I tested it and for me it works with gmail.

MK9885
Master II
Master II

Why not use Alert Options in Qlik view?

You'll find that in Tool Tab at the top.. where you can add email ids, subject, condition etc.

I guess better than writing a macro?

avantime
Creator II
Creator II
Author

Thank you Frank, I will test today and let you know how it went.

avantime
Creator II
Creator II
Author

Hi,

I looked into Alerts but it`s a real head scratcher here. Can I filter data per user and send it via email after AccessPoint reload?

Thank you!

Frank_Hartmann
Master II
Master II

and did you set up the vEmailadress Variable :

vEmailadress = GetFieldSelections(EMAIL)

That might be the reason why the sending was not successful.