Find totals and send emails to different people

Hi Friends / Geniuses / Observers

Wondering if you can help me to create a macro to send emails.

1. Attached is an excel spreadsheet. In column A, each time the word total is seen, i want an email to be sent to to someone with the total in colum E.

Eg.
A B C D E
UAFEQ1 TOTAL xxxx xxxxxxxx xxx R 15 000
ALPROP TOTAL xxxx xxxxxxxxx xxx -R 20 000

For UAFEQ1 TOTAL I would like a mail to be sent to john@example.com
For ALRPOP TOTAL I want like a mail to be sent to peter@example.com

If the total in column E is positive i want the mail to say:
Please see deposit of R 15 000

If the total in colum E is negative i want the mail to say:
Please see withdrawal of -R 20 000

The currency is Rands (South Africa)

Sometimes, i need to send 20 or more e-mails. And these can repeat themselves so, there may be two totals for UAFEQ1 TOTAL and a mail must be sent each time. See attached as an example.

The macro can stop when Grand Total is reached. No email needs to be sent for the grand total.

Thanks a million for you help!!

Regards
Dean

Thanks Salim, as helpful as this doc is, truthfully, my VBA skills are way to poor to understand most of it...I am trying though.

Do you, or does anyone have any code which is more specific to my issue which I can copy and past into the module screen?

Dean (Nonboff)

Hi Salim

Thanks for quick reply.

The macro returned a run-time error '9' subscript out of range at the following piece of code:
Set wsMain = ThisWorkbook.Worksheets("CASHFLOWS UTS")
The Worksheet is called CASHFLOWS UTS

You have in the code assumes level 3 (does this mean only 3 emails - because this spreadsheet has up to say 50emails sometimes).

Hi again

The earlier problem is now solved. The macro returned a run-time error '9' subscript out of range at the following piece of code:
Set wsMain = ThisWorkbook.Worksheets("CASHFLOWS UTS")
The Worksheet is called CASHFLOWS UTS

You have in the code assumes level 3 (does this mean only 3 emails - because this spreadsheet has up to say 50emails sometimes).

I guess i have to unqote the following and have to put SMTP server details in. For Outlook express where to i get the SMTP server details?

' iConf.Load -1 ' CDO Source Defaults
' Set Flds = iConf.Fields
' With Flds
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
' = "Fill in your SMTP server here"
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' .Update
' End With

Unbelievably, this worked! I used my gmail account to send mail to my hotmail account and it worked.

Just a couple questions. After the main body (.TextBody = strbody) the value is added. How do i add a signature such as follows after the text?
Thank you
Dean

I will try this from work on Monday using my outlook....

Regards
Dean

Hi Salim / All

I've tried working with this, but the trouble I am having is that in I have a long list of people I need to e-mail. The list is going to change regularly, so i will need to save details either on a spreadsheet or in a folder (preferably a folder). So instead, I am wondering if I can change this up a little.

A new spreadsheet is attached.

I simply want to email each person (fund) in colum A with the value in column D. If the value in colum D is positive, then say deposit. If negative we say, withdrawal.

Email address details are on tab called Email details.

An example of finished product would be as follows:
.......................................................................................................
To:Focused.Fund@example.com
Subject: Focused Equity Fund Class B1

Important Notification

Please note the following deposit into you account: R 15 000

Kind regards
Dean
.......................................................................................................

So, somehow we need to use a vlookup from tab1 to tab2 (althought i would prefer it, if tab2 to were located on a file on my pc say D:\Documents and Settings\Legend\Desktop\
Rondebruin is a legend and I did get the above code to work. I've nearly managed to get the signature added.

If someone could whip up this code for me, i would be hugely grateful!!

Many thanks
Dean

Anybody had any luck with this? Maybe today will be the day!!
Dean

Hi Salim

I have made the adjustments required but it is falling over at point indicated below:

Please Login or Register  to view this content.

Any ideas?

Are you by any chance able to do the loop mentioned in my previous post above, where names in column 1 are emailed the value in column D (email addresses saved on either tab2 or in a file saved in a folder?

Dean

I'm getting desperate to get this loop sorted. Maybe I should close this post and start afresh. Is this allowed?