Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 10th, 2005, 04:52 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Send email from Excel

Hi

I have an Excel sheet that tracks Action Items
I wrote a formula that each time an Action Item is Over Due the status changes to "Over Due"

I need a macro that will recognize that automatically and will copy the specific row and send an email to the person responsible for this Action Item with a message "Your Action Item is Over Due….."

You can find the Excel sheet in Hi

I have an Excel sheet that tracks Action Items
I wrote a formula that each time an Action Item is Over Due the status changes to "Over Due"

I need a macro that will recognize that automatically and will copy the specific row and send an email to the person responsible for this Action Item with a message "Your Action Item is Over Due….."

You can find the Excel sheet in ftp://dannyn:galit123@194.90.236.210/


Can you please help?

Thanks



Can you please help?

Thanks


Reply With Quote
  #2 (permalink)  
Old January 14th, 2005, 01:59 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was unable to access your Excel file, pls let me have it on my email addr amjad.s.mahmood@gsk.com

Reply With Quote
  #3 (permalink)  
Old January 18th, 2005, 09:18 AM
Registered User
 
Join Date: Aug 2004
Location: Birkenhead, Wirral, United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am after the same thing, well sort of.

I have a spreadsheet that has various columns but their are two columns of interest one is the 'Name' column and the other is the 'Due Date' column. When a Due Date in the column on the spread sheet matches that of the computer data I want it to send an email to a specific person or persons with the information from the 'Name' column automatically, and giving the email a subject of 'Date for review now due'

Can anyone help or point me in the right direction.. It would be very much appreciated.

Regards

John

Reply With Quote
  #4 (permalink)  
Old January 21st, 2005, 07:51 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

have a bash at this, things to consider are that to ensure that emails are not sent in an automated and spamming nature microsoft have set the feature of requiring a confirmation for the email to be sent before it actually goes...
Assumptions...
there is only one cell beingclassed as overdue. but if there isn't you can have a loop to search for each instance of the "Over Due" phrase.
The email recipient name in the code should be changed to that of a parameterised cell content, or similar, to allow for the different users.
Change the subject line text to what ever you want.

Sub sendemail()

Dim OutlookApp As Object
Dim myBodyText As String
Dim myLoop As Integer
Dim myRow As Integer

    Cells.Find(What:="Over Due", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

    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 = 256 Then myBodyText = myBodyText Else ActiveCell.Offset(0, 1).Select
    Next
    myBodyText = myBodyText & " " & ActiveCell.Value

    Set OutlookApp = CreateObject("Outlook.Application")
     With OutlookApp.CreateItem(olMailItem)
        .Subject = "My Subject Line"
        .Body = myBodyText
        .To = "Paba matthew"
        .Send
    End With
    Application.ScreenUpdating = False
End Sub


cheers

Matthew

Reply With Quote
  #5 (permalink)  
Old January 23rd, 2005, 06:34 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Matthew

Thank you for your help
How I can I make this to chose the relevant person to send the email?
Currently it sends the email to a pre defined person (To = "Paba matthew")

Thanks


Reply With Quote
  #6 (permalink)  
Old January 24th, 2005, 07:36 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I presume the recipient's email address is located in one of the columns of data?

if so, use this...

I have assumed that the email address is located in column1 of the data. I f this assumption is wrong just shout.

Sub sendemail()

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

    Cells.Find(What:="Over Due", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

    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
    Next
    myBodyText = myBodyText & " " & ActiveCell.Value

    Set OutlookApp = CreateObject("Outlook.Application")
     With OutlookApp.CreateItem(olMailItem)
        .Subject = "My Subject Line"
        .Body = myBodyText
        .To = myRecipient
        .Send
    End With
    Application.ScreenUpdating = False
End Sub


Cheers

Matthew

Reply With Quote
  #7 (permalink)  
Old January 24th, 2005, 11:25 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Matthew

Thank you for your help
It works partially
1. It sends only one email only to the first person on the list or to the person the curser stands on
2. It sends the email even if the Action Item is "Done" and not "Over Due"

Once again thanks for your help

Danny

Reply With Quote
  #8 (permalink)  
Old January 24th, 2005, 02:16 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Give this a whirl...

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
Range("A1").Select
    Cells.Find(What:="Over Due", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
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 3
        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
    Next
    ActiveSheet.Range(myCurrAdd).Select

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

End Sub



cheers

Matthew

Reply With Quote
  #9 (permalink)  
Old January 24th, 2005, 02:18 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ooops!

change the 'For myLoop = 1 To 3' to 'For myLoop = 1 To 255'

sorry,

M




Reply With Quote
  #10 (permalink)  
Old January 25th, 2005, 05:01 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Matthew

The first problem is solved.
The remained problem is that it sends the email even if the Status column is not "Over Due"

Thanks in advance


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Macro - Send Email and Attachments robmill101 Excel VBA 1 October 21st, 2008 08:10 AM
Excel VBA - Send email with html message body elad Excel VBA 1 July 16th, 2007 03:28 PM
send email via excel for action items with overdue miracles Excel VBA 0 May 16th, 2007 02:20 AM
Send by email tsimsha Classic ASP Databases 1 October 1st, 2005 03:03 AM
send email X-Ken VS.NET 2002/2003 8 July 2nd, 2004 06:13 AM



All times are GMT -4. The time now is 06:29 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.