Excel Emails

[schugh]

I have been previously helped by being directed to a site on excel emails, but i really dont have the program writting knowledge to convert what i see into what i want. Basically I have a table (made a simple version below) i have various orders but depending on teir status they may go on hold while awaiting things like customer information or an estimate approval, these are represented by various different "hold codes" the most important one is the "?" hold code.......sooo what I am looking for is the code for when a hold code changes to or from a "?" an email is sent stating the "customer name" and "serial number" and that it's hold code status has changed. Any help would be greatly appreciated.

Customer name	serial no.	work scope	hold code
A	1	OVERHAUL	[
B	2	REPAIR	[
C	3	REPAIR	E
D	4	REPAIR	?
E	5	OVERHAUL	?
F	6	OVERHAUL	2
G	7	REPAIR	8
H	8	REPAIR	8

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col span="4"></colgroup>

 

[JoeMajestee]

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 4 Then
    If Target.Value = "?" Then SendEmailUpdate Target.Offset(0, -3).Value, Target.Offset(0, -2).Value
End If
End Sub
Sub SendEmailUpdate(strCustName As String, strSN As String)
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .to = "[EMAIL="ron@debruin.nl"]ron@debruin.nl[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Something has changed"
        .Body = "Customer: " & strCustName & ", SN: " & strSN & " has changed to ?"
        .Display
'or use        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Assume you know how to assign it to worksheet change?
Took the email part from Ron de Bruin Excel Automation

 

[schugh]

thank you....i will give it a go.

 

[schugh]

Sorry my basic skills have failed me again, I can't seem to get it to work with the sample spreadsheet, I have entered the code but nothing happens when column 4 is changed to a "?", perhaps I don't know how to assign it to worksheet change after all.

 

[JoeMajestee]

In the visual basic editor, upper left window which I think is called the project explorer, you can expand until you see the sheets of the target workbook. Right-click on the target ThisWorkbook and select "View Code". At the top left of that window is a drop-down that should say (General). Change that to (Workbook). The other drop-down up there should say SheetChange.

Actually, now that I think about it, the code would probably be better assigned to the target workSHEET. Right-click on the WorkSheet > View Code. Then dropdowns to (WorkSheet) and (SelectionChange). The first line of code would change from:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

To:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[schugh]

thanks again.