Send email to multiple people with relevant data

[avantime]

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!

[Clever_Anjos]

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

[avantime]

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]

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]

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 · Accepted solution]

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]

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]

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

[avantime]

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]

and did you set up the vEmailadress Variable :

vEmailadress = GetFieldSelections(EMAIL)

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