Send email based on Cell value

[somunair]

Hi
I have one excel file which is shared with many departments ( like sales, purchase , distribution and finance)

In this file we capture all sales orders and the status ( using drop down) of it. ( status are like, PO waiting, Approved drawing submitted, Ready to go, Material received, Completed)

what i am looking for is, like of Sales change the status to" Ready to go" purhcase department should get an auto populated emaail, saying that this order is ready to process. Please Issue purchase order.

( This must happen to all the new record added to this sheet , upon change of status from drop down. , I am using outlook for email.


any one will be able to guide me with the solution.

Thanks

Sumesh

 

[ranman256]

NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; ctl-G, menu,tools, references: Microsoft Outlook X.X object library
have a macro run when the status box changes, then check to send email

sub cboBox_Change()
if range("X23") = 'Ready to go' then
   sTo = range("a43").value
   sSubj = "order " & range("A123").value & " is ready to process
   sBody = "Dear client,....."

   call Email1(sTo, sSubj, sBody)
endif
end sub

put this code into a module for all to use.

Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail

'NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; ctl-G, menu,tools, references, Microsoft Outlook XX Object library
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.createitem(olMailItem)


With oMail
    .To = pvTo
    .Subject = pvSubj
    If Not IsNull(pvBody) Then .Body = pvBody
    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
    
    .Display True
    
     .Save    'draft, we are NOT sending...we save as draft
    '.Send     
    
End With

Email1 = True
endit:
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
End Function

 

[somunair]

Dear Ranman
Thank you for your guidance.

I have some doubt on the solution you suggested.

In my data file, i am not using combo box, but is from Data Validation ( as i need the same value in all the column)


A B C D E F G H I J

So what change should i make in this code? also this should happen in any Row upon change of " I " Column



Awaiting for your guidance.

Thansk

Sumesh