Solved

How do I schedule an excel file to be sent automatically via email

Posted on 2013-01-16
5
389 Views
Last Modified: 2013-02-16
I would like to automate the sending of an excel spreadsheet via email to be triggered on a schedule defined in windows 7. Is it possible to use the task scheduler? IF so how?
0
Comment
Question by:mbs2000
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Alex_W
ID: 38783329
This may be of some use:

http://www.outlookforums.com/threads/34063-auto-send-attachment/

I think you may end up with a plug in or 3rd party application.
0
 
LVL 9

Accepted Solution

by:
TazDevil1674 earned 250 total points
ID: 38783367
There are many ways this could be done using either Batch File or VBS.


VBS Example

Set outobj = CreateObject("Outlook.Application")
Set mailobj = outobj.CreateItem(0)
Set fso = CreateObject("Scripting.FileSystemObject")

SigString = "C:\Users\USER\AppData\Roaming\Microsoft\Signatures\Signature.htm"

If fso.FileExists(SigString) Then
	Signature = GetBoiler(SigString)
Else
	Signature = ""
End If

With mailobj
	'.SentOnBehalfOfName = 
	.To = "alpha@domain.com"
	.CC = "beta@domain.com" & ";" & "charlie@domain.com"
	.Subject = "Automated Report"
	'.Body = "As requested, please see attached..." & vbNewLine & Signature
	.HTMLBody = "<HTML>" & _
        "<p style='font-family:calibri;font-size:14'>" & _
		"As requested, please see attached..." & _
		"<BR><BR>" & Signature & _
		"</HTML>"
	.Attachments.Add "\\unc_server_anme\share\file.pdf"
	.Attachments.Add "D:\files\excel.xls"
	' uncomment .Send to send automatyically - comment out .Display
                .Display
	'.Send
End With

'Clear the memory
Set outobj = Nothing
Set mailobj = Nothing

Function GetBoiler(ByVal sFile ) 'As String) 'As String
'Dick Kusleika
    Dim fso 'As Object
    Dim ts 'As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Open in new window


Batch File Example
Set objEmail = CreateObject("CDO.Message")

objEmail.From = "helpdesk@fabrikam.com"
objEmail.To = "administrator@fabrikam.com"
objEmail.Subject = "Server down" 
objEmail.Textbody = "Server1 is no longer accessible over the network."
objEmail.AddAttachment "C:\Scripts\Output.txt"

objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "smtpmailer" 
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update

objEmail.Send

Open in new window


Batch file would be good if you may not be logged on or have outlook open...  

You could then set up a scheduled task to run either...
0
 
LVL 8

Assisted Solution

by:d0ughb0y
d0ughb0y earned 250 total points
ID: 38783403
If you're using Outlook, an approach that I have seen in the past (not recently, mind you) is to use an Outlook add-on, like Slipstick. That should be able to do what you're asking, but it is shareware, and ultimately, you'd have to pay for it.

If you're not using Outlook, then you can use the scheduler to send it, but you're going to need to do a little coding, and/or use some tools to get it done. I've done it with Google, for example, by downloading and using SendEmail for Windows, or Blat for Windows.

I've used both, but the one I remember best was SendEmail. The command that you're going to set as your action is going to be something like this. (Remember, I'm using Google as my host here.)
sendemail.exe -f <your email address> -t <the target email address> -u <your subject line> -m <your message> -a <the complete path to the file> -s smtp.gmail.com:587 -xu <your login account - could be the same as your email address> -xp <your account password> -o tls=yes

That's what you'd use. You could put it into a batch file, and then call that from the scheduler. Or you could just use sendemail.exe as the action, and that long string as the optional parameters. Make sure you use the folder where you have the sendemail executable, as the Start In folder.
0
 
LVL 8

Expert Comment

by:d0ughb0y
ID: 38783439
Just wanted to point out that the "Batch file" listed above, isn't a batch file. It's VBS as well. Try typing any of those commands at the C:\ prompt, and see what happens. Heck, try typing them at the PowerShell prompt and see what happens.

That said, that VBS would probably do the job too.
0
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 38783534
@d0ughb0y, thanks for spotting that...  brain not working fully today!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question