Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 11th, 2006, 06:39 PM
Registered User
Join Date: Jan 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Emailing from Excel

Hi, I'm trying to run vb script that emails to specific email accounts whenever an account is "Over Due" in an excel workbook. I like the script from http://p2p.wrox.com/topic.asp?TOPIC_ID=24650 , but I get a Visual Basic error message "System Error &H80004005 ... etc".

Other questions:

Is there anyway of removing the outlook prompts when automating?
Is there anyway of running the script automatically every morning? Does the file need to be open in excel?

I am new at this and would appreciate help in solving this.

My excel file can be downloaded at:



Script from http://p2p.wrox.com/topic.asp?TOPIC_ID=24650 :

Sub sendemail()

Dim OutlookApp As Object
Dim myBodyText As String
Dim myLoop As Integer
Dim myRow As Integer
Dim myRecipient As String
Dim myFirstCellAdd
Dim myCounter As Integer

myCounter = 0
    Cells.Find(What:="Over Due", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
Do Until ActiveCell.Address = myFirstCellAdd
    myCounter = myCounter + 1
    myCurrAdd = ActiveCell.Address
    If myCounter = 1 Then myFirstCellAdd = ActiveCell.Address
    myRow = ActiveCell.Row
    ActiveSheet.Range("A" & myRow).Select

    Application.ScreenUpdating = False

    For myLoop = 1 To 255
        If ActiveCell.Value = "" Then myBodyText = myBodyText & "" & ActiveCell.Value Else myBodyText = myBodyText & " " & ActiveCell.Value
        If ActiveCell.Column = 1 Then myRecipient = ActiveCell.Value
        If ActiveCell.Column = 256 Then myBodyText = myBodyText Else ActiveCell.Offset(0, 1).Select

    Set OutlookApp = CreateObject("Outlook.Application")
     With OutlookApp.CreateItem(olMailItem)
        .Subject = "My Subject Line"
        .Body = myBodyText
        .To = myRecipient
    End With
    Cells.Find(What:="Over Due", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    MsgBox (myCounter)
    Application.ScreenUpdating = False

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Emailing from Excel using webquery acarlson ASP.NET 1.0 and 1.1 Basics 18 October 15th, 2008 03:32 PM
Opening and emailing Excel sheet through ASP.Net venterjo ASP.NET 1.0 and 1.1 Basics 0 October 10th, 2005 09:55 AM
dynamic emailing ips2004 JSP Basics 1 December 8th, 2004 01:57 PM
Auto-Emailing hafizism Excel VBA 1 October 29th, 2004 09:43 AM
emailing forms.. kyootepuffy Classic ASP Databases 3 August 26th, 2003 03:54 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.