PDA

View Full Version : Send Email to address in previous box



nickirvine
05-24-2010, 06:53 AM
Hi,

I'm new to excel vb so any help would really be appreciated.

I have a formula in excel which is the following:

=IF(D2>0, "NO", IF('Working Sheet'!H4>'Working Sheet'!F10, "YES", "NO" ))

I wondered if instead of it saying YES is it possible to display a button, which when clicked sends a email to an address defined on another worksheet with a defined subject and body text?

Any help really appreciated.

Thanks,

Nick

Aussiebear
05-24-2010, 03:50 PM
Which cell is likely to hold the value based on your formula?

nickirvine
05-25-2010, 12:31 AM
'Contacts'!E3

is where the email address will be.

Aussiebear
05-25-2010, 02:18 AM
I'm not understanding your formula very well. Does it actually function?

The logic suggests that if D2 > 0 then "No", but if it isn't then If Working Sheet!H4 > Working Sheet F10, "Yes","No"

So I need to ask you again, which cell holds the value resulting from this formula? You said you require a method of sending an email if the value is "Yes". This can be done but you need to be more explicit.

nickirvine
05-25-2010, 02:23 AM
Hi,

Thanks for sticking with me!

The forumula does work!

To explain a little more. I have loads of names listed who are required to respond once a month after a specific deadline, if it is after the deadline and they have not responded it says YES, if not it says NO..so the first part if D2>0 checks to see if there have been any responses, if no then it moves on..

The second part, checks the current date and compares with the deadline.

'Working Sheet'!H4>'Working Sheet'!F10,

If the current date is after the deadline then it says YES.

I'm sure ive probably done it the long way round! but now instead of saying YES i want it to display a button to enable me to send an email reminder. It has to pull the email address from another sheet:

'Contacts'!E3

Hope that explains things a little better.

Thanks for your help.

Aussiebear
05-25-2010, 03:48 PM
I'm sorry but since you don't wish to tell me the location of the cell that contains the formula, I'm washing my hands of this issue.

nickirvine
05-26-2010, 12:02 AM
Sorry I misunderstood your question, the location of the cell could be anywhere, say D1.

Its a shame people on this forum are normally really helpful.

Aussiebear
05-26-2010, 12:59 AM
So you don't know where your cell resides?

nickirvine
05-26-2010, 01:01 AM
I'm still working on the spreadsheet, on where im putting stuff but if you need a specific cell then the forumula is in D1.

geekgirlau
06-02-2010, 02:49 AM
I would suggest a slightly different approach:

Create your formulas next to the list of email addresses/dates
Create a range name for the cells containing the yes/no formula
Create a macro that loops through the range, and sends the email if the value is "YES".You would need a single button on the sheet to launch the macro, and it would handle all emails in one hit.

Please attach a sample containing the structure of your data if you need further assistance. It's almost impossible to guess the structure based on what you've described so far.

nickirvine
06-03-2010, 08:39 AM
thanks geekgirlau thats very helpful i will give it a go.

Bendo
06-03-2010, 10:52 PM
Sub Email()
ActiveWorkbook.SendMail recipients:="fred@gmail.com"
End Sub

Zack Barresse
06-05-2010, 12:32 PM
I think the question needs to be what exactly is your data structure like? So if you go with geekgirlau's suggestions, define exactly what ranges house all of your data, including headers, where to look for your "YES" values and where to look for your email addresses. You started off giving a single example, but sounds like you want to check many cell values at one shot. Also, do you want to do anything to check if you've already emailed these people, or limit a date/time frame on how/when emails should be created?

I'm assuming you're using Outlook here? And what version? To avoid the Outlook security message you can download/install a third party application called ClickYes, use Outlook Redemption, or use the CDO method of emailing a workbook. Let us know your preference.