Need Help Sending automatic emails depending on cell value

[s_horne]

Hello Ive, got a spreadsheet where column C is a list of drop down list of our curriers, and i want to know if its possible for it to send automatic emails when one of the cells is updated to a certain currier (in this case a currier called gopher) if so is it also possible for it to include the sheet title e.g i have a worksheet called "board" and there e five sheets once for each day of the working week and i want it to include whichever day i have added the job to.

 

[KOKOSEK]

I am just guessing.
on Worksheet_Change events you have to check that Target.value is equal value choosen by dropdown, something like:

if Target.value=range("C1").value then 'let's say C1 is cell with dropdown list
  macroWithEmail
end if

if you have to attached particular sheet into email, you have to save it first (as temp file).
That all what I can say without any more information.

 

[s_horne]

I am just guessing.
on Worksheet_Change events you have to check that Target.value is equal value choosen by dropdown, something like:

if Target.value=range("C1").value then 'let's say C1 is cell with dropdown list
  macroWithEmail
end if

if you have to attached particular sheet into email, you have to save it first (as temp file).
That all what I can say without any more information.

Thanks for that however its the whole of Column C that has the dropdown list and any one of them could be the certain this text. how could i get it to run if any of these cells are changed, also is it possible to have the email include details of a fixed cell e.g H7 ?
sorry about all this i'm fairly new to macros and still trying to get my head around it.

 

[s_horne]

Sorry i meant to say any one of the cells in column C could have this certain text, and i wanted to know how i could get it to run if any of them changed to the text "Gopher"

 

[KOKOSEK]

So other way then.
On Worksheet_Change event do the check like:

' Find the name Gopher in the range C:C
[TABLE="width: 278"]
[TR]
  [TD="width: 278"]Dim rgFound As  Range[/TD]
[/TR]
[/TABLE]
Set rgFound = Range("C:C").Find(Target.value)
If Not rgFound Is Nothing Then
  macro_with_email
End If
[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR]

 

[s_horne]

So other way then.
On Worksheet_Change event do the check like:

' Find the name Gopher in the range C:C
[TABLE="width: 278"]
[TR]
[TD="width: 278"]Dim rgFound As  Range[/TD]
[/TR]
[/TABLE]
Set rgFound = Range("C:C").Find(Target.value)
If Not rgFound Is Nothing Then
  macro_with_email
End If

Thanks for that, thats working great, is it possible to have it run that macro automatically when the cell changes, as at the moment that only seems to work when i run the macro

 

[KOKOSEK]

you have to go into Sheet code and put all VBA macros into

Private Sub Worksheet_Change(ByVal Target As Range)
[B]  'put all vba here it runs when anything on particular sheet has changed[/B]
End Sub

 

[s_horne]

Hi Again sorry about all this but for some reason it runs the macro all when ever anything changes in column C and not only when Gopher is entered
the code ive got at the moment is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgFound As Range
Set rgFound = Range("C:C").Find(Target.Value)
If Not rgFound Is Nothing Then
macro_with_email

End If


End Sub

Sub macro_with_email()
Dim xOutApp As Object
Dim xMailItem As Object
Dim xName As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.createitem(0)
xName = ActiveWorkbook.FullName
With xMailItem
.To = "spares@oceanairuk.com"
.CC = ""
.Subject = "confirmation of booking"
.Body = "Hi, just a quick confirmation that we have you booked in for a collection on " & Chr(13) & Chr(13) & "File is now updated."
.Attachments.Add xName
.Display
'.send
End With
Set xMailItem = Nothing
Set xOutApp = Nothing
End Sub

is there a reason its doing it all the time?

 

[s_horne]

i might not be making this clear, and apologies for that but in this drop down menu i have over 10 curriers and i only want it to send emails when a specific 1 is chosen, is it possible to do that?
Thanks again

 

[KOKOSEK]

i might not be making this clear, and apologies for that but in this drop down menu i have over 10 curriers and i only want it to send emails when a specific 1 is chosen, is it possible to do that?
Thanks again

Choosen where? you've said that all cells in col. C are dropdown lists.