Export to excel MACRO and Excel to mailbox

[abhaysingh]

Hello every one

In my application Dashboard, whatever the analysis shown , I want first to export to excel and than that excel should be mailed to the particular mailbox I need...

I know it is possible through macro but I don't no the script....

can any one tell me the script for that???

and procedure to integrate it???

please help...

[ashfaq_haseeb]

Here you go.

Regards

ASHFAQ

[abhaysingh]

hi ashfaq can u please give the script of export to excel as well?????

[ecolomer]

here you have an example export to excel

[abhaysingh]

where??

[ecolomer]

I put attached two files qvw

The macro for one of them

sub Export

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true 'you can make it false, if you want to make it in the background

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "Export"

set XLSheet = XLDoc.Worksheets(1)

set MyTable = ActiveDocument.GetSheetObject("TB01") 'Change TB01 to the object you want to export to Excel (Object Properties > General tab > far right)

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

Mytable.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at A1

End Sub

[ashfaq_haseeb]

Look at this

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

 Path = "C:\temp\"
 FileName = "Test_" & GetFormattedDate  & ".xlsx"


 set XLApp = CreateObject("Excel.Application")


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

 Set v = ActiveDocument.Variables("vCount")
 Selection=v.GetContent.String


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

 XLDoc.Sheets(1).Columns("A:K").EntireColumn.AutoFit

 XLDoc.Sheets(1).Name = "Export"


 Set XLSheet = XLDoc.Sheets("Export")
 ActiveDocument.GetSheetObject("TX46").CopyTextToClipboard
 XLSheet.Range("A" & Selection).Select
 XLSheet.Paste 
 XLSheet.Range("A1").Select


 XLDoc.Sheets(2).Delete
 XLDoc.Sheets(2).Delete
 XLDoc.SaveAs Path & FileName
 XLApp.Quit

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


 myMessage.To = InputBox("Enter email address in johnsmith@mail.com format","Email Address")
 myMessage.Attachments.Add "C:\temp\" & FileName
 myMessage.Subject = "Test File " & Date()


 myMessage.Send
msgbox("Mail Send")
Set myMessage = Nothing 
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing


end sub

Regards

ASHFAQ

[#8]

Your code solved my problem! Thanks!

[Anonymous]

Hi it runs , have u any idea how can i put a filter before sending the report , thank u