Sending email using Loops/If Then?

[Anomandaris]

Hi guys, I’m relatively new to VBA, just started learning this stuff couple of weeks ago and with some help from a book for’dummies’ and the web have managed to this far.

What I’m basically trying to do is send emails to clients showing their daily trades. Emails may be sent out several times in a day, updating them with all their trades on the day. I would like to send this in ‘Worksheet’ format as an attachment but to also have a small message in the body of the email. As you can see in my code I did something like this but separately, I’m unable to put it together into one email (both attachment and body message).

So ---in the beginning data will be copied and pasted from another system into ‘Dark’ worksheet, from here we filter all the filled orders that will be emailed to clients (So if Column E=0) copy the rows into “Shadow”(skipping certain columns as I’ve done) ………..then I would go to “Shadow” and Filter by Trader ID (Each Trader ID should have multiple email addresses attached to it, all the client email adds would be listed on “Throne”). …then I run another macro that copies Trades from one specific Trader ID into “Light” (this is not working now for some reason, I got this to work on another version earlier)

From “Light” I intend to send out all the emails. Light should look like this – On the left side of the sheet, Columns A to about J will have client info…. Columns M to Q it should have Buttons that will send emails to all the different clients. Also to avoid sending mail to wrong clients, I want to have a code …something like…..
If Column A value = JON19 Then SendMail, Else Display Error Msg “You have selected wrong client, Try Again” Click on Ok to close box.

(So I would put in a separate on for each of the clients)

-Another problem I had was that when I sent the email, its also sending the ‘buttons’ from “Light” sheet. How can I stop this?
-Also I’m not sure how I should list all the email addresses on “Throne”. Remember for each Trader ID there’s about 7-10 or more email addresses.
-Time and Date was not copied properly into “Shadow”.
- And about some Data Validation, I should probably write a code for this too? Or ppl may accidentally copy things into wrong cell

So that’s what I’m trying to do. If someone can help me I’d be very grateful..thanks a tonne in advance, I’ve attached the file, please let me know if anything I’ve mentioned is unclear.

(It would be cool if after an email was sent out, that trade was deleted from Light and Shadow (NOT from DARK). And also is there a way to make sure that same Transaction.ID does not appear twice, because while all the other values in a row can be repeated this cannot)






I am using Outlook and Excel 2007 btw.

[Bob Phillips]

If you want to send an attachment, you need to copy that worksheet to a new workbook, save that workbook (even temporarily) and attach that workbook to the email.

If you want buttons but not have them on the email, create a toolbar that does it, that will avoid that issue.

[Anomandaris]

Thanks xld, will try that
But can you help me with an 'If-Then statement for sending emails? I have no idea how that works

And how do I create a toolbar to stop emailing buttons?

Thanks

[Bob Phillips]

Which IF statement?

Here is an example of adding a toolbar from your code workbook




Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

[Anomandaris]

Hey thanks buddy,

With regards to the If Then -

From “Light”sheet I intend to send out all the emails. Light should look like this – On the left side of the sheet, Columns A to about J will have client info…. Columns M to Q it should have Buttons that will send emails to all the different clients. Also to avoid sending mail to wrong clients, I want to have a code …something like…..
If Column A value = JON19 Then SendMail, Else Display Error Msg “You have selected wrong client, Try Again” Click on Ok to close box.

Then another would say

IF Column A = BOB20 Then SendEmail, if not then display error message and so on.......

(So I would put in a separate on for each of the clients)


This part seems complicated for me, I'm doing buttons for each client because each client will have like 10+ email addresses...
What do you think is the most efficient way to arrange the email adds on "Throne" (name and email page) ?

[Bob Phillips]

Now I am really confused. Where do those values, JON19, BOB20, etc., come from, how do you know to test for those?

If a client has 10 emails, put them all in separate columns, it is easy to find the last one.

[Anomandaris]

Oh sorry, I wasnt clear...Those values are the Trader IDs that will be copied from our trading system, first all these trades will be copied into Sheet1(Dark), then from Sheet1 I filter out the trades that have been completed and send them to Sheet2(Shadow), from here I would like to select all the trades done by one trader ID --so BOB20 or JON19 or whoever....So completed trades by one trader are sent to Sheet3(Light),

and from Sheet3 I need to send out all the emails.....so far I've been able to get to sheet3, I just want to throw in an 'IfThen' statement into the emailing code so that the buttons can differentiate between different Trader IDs.

Is that better?

so basically after I run a macro from Sheet2(ive alrdy done this), Sheet3 will look like this......it will have a set of trades with data on Cloumns A to J, all will be by same Trader ID. Trader ID will be on Column A.
Columns M:P will have 20 buttons, each sending email to different Trader ID. But someone could easily make a mistake and press on the wrong button which is why I want to attach this 'If Then' macro.....

(for e.g. lets say we have all the BOB20 trades, but i accidentally click on the JON19 email button)

I think this could be done, I just cant figure out how to combine IfThen with sending emails.

I hope thats better, thanks for your patience

[Anomandaris]

This is what I've come up with so far, I need to also throw in --an error message if the cell value is not JON19---



Sub Clients()
Dim cell As Range
Dim Rng As Range
Dim x As Integer
Dim Row As Range

x = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Sheet3.Range("A2:A" & x)
For Each cell In Rng
If cell.Value = "JON19" Then
Sheets("EXIT").copy
ActiveWorkbook.SaveAs "Part of" & ThisWorkbook.Name _
& "" & ".xls"
ActiveWorkbook.SendMail nnn@fff, _
"Trade Recap"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End If
Next cell
End Sub

[Bob Phillips]

You are flip-flopping about all over the place now. The original code used the Outlook client directly, this now uses SendMail.

I think the easiest way is for you to post a workbook with some data in, filtered, setup and all, and then describe with that workbook what procedure steps you want to follow, what checks that you want, and what you want to happen.

[Anomandaris]

I'm not sure about the email stuff, I need to send attachments and a message in the body of the mail--all using outlook...


The code above is just something i found on the web and tweaked a bit, its not one I'd like to use for sending the email
I'm at work now so cant attach anything, but will do after im home in about 6 hours

Ignoring the code I have, is everything else clear though?

thanks

[Bob Phillips]

No, nothing is clear, that is why I made the statement I did in my lost post, #9.

[Anomandaris]

Well I'm still at work, cant send files...

Hmm, I'll make it simple.....
Lets say there's Sheet1 with Names in column A, and email addresses in Columns B to E.
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 = Bob.....If yes then send the email, If NO then show an error message.

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 an 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

Given that, how would the code look?