Cell value change macro

wildignite

New Member
Joined
May 5, 2009
Messages
6
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hello and welcome to MrExcel.

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

Sheet code:

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

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

New Member
Joined
May 5, 2009
Messages
6
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Which e-mail client do you use? Outlook, Outlook Express, Other...
 

wildignite

New Member
Joined
May 5, 2009
Messages
6
Outlook. '03

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

wildignite

New Member
Joined
May 5, 2009
Messages
6
How would I write this?
Code:
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

New Member
Joined
May 5, 2009
Messages
6
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

New Member
Joined
May 5, 2009
Messages
6
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.


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,335
Messages
5,467,999
Members
406,562
Latest member
Isbenji

This Week's Hot Topics

Top