This is a simple example to run a query against a Microsoft SQL server, generate an XLS file with a timestamp in the file name, then email that file to selected recipients. The SMTP connection setup works for internal Outlook email that does not require authentication.

Run SQL Query Then Send XLS with Timestamped Filename Using R.r

##### This will run a query against a Microsoft SQL server,
##### generate an XLS file with a timestamp in the name from the SQL result,
##### then email that XLS file to selected recipients
##### smtp connection works for internal Outlook email not requiring authentication

require(RODBC)
require(xlsx)
require(mailR)

#define the query (insert your query here)
myQuery <-
  'SELECT ...
FROM ...
WHERE ...
;'

#open the ODBC connection
ch <-
  odbcDriverConnect(
    "Driver=SQL Server; Server=servername\\instance; Database=databasename; UID=username; Pwd=password"
  )

#run the query
sqlResult <- sqlQuery(ch, myQuery)

#close the ODBC connection
odbcClose(ch)

#define the variable for the timestamp
TS <- paste(format(Sys.time(), "%Y%m%d_%H%M%S"))

write.xlsx(sqlResult, paste("YourFileName_", TS, ".xls", sep = ""))

send.mail(
  from = "[email protected]",
  to = c(
    "[email protected]",
    "[email protected]"
  ),
  bcc = "[email protected]",
  subject = "Subject line for email",
  body = "Body of email message",
  html = T,
  smtp = list(host.name = "smtp.emailaddress.com", port = 25),
  authenticate = F,
  attach.files = paste("YourFileName_", TS, ".xls", sep =
                         "")
)