schedule VBA script to email cell value

[Mykle]

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

 

[John_w]

If you change the sendmail subroutine to accept 2 parameters - an email address and email body text - it can then be called from another subroutine to send emails to your 6 people.

The code in Email_Balances assumes the balance amounts are in cells A1:A6 and the corresponding email addresses are in cells B1:B6. Put all this code in a module.

Option Explicit

Public Sub Email_Balances()
    
    Dim cell As Range
    Dim balance As Variant
    Dim strEmailText As String
    
    With Sheets("Sheet1")
        For Each cell In .Range("A1:A6")
            balance = cell.Value
            If balance > 0 Then
                strEmailText = "Here is your current balance:" & vbNewLine & vbNewLine & balance
                sendmail cell.Offset(, 1).Value, strEmailText
            End If
        Next
    End With

End Sub


Sub sendmail(strRecipientEmailAddress As String, strEmailBodyText As String)
    Dim iMsg As Object
    Dim iConf As Object
    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 iMsg
        Set .Configuration = iConf
        .To = strRecipientEmailAddress
        .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 = strEmailBodyText
        .Send
    End With

End Sub

This code has to go in your workbook, although as I said in my first post everything can be done using VBScript. I would just use the VBScript part for running the Email_Balances subroutine via Task Scheduler. For this, the line in the MyScript.vbs would be:
objExcel.Run "Email_Balances"

 

[Mykle]

The code did not work. I think it is missing the email address part. Where do I put that the email address are in B1:B6?

 

[John_w]

The code works for me. cell.Offset(, 1).Value picks up the corresponding email address. This is the cell 1 column to the right of the 'cell' variable cell. To check this, put the following line in after the balance = cell.Value line:

MsgBox "Balance=" & balance & " Email=" & cell.Offset(, 1).Value

Obviously, you have to change all the ****s to something valid for the email sending to work.

 

[Mykle]

Ok what that code it is picking up the correct email addresses. All email info is correct. On error, it hightlights .From line. What else should I troubleshoot?

 

[Mykle]

Ok now I am just being picky but I am learning so much. How do I format the cell in the email to show dollar instead of plain?

 

[John_w]

So is the .From line error fixed now?

To show a dollar sign, simply add it to the email text like this:

strEmailText = "Here is your current balance:" & vbNewLine & vbNewLine & "$" & balance

 

[Mykle]

Yes the from line is fixed. I don't only want to show a dollar sign but I want the balance to be formatted as currency. Like instead of 15.245456 I want $15.25. Do you know how to do that?

 

[John_w]

If the balance cells are formatted as Currency then to format the string in exactly the same way use:

Dim sFormattedValue As String
sFormattedValue = Format(balance, cell.NumberFormat)

Otherwise, you could use something like:

sFormattedValue = Format(balance, "$#,##0.00")

 

[Mykle]

Do I replace Dim balance As Variant with Dim sFormattedValue As String or add it? Thanks.

 

[John_w]

Try replacing it and that should tell you why you need to add it instead . Make sure you have Option Explicit at the top of the code.

And, if the logic isn't obvious, you also need:

strEmailText = "Here is your current balance:" & vbNewLine & vbNewLine & sFormattedValue

but I expect you know that.