schedule VBA script to email cell value

Mykle

New Member
Joined
Mar 21, 2009
Messages
12
I have been doing a lot of research on macros and vba scripts recently. I essentially want to email the value of a cell which I know is possible with a macro. To make things a little more difficult, I want to schedule this using Windows Task Scheduler. Here is my exact situation; I take care of a bill for my house. If my roommates carry a balance, I want them to be emailed once a week of how much they owe me. I want this macro to be independent of my excel file, meaning I don't want to do run every time I open the file or anything. I only want it sent once a week. If anyone could please advise on how to develop this script, I would greatly appreciate it. Please let me know if you need additional info.

Current system:
Windows Vista
Office 2007
Thunderbird email
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Is this the method you would recommend? Macros are essentially vba scripts right? Thanks for the links.
 
Upvote 0
You can use VBScript to open your workbook and run a subroutine inside the workbook. A scheduled task can then be created which runs the VBScript (.vbs) file. Here is an example:

1. In a new Excel workbook, put this code in a standard module:
Code:
Option Explicit
Public Sub MySub(message As String)
    MsgBox Now & vbNewLine & message
End Sub
Save this workbook as MyWorkbook.xls

2. Put this code in a new Notepad file:
Code:
Dim objExcel

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:\temp\excel\MyWorkbook.xls"
objExcel.Visible = True
objExcel.Run "MySub", "Hello"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
Change the C:\temp\excel\ path to the location of your MyWorkbook.xls. Save the file as MyScript.vbs.

3. Run MyScript.vbs from a command prompt or by double-clicking it in Windows Explorer.

All manipulation of Excel objects can be done from VBScript, but it's easier just to have the .vbs file run an Excel VBA subroutine as shown above and then you have the Excel VB Editor syntax and highlighting available as you develop your code.
 
Upvote 0
Thanks John w. This looks like what I want to do. I don't understand your first step though. I already have an excel workbook. Am I making a new one or putting that code in my existing file? You wouldn't happen to know the code to send emails would you? Thanks again.
 
Upvote 0
My code is just a complete working example to show the technique. You would put the code for step 1 in your own workbook. The point is, the .vbs file is opening your workbook and calling the MySub subroutine within that workbook using the Run method. I happen to call Run with a parameter because I've coded MySub to expect a string argument, but if you don't need to pass anything to MySub you would call it using: objExcel.Run "MySub". Obviously MySub is just an example. Its name and contents can be anything. Hope that's clear.

Code for sending email from Excel can be found at http://www.rondebruin.nl/sendmail.htm.

Get the email code working within Excel first, then run it manually with your .vbs file. Then create a scheduled task to run the .vbs file.
 
Upvote 0
I think I am starting to understand. Thanks for all your help John. However, I'm having trouble understanding which code to follow from rondebruin. I want to send mail with an IF function. There will be 6 different if functions with 6 different email addresses. Since my primary email is Thunderbird, will this even work?
 
Upvote 0
You will have to experiment with the Ron de Bruin methods to find out what happens with Thunderbird.

You said you have Office 2007 installed, so try the methods in the Outlook object model (body) section, in particular http://www.rondebruin.nl/mail/folder3/mail4.htm.

Or try http://www.rondebruin.nl/cdo.htm, which it says doesn't matter which mail program you use.

Try to get the examples working on your computer, don't try to adapt them for your requirements just yet.

I also found http://www.ozgrid.com/forum/showthread.php?t=49302 which shows how to send email from Thunderbird using its command line interface.
 
Upvote 0
Wow. I am almost there. I can send emails from excel with Gmail. Here is my code so far:
Code:
Sub sendmail()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "*************@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*********"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

    With Sheets("Sheet1")
    strbody = "Here is your current balance:" & vbNewLine & vbNewLine & _
        .Range("A1")
    End With

    With iMsg
        Set .Configuration = iConf
        .To = "**********@gmail.com"
        .CC = ""
        .BCC = ""
        ' Note: The reply address is not working if you use this Gmail example
        ' It will use your Gmail address automatic. But you can add this line
        ' to change the reply address  .ReplyTo = "Reply@something.nl"
        .From = """"" <*********@gmail.com>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With


End Sub

Should I just manually set up a different email for all 6 people or is there a way to do a range so the code is a little more clean? Thanks again for all the help.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top