hi,
i don't believe I explained very well.
I have 5 cells that need to be filled in with Data in excel and depending on the fourth cell's content I am going to implement a condition statement if then to see if for example test says "none" then my condition statement will execute. here is an example of a working code...it is just that I need to implement this condition as an add-on feature...please look at this:
Private Sub CommandButton1_Click()
Dim myRange As Range
Dim EmailFrom, EmailTo, EmailCC, Subject, xSubject, UserVal, PassVal As String
Dim Content, Content1, ClientName, BAType, BANote, FreeText As String
Dim FileAttached As Variant
Dim CRow, CCol As Integer
Dim oOApp, oOMail As Object
Dim sendNONE As Boolean
With Sheets("connect")
EmailFrom = .Cells(2, 1).Value
EmailNONE = "notnone@gmail.com" ***here if the 4th cell's text is set to "none" I do NOT WANT THE EMAIL TO BE SENT TO THIS ADDRESS AND ONLY TO BE SENT TO THE 2 EMAIL ADDRESSES BELOW***
EmailToTech = "none@netscape.net"
EmailCC = "none@netscape.net"
End With
Set myRange = Sheets("connect").Range("B9:B65000")
CRow = Application.WorksheetFunction.CountA(myRange)
Set myRange = Sheets("connect").Range("B9:B" & CStr(8 + CRow))
Set oOApp = CreateObject("Outlook.Application")
sendNONE = True
For Each Cell In myRange
If IsEmpty(Cell.Offset(0, 7).Value) Then
Subject = "Request " & Cell.Offset(0, 0).Value & "_" & Cell.Offset(0, 4).Value
****in here I need to put in a condtion statement : if cell 4 contains text none than do not send to EmailNONE = notnone@gmail.com and only send to EmailToTech = "none@netscape.net"
And EmailCC = "none@netscape.net"
If InStr(1, xSubject, "none") > 1 Then
sendNONE = False
End If
ClientName = Cell.Offset(0, 0).Value
TpName = Cell.Offset(0, 4).Value
BAType = Cell.Offset(0, 2).Value
BANote = Cell.Offset(0, 3).Value
FreeText = Cell.Offset(0, 6).Value
Content = ClientName & vbTab & vbTab & Cell.Offset(0, 1).Value & vbCrLf & vbCrLf
Content = Content & Cell.Offset(0, 4).Value & vbTab & vbTab & Cell.Offset(0, 5) & vbCrLf & vbCrLf
Content = Content & FreeText & vbCrLf & vbCrLf
Content = Content & vbCrLf & "Please contact none@netscape.net if you have any question."
Content = Content & vbCrLf & "THANK YOU." & vbCrLf & vbCrLf & "private" & vbCrLf & "none" & vbCrLf & "none" & vbCrLf & "none" & vbCrLf & "none" & vbCrLf & "none@netscape.com"
Content1 = "some content." & vbCrLf & vbCrLf & BAType & vbCrLf & vbCrLf
Content1 = Content1 & ClientName & " QID" & vbTab & vbTab & Cell.Offset(0, 1).Value & vbCrLf
Content1 = Content1 & Cell.Offset(0, 4).Value & " QID" & vbTab & vbTab & Cell.Offset(0, 5) & vbCrLf & vbCrLf
Content1 = Replace(Content1, ":", "")
Content1 = Replace(Content1, "/", "")
Content1 = Content1 & BANote & vbCrLf
Set oOMail = oOApp.CreateItem(0)
On Error GoTo Jump
With oOMail
'.From = EmailFrom
.Subject = Subject
.To = EmailNONE
.CC = EmailCC
.body = Content
'.Send
I PROBBALY NEED TO INDICATE HERE THAT ALSO
If InStr(1, xSubject, "none") > 1 Then
sendNONE = False
Else
.Send
End If
Jump:
End With
Set oOMail = Nothing
Set oOMail1 = oOApp.CreateItem(0)
On Error GoTo Jump1
With oOMail1
'.From = EmailFrom
.Subject = "BA Copy for " & ClientName & " - " & TpName & "/ " & BAType
.To = EmailToTech
.CC = EmailCC
.body = Content1
.Send
Jump1:
End With
Set oOMail1 = Nothing
Cell.Offset(0, 7).Value = "Y"
End If
Next
Set oOApp = Nothing
End Sub
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93:





by: purplepomegranitePosted on 2009-10-10 at 02:19:18ID: 25541522
What is that line meant to do?
The inner Replace replaces "" with "" (i.e. does nothing), and the outer replace is missing a parameter (you have specified "-" as the string to replace, but nothing to replace it with).
I also don't see how this would achieve what you are trying to achieve.
To be honest, writing such a function would be complex anyway. You'd be best off using the built-in autocorrect feature of Excel, and just entered misspellings to be autocorrected.