form auto email question

[vanwhyjr]

Hello Everyone,

New to the forum.
I have been asked by the boss to set up a sheet that has a list of our active jobs. One of the columns will have the customers email and one will have a column with a drag down list (In-process, on hold, complete).
I would like the spreadsheet to automatically send a customer survey to the email in the row that is changed to complete.
Is that something possible with a Macro? I have some basic macro knowledge. Not real good with the VBA code.

Thank you for any recommendations.

The nube!
George V.

 

[mumps]

That is certainly possible. Is the customer survey in a separate file? If so, what is the name of the file and what is the full path to the folder where it is saved?

 

[vanwhyjr]

Yes the survey is a separate file. It would be located on one of our share drives.

 

[mumps]

In which column are the customers emails and in which column are the drop down lists?

 

[vanwhyjr]

The email is in column B and the drop down is in column F.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Please note the text in red. Close the code window to return to your sheet. Make a selection in the drop down list in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 6 Then Exit Sub
    Dim OutApp As Object, OutMail As Object, sPath As String
    If Target = "complete" Then
        sPath = "Enter full path to the file here."
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = Range("B" & Target.Row)
            .Subject = "Enter subject line here."
            .HTMLBody = "Enter body of email here."
            .attachments.Add sPath
            .Display
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[vanwhyjr]

Sweet, That works. If I change "Display" to make it send automatically?

 

[mumps]

Yes.

 

[vanwhyjr]

I can't get it to auto send. If I get rid of display and add .Send it goes to debug.

 

[mumps]

When you click "Debug", what is the error message and which line of code is highlighted?