schedule VBA script to email cell value

M

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Macro script and scheduler
Replies
1
Views
300
Micron
M
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie
I
  • Question Question
Help with VBA script please
Replies
3
Views
426
jolivanes
jolivanes
O
  • Question Question
Add Signature to Email Macro
Replies
1
Views
366
starl
starl
I
  • Question Question
How to Set Excel's "Find All" Feature to Search Entire Workbook by Default rather than sheet only ?
Replies
1
Views
168
RoryA
RoryA

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,013
Latest member
Shubashish_Nandy

Share this page

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