Anomandaris
03-19-2009, 12:18 AM
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.
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.