How to change display address in Macro - Page 3 - 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-29

Thanks very much and I am getting attachemnet with name

Test.xlsx.

If I want to get Test_10-29-2013.xlsx means how can I change it. Please help me.

marcus_sommer · ‎2013-10-29

Try this:

"Test_" & month(now()) & "-" & day(now()) & "-" & year(now()) & ".xlsx"

- Marcus

Report Inappropriate Content · ‎2013-10-29

Thanks and when I am using this it is showing

The process cannot access the file because it is being used by another process.

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.313"
    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.AddAttachment "D:\Amelia Disousa\" & "Test_" & month(now()) & "-" & day(now()) & "-" & year(now()) & ".xlsx"
    objEmail.Send
    Set objFlds = Nothing
    Set objConf = Nothing
    Set objEmail = Nothing
    end function

marcus_sommer · ‎2013-10-29

The xlsx must be already exists (perhaps you need some delaying to make sure this file is already created) and must not be open.

rem ** let QV sleep for 10 seconds **

ActiveDocument.GetApplication.Sleep 10000

- Marcus

Report Inappropriate Content · ‎2013-10-29

Thanks for your reply.

Actually I had an excel file and I make that excel file as attachment and instead of that if I want to send CHART item
to user email with FROM address Qlikview@reporting how can I change the below macro please help me

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.313"

Const SMTPPort = 25 ' Port number for SMTP

Const SMTPTimeout = 60 ' Timeout for SMTP in seconds

Path = "D:\Amelia Disousa\"

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

'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@uk.gt.com" 'Email Recipient

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

objEmail.Subject = "Dashboard" ' Subject

objEmail.TextBody = "This is Automated Email" 'Text Body

objEmail.AddAttachment "D:\Amelia Disousa\" & "Test_" & GetFormattedDate & ".xlsx"

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

end function

marcus_sommer · ‎2013-10-29

You need something like this:

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff "C:\test.xls"

This is then in xls and not xlsx - if there are not really an important reason, you should take xls.

- Marcus

Report Inappropriate Content · ‎2013-10-29

Thanks very much and it's working. Actually I was trying this personal edition and when I am clicking TEST button the excel file is going to outlook email. If I want to make use of this server in qvw application which is already in access point could you help me how it will go automatically to my user email wothout clicking any TEST button and without logging mt user into access point. Please let me know so that I will implement in Server.

marcus_sommer · ‎2013-10-29

You need any event which executed your macro, the easiest way is to use a button.

- Marcus

Report Inappropriate Content · ‎2013-10-30

Thanks and if I use Button how can my users will get excel file automatic to their outlook email
sorry could you explain a bit more as I would like to know without openeing access point I want my user get excel file to their email. Please help me this.

marcus_sommer · ‎2013-10-30

If a user should be able to execute the export + mailing you need testing it with access point + login as user + using from a user-environment. There are many things which could going wrong ... Are these exports standard-reports it could be easier to create this for each user per admin.

- Marcus