Solved: Sending email using 'IfThen'

[Anomandaris]

hello experts,

ok so I'm having another go at this, making it simple.

there's Sheet1 with Names in column A, and email addresses in Columns B to E(or more, likely atleast 10)
On Sheet 2 there is some data from Columns A to J. I want these columns emailed to client in a worksheet. Before emailing, the program should check if the values in Column A = 11986.....If yes then send the email, If NO then show an error message. Now tricky thing here is that the account number could be slightly different as in it could be 11986-b or 11986-d, will the code still recognize that as being similar? because email will be going to same company.

For this Account # 11986 lets say the email should be sent to Bob (all recipients in Row 2 in Sheet1), how do i it?

So now - how do I send an email using the email info in Sheet 1? I need Columns A to J from Sheet2 sent as a worksheet attachment, and at the same time send a message in body of email saying " Hi Bob, here is today's trade recap".

Thats it, i hope that's clear now

Thanks in advance, and thanks for ur patience earlier XLD hope this is easier to understand now

[Anomandaris]

Hmm maybe the way I'm structuring this is not the most efficient

Would it be better if in Sheet 2 Column A I had a name, say 'BOB', and in Sheet1 Column A, I had different names on each row along with email addresses for each name ranging thru columns B to J- eg PAT, BOB, JON, etc....I would like the macro to recognize and match the name from Sheet2 to column A in sheet1 and send email to all addresses in that row.


Is that possible?
Or was the previous way easier to do?

Please advise, maybe i'm not on the right track
(Oh and as for the above messg, the Account numbr (11986)wont change, it'll be equal (exactly same), so that makes it somewhat easier i suppose

Thanks

[Anomandaris]

Here's what I have so far,

maybe the code is not in the right order.....What I'm trying to do is make the code go thru a structured process --first step would be to check if Cell Value not equal to DB340, if its not equal then show error msg AND END THE PROCESS (what do i use for that Exit sub?)

If however DB340 =cellvalue then we continue with the rest of the code and send an email to recipients listed on another sheet.

I just need to figure out where I need to throww in the 'Next' command, i have two 'For's so I need 2 'Nexts' yea?

Thanks


Sub eMailLaunchdata()
Dim OL As Object
Dim EmailItem As Object
Dim cell As Range
Dim EmailAddr As String
Dim Recipient As String
Dim Subject As String
Dim Msg As String
Dim x As Integer
Dim rng As Range
Dim row As Range
Dim wb As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0)

x = Sheet5.Range("A" & Rows.Count).End(xlUp).row
Set rng = Sheet5.Range("A2:A" & x)
For Each cell In rng
If cell.Value <> "DB340" Then MsgBox "Error, you have selected wrong client email. Please review"
Else
Sheets("LAUNCH").copy
ActiveWorkbook.SaveAs "Part of" & ThisWorkbook.Name _
& "" & ".xls"
End If

For Each cell In _
Sheets("FISH").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
Subject = "Trade Recap"
Recipient = cell.Offset(0, -1)
EmailAddr = cell.Value
Msg = "Hi Guys " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "A summary of your trades have been attached on the excel worksheet" & _
vbCrLf & vbCrLf
Msg = Msg & "Cheers" & vbCrLf
Msg = Msg & "ZS"

Set wb = ActiveWorkbook
wb.SaveAs "Part of" & ThisWorkbook.Name _
& "" & ".xls"
wb.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = Subject
.Body = Msg
.To = EmailAddr
.Attachments.Add wb.FullName
.Send
End With
Kill wb.FullName
wb.Close False

Application.ScreenUpdating = True

End With
End If
Next
End Sub

[Anomandaris]

up there it shows a happyface, it should actually read Columns("B : J")

[Anomandaris]

and I noticed this part should be deleted as i've already copied that in the bottom

'ActiveWorkbook.SaveAs "Part of" & ThisWorkbook.Name _
& "" & ".xls"
End If'

[Bob Phillips]

Does every line in sheet 2 have to equal the target id, or just any line?

[Anomandaris]

Hi Xld,

Preferably All of column A in sheet2 should be equal to target id. That would reduce possibility of any mistakes.
But if thats difficult to do, then even just the value in cell "A2" from sheet 2 is fine

[Anomandaris]

Thanks a lot for your interest in this

[lucas]

up there it shows a happyface, it should actually read Columns("B : J")

You can easily avoid that problem if you select your code when you post and then hit the vba button. Your code will be formatted for the forum.

[GTO]

hello experts,

ok so I'm having another go at this, making it simple.

there's Sheet1 with Names in column A, and email addresses in Columns B to E(or more, likely atleast 10)

On Sheet 2 there is some data from Columns A to J. I want these columns emailed to client in a worksheet. Before emailing, the program should check if the values in Column A = 11986.....If yes then send the email, If NO then show an error message. Now tricky thing here is that the account number could be slightly different as in it could be 11986-b or 11986-d, will the code still recognize that as being similar? because email will be going to same company.

For this Account # 11986 lets say the email should be sent to Bob (all recipients in Row 2 in Sheet1), how do i it?

So now - how do I send an email using the email info in Sheet 1? I need Columns A to J from Sheet2 sent as a worksheet attachment, and at the same time send a message in body of email saying " Hi Bob, here is today's trade recap".

Thats it, i hope that's clear now

Thanks in advance, and thanks for ur patience earlier XLD hope this is easier to understand now

Greetings Anomandaris,

From a non-expert, while I certainly don't want to speak for Bob (XLD) or anyone else for that matter, your question seems to me leastwise, rather unclear. While I get that you'd like to email specific folks specific info, things become murky thereupon.

For instance, the code you supplied refers to (codename) sheet5, which doesn't exist in the attached workbook.

I get the part about sending all similar account numbers (111-b or 111), but why/how are we picking who gets the mail sent to them in the first place? Are certain account number ranges assigned to specific individuals or ???

If you would attach a workbook example with your code developed so far and provide a plain description of what is supposed to be happening, I think you would get a better response/solution.

In closing, I would like to stress that it is not my intent to be negatively critical. Rather - we'd like to be helpful, we just cannot see over your shoulder so-to-speak, so I am hoping to help you "write a better picture" of what you want to do.

Humbly and hoping to be helpful,

Mark

[Anomandaris]

Thats fine Mark...i know i made it more confusing by throwing in that code. Its not related to the workbook i've posted. The Sheet numbers are different as this was from work.....but anyway I'll post another file, i'll try to keep it clear....i know i'm making the whole thing more complicated than it is. It probably doesnt even require For-next loops, Select Case commands could probably work

i'll be back in a bit, gotta go cook me some chicken! hah

thanks a lot guys

[Bob Phillips]

[vba]


Sub SendMail()
Const MatchCode As String = "11986"
Dim wb As Workbook
Dim LastRow As Long
Dim LastCol As Long
Dim NumMatch As Long
Dim OLApp As Object
Dim EmailItem As Object
Dim EmailRecip As Object
Dim j As Long

With Worksheets("Sheet2")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).row
NumMatch = .Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""" & MatchCode & """,A2:A" & LastRow & "))))")
If NumMatch + 1 < LastRow Then

MsgBox "Data not for " & MatchCode
Exit Sub
End If
End With

With Worksheets("Sheet1")

Set wb = ActiveWorkbook
wb.SaveAs "Part of" & ThisWorkbook.Name & "" & ".xls"
wb.ChangeFileAccess xlReadOnly


Set OLApp = CreateObject("Outlook.Application")
Set EmailItem = OLApp.CreateItem(0)

EmailItem.Subject = "Trade Recap"
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
For j = 2 To LastCol

Set EmailRecip = EmailItem.Recipients.Add(.Cells(2, j).Value)
EmailRecip.Type = 1
Next j
EmailItem.Body = "Hi, " & .Cells(2, 1).Value & ", here is today's trade recap"
EmailItem.Attachments.Add wb.FullName
EmailItem.Display 'Send

Kill wb.FullName

Set wb = Nothing
Set EmailItem = Nothing
Set OLApp = Nothing
End With
End Sub
[/vba]

[Anomandaris]

Infinite Thanks xld,

The code works great! You structured that really well, seems so much less complicated than the jungle i came up with.

I have two questions though.

1. If instead of attaching the whole workbook, I just wanted to attach Columns B to J of Sheet 2...what would that line look like?
Something like....

Worksheets("Sheet2").Range("B1:J").Copy
? I tried that it didnt work, I managed to send the worksheet by trying

Worksheets("Sheet2").Copy

but when i tried the columns more specifically it wouldnt work
any ideas on that would be awesome buddy

2. The other thing is regarding the first part of the code with the NumMatch and Matchcode stuff......it works great with detecting account numbers....but what if instead of numbers I had a name there like say 'Walmart', how should I change that line of code
NumMatch = .Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""" & MatchCode & """,A2:A" & LastRow & "))))")
If NumMatch + 1 < LastRow Then
Is there another command like 'ISTEXT' instead of 'ISNUMBER'?


Thanks a bunch man, I cant wait to try it out at work tomorrow, its working at home pretty good so far.

[Bob Phillips]

I have two questions though.

1. If instead of attaching the whole workbook, I just wanted to attach Columns B to J of Sheet 2...what would that line look like?
Something like....

Worksheets("Sheet2").Range("B1:J").Copy
? I tried that it didnt work, I managed to send the worksheet by trying

Worksheets("Sheet2").Copy

I would just copy the worksheet to a new workbook. You can easily do that say

[vba]

Worksheets("Sheet1").Copy
Set wb = ActiveWorkbook
[/vba]

This creates a new workbook with just that sheet. Then just delete the columns that you don't need.

2. The other thing is regarding the first part of the code with the NumMatch and Matchcode stuff......it works great with detecting account numbers....but what if instead of numbers I had a name there like say 'Walmart', how should I change that line of code
NumMatch = .Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""" & MatchCode & """,A2:A" & LastRow & "))))")
If NumMatch + 1 < LastRow Then
Is there another command like 'ISTEXT' instead of 'ISNUMBER'?

That ISNUMBER is nothing to do with the fact that it is checking a numeric id, it is that the value being looked for is returned as a numeric position by the FIND functon, so it should work just as well with a text id.

[Anomandaris]

Excellent!! you're right the text works with anything, it wasn't working before when i tried, but it sure does now....
Awesome stuff, case solved thanks to u

dude you should be charging ppl a fee for this..
now i just gotta decorate it and make the workbook look good, hope my boss doesnt ask me to change anything too much, haha.

thanks again buddy, so am i supposed to click on something to say that my questions have been answered and you get awarded some points? anything like that on this site?

[lucas]

We would appreciate it if you would mark it solved using the thread tools at the top of the page so that others don't read the whole thread before finding out it has been solved...