Macro to email

[andrewbrock1983]

Hi everyone

I dont know if I am trying to do something impossible.

I want a macro to take all the headed data from each row and put it on an email with the headed data and the filled data beside it on the outlook email.

Please advise

Andrew

[Leith Ross]

Hello Andrew,

Can you provide a sample of what the email will look like and which ranges you will be using?

[andrewbrock1983]

Hi Leith

I cant seem to attach my file. is there another way I can get it to you?

Its hard to explain without actually seeing the file

Andrew

[andrewbrock1983]

anyone else wana chip in feel free?

I can send free via email to any willing helpers :-)

Andrew

[Rick_Stanich]

Attaching is easy.
1. Click on the button labeled "Go Advanced" (Next to "Post Quick Reply", scroll down a bit, you will see it.)
2. This will take you to a new screen, very similar to the one you started with.
3. Scroll down the screen and look for another button labeled "Manage Attachments" and click on it.
4. A new window appears.
5. Click on the button labeled "Browse" (First one).
6. Now a folder browsing window appears.
7. Find the folder where your file is at.
8. Select the file you want to attach (there may be size limits!). IF necessary modify the contents of your file for this attachment procedure. ALWAYS use a COPY of the original file and in some cases remove sesitive data.
9. Once you select a valid file click on "Open".
10. Click on th ebutton labeled "Upload". This will attach the file to the forum.
11. If you do not receive any errors, it was most likely succesful.
12. Close the browsing window and return to your post.
13. Now either finish typing any text you need and then click on the button labeled "Submit Reply".

Your attached file should show up as a link within your post. Just like this post (Example attachment is a simple textfile.)

HTH

[andrewbrock1983]

Its saying some database error-do you have a personal email i can send to?

Andrew

[Rick_Stanich]

Attachment is for OP (Andrew). Attachment has no macros (has a module with no text).

Andrew
Your initial file size was too large for the upload limits. Which is why you could not upload it.

[Leith Ross]

Hello Andrew,

Here is the first macro to email the trade information using Outlook. The information is sent in HTML with the headers in bold and the information in standard font to right separated by 3 spaces. Here is an example of the output.

Date Trade booked: 10/26/2009
Client Account: Test
Conversion Code: Euro-US Dollar
Amount: 16000.00
Rate: 1.11
Funds Due ODL: 17,760.00
Corresponding bank: KAS
Settlement Date: T+3
DSL Dealer:]Andrew
DSDFX Dealer: Test
reference number: DFX1
Date completed:

Please Login or Register  to view this content.

[andrewbrock1983]

This macro works really well but i need to select only data in the spreadsheet for the day in question. I was thinking of putting =today() in a far off cell and getting the macro to look to look for that but my programming knowledge is limited.

Can someone help?

Thanks

Andrew

[Leith Ross]

Hello Andrew,

I am heading off to bed now, but will work on this for you in about 7 hours. In the mean time, someone may fill in for me.

[andrewbrock1983]

Cheers Leith for all your help :-)

[tony h]

Leith has done a good job with this code. The additional bit of only doing today's trades can be done like this (but do read my notes following):

Please Login or Register  to view this content.

I have put an int() round the dates in case they also include time.
Obviously the now function returns today's date and time. now-1 is yesterday.

If the trades pile up sequentially on the sheet (eg today's, followed by yesterday's, followed by the previous day's) then the date test could be altered to exit the main For R ... loop.

[andrewbrock1983]

Not quite sure what this new thing means can you explain?

Thanks

Andrew

[Leith Ross]

Hello everyone,

Here is the final macro that was approved by Andrew. The macro only triggers once all the information has been entered on a row into columns "A:K". The macro then emails the information out to two recipients in HTML format using Outlook. All emails are logged on a separate sheet that includes the client account, date and time, and the trade reference number.

Worksheet Change Event Code

Please Login or Register  to view this content.

Email Macro

Please Login or Register  to view this content.

[andrewbrock1983]

I would like to thank Leith for all his help!

You were amazing!