Cell value change macro

[wildignite]

Is there a way to send an email to a particular recipient if the value in a cell equals their name. For example, Four Names: JOSH, TIM, ALAN, RON. Alright, when an owner, let's say JOSH(in column A row 3) is changed to TIM, I would like to have a macro that automatically sends and email to TIM to tell him a certain task is ready for him. If the names are in the "A" column then the subject would be in the "C" column. I hope I have explained myself properly. I am a novice in writing VB. Please help. Thank You.

 

[VoG]

Hello and welcome to MrExcel.

Based on http://www.rondebruin.nl/mail/change.htm (please read this for where the code goes):

Sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Call Smail(Target.Value, Target.Offset(, 2).Value)
End Sub

Module code

Sub Smail(recip As Variant, subj As Variant)
    Dim iMsg As Object
    Dim iConf As Object
    '    Dim Flds As Variant
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With
    With iMsg
        Set .Configuration = iConf
        .To = recip
        .CC = ""
        .BCC = ""
        .From = """Ron"" <ron@something.nl>"
        .Subject = subj
        .TextBody = "xxxx" 'put message here in quotes
        .Send
    End With
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub

 

[wildignite]

I am getting an error on .Send

how do i find my SMTP Server? I know your probably rolling your eyes right about now. I told you I was a novice. Could the address be something like mail.yahoo.com? Where yahoo is my company name.

Is the error linked to me not inputting the needed info for the server?

 

[VoG]

Which e-mail client do you use? Outlook, Outlook Express, Other...

 

[wildignite]

Outlook. '03

If it helps, I am also seeing the error saying the sendusing value is invalid.

 

[VoG]

Take a look at this: http://www.rondebruin.nl/mail/folder3/smallmessage.htm

You need to change the relevant fields as per my original reply.

 

[wildignite]

How would I write this?

Sub Mail_with_outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String

 

    Set OutApp = CreateObject("Outlook.Application")

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

 
If Cells = "JOSH" Then
    strto = "jmilton@worxofwood.com"
    strcc = ""
    strbcc = ""
    strsub = "Important message"
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed"
    End If
    
 

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Send
    End With

Where I have the if/then statement. Can I say if the cell changes to "JOSH" send to this email? And if the cell changes to "RON" send to this email address? And so on. Is this possible?

​

 

[wildignite]

This code worked for me, of course, before I threw up in it by adding that if then statement. I just wanted to show what I was thinking.

 

[wildignite]

Here is what I am using now. A friend of mine helped me out. But it is still not working. I am getting an error saying that niether the from or to sender is filled out. Do you see anything wrong with this code? I have the wrong email and pass word in there for obvious reasons.

Sub Smail(recip As Variant, subj As Variant)
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "jmilton@yahoo.com" 'put your email address of your service provider
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "jm0055785"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yahoo.com" 'ex smtp.gmail.com


        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With


    'body of email
    strbody = "Hi there i am excel" & vbNewLine & vbNewLine & _
              "my code rocks" & vbNewLine


    With iMsg
        Set .Configuration = iConf
        .To = recip '"jmilton@yahoo.com" 'who ur sending to
        .CC = ""
        .BCC = ""
        .From = """"""
        .Subject = subj
        .TextBody = strbody
        .Send
    End With
End Sub