After gathering information from multiple sources for this configuration, I decided to write a blog post and maybe help someone else out. Below are the steps required in SAP to schedule a job to send a report via email in an excel compatible format. Please note, I am on version EHP7 for ERP 6.0.
Create a variant on the report you would like to send. The variant should be exactly what you expect the end results for the user to be.
Create a distribution list with the appropriate email addresses using tcode SO04. You need to know the name of the distribution list to schedule the job. The name is listed on the Attributes tab of the list.
You also have to configure Output Formats for SAP Documents if you want the file to be in Excel compatible format. Please note: this may change other scheduled outputs, only change if you’re sure this is OK to change.
Go to tcode SCOT. Click Business Communication Administration > Settings > Outbound Messages > SMTP Nodes. Under SMTP Nodes, double-click on Internet. Ensure “ABAP List” is set to “TXT”. Click the green check.
Create a job in SM36 to send the report. You need to know the program name for the job. To setup the job to send to the distribution list, click the Spool List Recipient button under the General Data area of the job.
Then press F4 or click the match code box to open possible recipients.
Select Distribution Lists radio button and enter the name of your distribution list, then click OK.
Your distribution list should be listed in the Recipient field. Click Copy.
In the Define Steps portion of the job scheduler, enter the program name and variant. Remember to change the Print Specifications to LOCL and SAP Spool Only for Now, or every time the job runs, the results will also print on your default printer.
Once the report is scheduled, use tcode SOST to see the outbound messages.