How to change display address in Macro - Page 2 - Qlik Community

Report Inappropriate Content · ‎2013-10-23

Hi,

I have a macro in qvw and is it possible to change the display address. i,e when I am clicking OK the file is coming to my email but it's showing my name as display address instead of my name will it be possible to get different name.

sub ExcelFile

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
Set v = ActiveDocument.Variables ("EmailAddress")
Email=v.GetContent.String

Path = "D:\Amelia Disousa\"
FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Columns("A:D").EntireColumn.AutoFit
XLDoc.Sheets(1).Columns("A:A").ColumnWidth = 15.57
XLDoc.Sheets(1).Columns("B:B").ColumnWidth = 12.43
XLDoc.Sheets(1).Columns("C:C").ColumnWidth = 15.29
XLDoc.Sheets(1).Columns("D:D").ColumnWidth = 15.57

XLDoc.Sheets(1).Name = "Export"
XLDoc.SaveAs Path & FileName
XLApp.Quit

Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.BodyFormat = 3 'Outlook.OlBodyFormat.olFormatRichText

myMessage.To = "amelia.disousa@xx.vv.com"
myMessage.Attachments.Add "D:\Amelia Disousa\" & FileName
myMessage.Subject = "Test File " & Date()

myMessage.Send

Set myMessage = Nothing
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing

end sub

Thanks.

Report Inappropriate Content · ‎2013-10-25

Thanks and I have used below

function MailReport()
    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 = "12.453.453.131"
    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 'ok
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer      'ok
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic 'ok

           .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxyyy"
           .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
             .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 = "amelia.disousa@xx.yy.com"        'Email Recipient
    objEmail.From = "QlikView Reporting"                        'Email Sender
    objEmail.Subject = "Dashboard"                        ' Subject
    objEmail.TextBody = "Body"        'Text Body

    objEmail.Send
    Set objFlds = Nothing
    Set objConf = Nothing
    Set objEmail = Nothing
end function

when I am testing this I am getting

At least one of the From or Sender fields is required, and neither was found.

could you help me waht is this and how to get this.

marcus_sommer · ‎2013-10-25

I have it just checked and you need a @ - char within the from-part - try this:

objEmail.From = "QlikView@Reporting"

- Marcus

Report Inappropriate Content · ‎2013-10-25

Thanks and you are right it's taking Qlikview@reporting and now it's showing another message

The transport failed to connect to the server.

What it means please let me know.

marcus_sommer · ‎2013-10-25

Make sure that all settings are correct:

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 = "12.453.453.131"

Const SMTPPort = 25 ' Port number for SMTP

Const SMTPTimeout = 60 ' Timeout for SMTP in seconds

You could look in your outlook-settings for some settings and/or ask your admin (there are several various security issues possible - firewall, group policies and so on - which could prevent the connection).

- Marcus

christian77 · ‎2013-10-25

I really hate the e-mailing macro. i've done it a few times and it is always a problem.

Port 25 has to be open. SMTP in numbers, good.

In your QV push Ctrl + Shift + M. Choose allow macro to access system.

Inside your macro module, there is a combo box in the bottom left pannel. Do the same setting.

Are you sending an attachment?

luck.

Report Inappropriate Content · ‎2013-10-25

Hi,

This time I am getting message like below.

The server rejected the sender address. The server response was: 452 4.3.1 Insufficient system resources

and I gave all correct. Please let me know as I need to get this done. please

marcus_sommer · ‎2013-10-26

The message meant there are not enough ressources available - see here: Exchange 2010: Said: 452 4.3.1 Insufficient system resources (in reply to MAIL FROM command) or Google. If it is not temporary (try it again) give your admin an evidence.

- Marcus

Report Inappropriate Content · ‎2013-10-29

Hi,

Thanks and it's working as I can able to see FROM address as Qlikview@Reporting and below is my Macro.

Could you help me how to attache excel file to get from this macro with FROM address as Qlikview@Reporting.

function MailReport()

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 = "12.453.453.131"

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 'ok

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

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic 'ok

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxyyy"

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

.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 = "amelia.disousa@xx.com" 'Email Recipient

objEmail.From = "QlikView@Reporting" 'Email Sender

objEmail.Subject = "Dashboard" ' Subject

objEmail.TextBody = "Body" 'Text Body

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

end function

salto · ‎2013-10-29

You need to add:

objEmail.AddAttachment "Path to your attachment" (somethin like C:\...\attachment.xls"

marcus_sommer · ‎2013-10-29

You need before sending the following statement:

objEmail.AddAttachment "CompletePath\AnyExcel.xls"

Path + File could be also variables like Path & File & vCurrentDate & "xls"

- Marcus