0

Just started experimenting with VBA today. Creating Excel sheet to track SOLD, PENDING, LOST that will allow salesmen to click a button to send a group email to one category at a time. After much searching I found some code that works well to send group email by checking a column to make sure a proper address is there. I found some other code that I thought would check the "Job Status" column so that only the "SOLD" or whatever would be chosen for email. I am a clueless beginner and need help. Here is the code that was working until I added the - If Sh.Cells(Target.Row, 7) = "PENDING" Then - part. Any help would be greatly appreciated. Thanks!

Private Sub CommandButton1_Click()
Dim cell As Range
 Dim strto As String
 For Each cell In ThisWorkbook.Sheets("Sales 2013").Range("E3:E500")
 If cell.Value Like "?*@?*.?*" Then
    If Sh.Cells(Target.Row, 7) = "PENDING" Then
     strto = strto & cell.Value & ";"
    End If
 End If
 Next cell
 If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

Application.ScreenUpdating = False
 Set OutApp = CreateObject("Outlook.Application")
 OutApp.Session.Logon

 On Error GoTo cleanup
 Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
 With OutMail
 .To = "Anchor Sales"
 .Bcc = strto
 .Subject = "Enter subject here"
 .Body = "" ' EMPTY FOR NOW
 'USE THIS FOR ENTERING NAMES OF RECIPIENTS IN BODY TEXT "here"
 '"Dear" & Cells(cell.Row, "A").Value _
 & vbNewLine & vbNewLine & _
 "Enter body text " & _
 "here"
 'You can add files also like this
 '.Attachments.Add ("C:\test.txt")
 '.Send 'Or use Display
 .Display
 End With
 On Error GoTo 0
 Set OutMail = Nothing
cleanup:
 Set OutApp = Nothing
 Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()

End Sub

Please help!

user2774174
  • 1
  • 1
  • 1
  • Am I missing something? Where is "Sh" set to anything? And, if it's not, how would VBA know what "Sh.Cells" is referring to? Go back, look at the sample code and see if you can figure out what "Sh" is supposed to refer to, and edit your code appropriately. – Johnny Bones Sep 12 '13 at 20:31
  • @Johnny Bones Sorry, I have no clue. It was written generically in the post where I found it, and I added the "7" for the seventh column and "PENDING" as the word to search for. Does the "Sh" simply mean I need to put in the name of the Workbook/Worksheet again? Please forgive my complete ignorance. I've been working on this while also working on some other projects. Guess I need to search VBA for Idiots when I get home tonight! – user2774174 Sep 12 '13 at 20:40
  • Yeah, I'm no Excel expert (I have lot of Access experience, but you don't have to reference cells in Access...) so I can't tell you exactly how to do it, but "Sh" needs to refer to something. That's where your problem is; "Sh" means nothing to your code at the moment. See if teh Googles knows how to reference a specific cell when you're looping through a spreadsheet. You can try setting Sh = ThisWorkbook.Sheets("Sales 2013") but I can't promise that will work. – Johnny Bones Sep 12 '13 at 20:49
  • sh probably referenced a sheet that was set in earlier code. Target is also not set. Post some more information about how your data is set up inside the sheet and we can help. i.e. status is col 'A' e-mail col 'B' etc... – Automate This Sep 12 '13 at 21:04
  • `If cell.Value Like "?*@?*.?*" And cell.EntireRow.Cells(7).Value = "PENDING" Then..`. – Tim Williams Sep 12 '13 at 21:43
  • @Tim Williams - Thanks! Worked great! Now the buttons work for PENDING, SOLD, and LOST. I'd hoped it was something that simple, but it's all Greek to me. (No offense intended if you're Greek.) Thanks again! – user2774174 Sep 13 '13 at 12:51

1 Answers1

0

of course I would prefer to send one mail per Person (if the recipients shall not know each other), but let's stay with your Problem.

you only need minor changes:

Private Sub CommandButton1_Click()
Dim cell As Range
 Dim strto As String
 For Each cell In ThisWorkbook.Sheets("Sales 2013").Range("E3:E500")
 If cell.Value = "PENDING" Then strto = strto & cell.offset(0, 1).value & ";"

I am not sure if you Need the part "If cell.Value Like "?@?.?*" " or if that is from copy-paste from the code you found... If you Need it, the last line would have to be replaced by

 If cell.Value Like "?*@?*.?*" and cell.Value = "PENDING" Then strto = strto & cell.offset(0, 1).value & ";"  

    'in Offset(0,1) I assume the mailadress is in the cell next to the cell tested for "pending" 
 Next cell
 If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

the rest as you have it.

the Problem was caused by 

    If Sh.Cells(Target.Row, 7) = "PENDING" Then

as you have no definition for "sh" - but you also don't need it.

I hope this helps

Max
  • 744
  • 1
  • 7
  • 19