topic Re: Send email to multiple people with relevant data in QlikView

Send email to multiple people with relevant data

avantime — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following table:

NAME	EMAIL	RELEVANT DATA 1	RELEVANT DATA 2	RELEVANT DATA 3
Name 1	name1@company.com	123	456	789
Name 2	name2@company.com	abc	def	ghi

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!

Re: Send email to multiple people with relevant data

Clever_Anjos — Thu, 05 Oct 2017 12:29:04 GMT

Same case from here Macro: Send an excel to an email list acording to a selection

Re: Send email to multiple people with relevant data

avantime — Thu, 05 Oct 2017 12:40:29 GMT

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.

Re: Send email to multiple people with relevant data

Frank_Hartmann — Thu, 05 Oct 2017 14:52:37 GMT

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

Re: Send email to multiple people with relevant data

avantime — Fri, 06 Oct 2017 12:19:37 GMT

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.

Re: Send email to multiple people with relevant data

Frank_Hartmann — Fri, 06 Oct 2017 12:44:46 GMT

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.

Re: Send email to multiple people with relevant data

MK9885 — Fri, 06 Oct 2017 12:54:48 GMT

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?

Re: Send email to multiple people with relevant data

avantime — Fri, 06 Oct 2017 13:10:35 GMT

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

Re: Send email to multiple people with relevant data

avantime — Fri, 06 Oct 2017 13:31:47 GMT

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!

Re: Send email to multiple people with relevant data

Frank_Hartmann — Fri, 06 Oct 2017 14:12:52 GMT

and did you set up the vEmailadress Variable :

vEmailadress = GetFieldSelections(EMAIL)

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

Re: Send email to multiple people with relevant data

avantime — Fri, 06 Oct 2017 21:35:04 GMT

Your help was amazing, thank you very much!

I kept deleting "mailusername = "" causing an error in the script even if I inserted the email address in "objEmail.From =".

Everything works OK now.

If I can send a six-pack of beer your way please let me know .