Hi

I have a work sheet where we track which jobs are completed during the
work day the sheet is updated via workers punching in their numbers
what i am looking for is a macro that will send an email to
when ever the word "RG Complete" appears in column F.
The email needs to send the subject line corrisponding to column C in
the same row where the word "RG Complete" has appeared in column F.

The email needs to be sent only once not every time the workbook is
opened up.


--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=553949

This is what I have come up with from chopping up code i find on forums

When I run the macro I get an error on "Dim objOL As New
Outlook.Application" so i dont even know if the Code is any good but i
doubt it :(




Code:
--------------------

Sub SendEmail()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

On Error GoTo Err_Execute

LSearchRow = 1

LCopyToRow = 2

While Len(Range("F" & CStr(LSearchRow)).Value) 0


If Range("F" & CStr(LSearchRow)).Value = "RG Complete" Then

With objMail
.To = "
.Subject = "Automated Mail Response"
.Body = "This is an automated message from Excel. " & _
"The cost of the item that you inquired about is: " & _
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select & "."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

--------------------


--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=553949

Hi Vlad999,

When I run the macro I get an error on "Dim objOL As New
Outlook.Application" so i dont even know if the Code is any good but i
doubt it :(

Without otherwise looking at your code, you need to set a reference:

Tools | References | select 'Microsoft Outlook xx Object Library'

where xx is a version number.


Additionally, I would suggest that you change:

Dim objOL As New Outlook.Application

to

Dim objOL As Outlook.Application

In this connection, see Chip Pearsons explanation at:

http://www.cpearson.com/excel/variables.htm

See particularly the section entitled: 'Don't Use The New Keyword In A Dim
Statement'


---
Regards,
Norman


"Vlad999" wrote in
message ...

This is what I have come up with from chopping up code i find on forums

When I run the macro I get an error on "Dim objOL As New
Outlook.Application" so i dont even know if the Code is any good but i
doubt it :(




Code:
--------------------

Sub SendEmail()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

On Error GoTo Err_Execute

LSearchRow = 1

LCopyToRow = 2

While Len(Range("F" & CStr(LSearchRow)).Value) 0


If Range("F" & CStr(LSearchRow)).Value = "RG Complete" Then

With objMail
.To = "
.Subject = "Automated Mail Response"
.Body = "This is an automated message from Excel. " & _
"The cost of the item that you inquired about is: " & _
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select & "."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

--------------------


--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile:
http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=553949