Macro export asking every time to replace file and showing Outlook message?

Tue, 16 Feb 2016 15:43:10 GMT

Hi,

There is a Button in qvw where will do export excel to user email. but when I am testing button every time it is asking replace existing file and Microsoft Outlook message.

I want the file automatically exported to user email with out me doing any replacing or allowing outlook message. Please can anyone help how to sort out this?

Macro used:

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 = "C:\Qlikview\QV Documents_Development\Automate Export\"
FileName = "xx_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

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

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

XLDoc.Sheets(1).Columns("A:BJ").EntireColumn.AutoFit
XLDoc.Sheets(1).Columns("A:A").ColumnWidth = 9.57
XLDoc.Sheets(1).Columns("B:B").ColumnWidth = 7.71
XLDoc.Sheets(1).Columns("C:C").ColumnWidth = 14
XLDoc.Sheets(1).Columns("D:D").ColumnWidth = 10.71
XLDoc.Sheets(1).Columns("E:E").ColumnWidth = 12.71
XLDoc.Sheets(1).Columns("F:F").ColumnWidth = 24.57

XLDoc.Sheets(1).Name = "Export"
XLDoc.SaveAs Path & FileName
XLApp.Quit
' SMTPServer = "smtp.office365.com"
'    Const SMTPPort = 587                 ' 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
'
Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.BodyFormat = 3 'Outlook.OlBodyFormat.olFormatRichText

myMessage.To = "adam.mk@xx.us.com"
myMessage.CC = "olu.ty@fr.us.com"
myMessage.Body = "Hi, This is automated report from Qlikview"
myMessage.Attachments.Add "C:\Qlikview\QV Documents_Development\Automate Export\" & FileName
myMessage.Subject = "XX " & Date()

myMessage.Send

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

end sub

Re: Macro export asking every time to replace file and showing Outlook message?

m_woolf — Tue, 16 Feb 2016 16:08:01 GMT

For the Excel msg, try:

XLApp.DisplayAlerts = True

before the SaveAs line

For the Outlook, Google has many opinions:

Maybe:

VBA excel outlook / A program is trying to send mail using Item.Send

Re: Macro export asking every time to replace file and showing Outlook message?

Tue, 16 Feb 2016 16:59:38 GMT

Thanks. Tried it still asking replace excel file.

Re: Macro export asking every time to replace file and showing Outlook message?

m_woolf — Tue, 16 Feb 2016 18:19:58 GMT

My bad. I should have said .DisplayAlerts = False

topic Macro export asking every time to replace file and showing Outlook message? in QlikView

Macro export asking every time to replace file and showing Outlook message?

Re: Macro export asking every time to replace file and showing Outlook message?

Re: Macro export asking every time to replace file and showing Outlook message?

Re: Macro export asking every time to replace file and showing Outlook message?