Hi,
I need to send report as an excel attachment.
I am using below macro code.
Sub VehicleStock
on error resume next
SET Doc = ActiveDocument
'Assign Inv_year field to FieldName, because the report generates for each Inv_year
SET f = Doc.Variables("vInc_Country22")
DIM Var1
Var1="VehicleStock"
FieldName = f.GetContent.STRING
Doc.Fields(FieldName).Clear
SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)
For i=0 to Field.Count-1
Doc.Fields(FieldName).SELECT Field.Item(i).Text
Doc.GetApplication.WaitForIdle
Path = "E:\BSC_Test\Application\ExcelTask\"&Var1&"_"&Field.Item(i).Text&".xls"
' Path = "E:\Qllikview\BSC dashboard\Incadea Excel\"&Field.Item(i).Text&".xls"
' Path = "E:\Qllikview\BSC dashboard\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"
set obj = Doc.GetSheetObject("CH2662")
obj.ExportBiff Path
Next
'adressat=inputbox("Please insert the receivers email address")
'timestamp1 = Timer
'Timestamp="-"&Year(now())&"-"&month(now())&"-"&day(now())&"--"&hour(now())&"-"&minute(now())&"-"&second(now())
Dim objEmail
Const cdoSendUsingPort = 2
Const cdoBasicAuth =1
Const cdoTimeout = 60
Const cdoNTLM = 2
Const mailServer = "154.53.30.234" -->>it is my smtp server IP
Const SMTPport =25 -->>it is my smtp port no
'msgbox "email is sending22"
mailusername = "abc@companydomain.com"
mailpassword = "abc123"
mailto ="xyz@companydomain.com"
mailSubject = "Title"&Timestamp
mailBody = "Attached the actual Report"
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
'msgbox "email is sending"
With objFlds
objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = TRUE
objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
objConf.Update
End With
objEmail.To = mailto
objEmail.From = mailusername
objEmail.Subject = mailSubject
objEmail.TextBody = mailBody
objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_1.xls"
objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_2.xls"
objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_3.xls"
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
'timestamp2 = Timer
'MsgBox("The E-Mail to"&" "&adressat&""&" was sent."&" "&"Sending took "&" "&Round((timestamp2-timestamp1)/60, 2)&" "&"min .")
End Sub
Please find screenshot of code as well.
Thanks in advance!!
