How to create a trigger macro in Excel

[justin.arnold2]

I have a speadsheet with some data in it. If the data meets certain
conditions it needs to be sent out to 2 e-mail receipients. I need to
create a macro that converts this sheet to a pdf and e-mails it to
these two receipients. The variable is Yes or No. If the certain
conditions are met and cell B33 displays 'Yes' then the macro needs to
execute. Since I'm new to this I don't know where to begin. Can
someone help me?

 

[JLGWhiz]

1. I have a speadsheet with some data in it.

Put a name to the spreadsheet like Workbooks("Spreadsheet") or
Worksheets("Sheet1") so we all talk the same language


2. If the data meets certain conditions it needs to be sent out to 2 e-mail
receipients.

You are halfway home with this one. Use an If statement to see if the
data meets the criteria for e-mail.
If not then exit the sub.

If Cells(1, 1) = myCriteria Then
'do stuff
Else
Exit Sub 'Get outta here
End If

3. I need to create a macro that converts this sheet to a pdf

See this site:

http://office.microsoft.com/en-us/excel/HA012142341033.aspx?pid=CL100570551033

and e-mails it to these two receipients.

See this site:
http://www.rondebruin.nl/sendmail.htm

4. The variable is Yes or No. If the certain conditions are met and cell
B33 displays 'Yes' then the macro needs to
execute.

This is a good starting point. Use an If statement to see if conditions
are met.
If Worksheets("Sheet1").Range("B33") = myCondition Then
'Continue process
Else
Exit sub
End If

So Here is the logic:

Sub sndPDF()
If Worksheets("Sheet1").Range("B33") = myCondition Then
'Convert file to PDF
'Open e-mail application (Outlook?)
'Send as attachment or in body of e-mail?
'Save file
'Close e-mail application
End If
Close workbook
End Sub

Have fun.

 

[justin.arnold2]

Thanks for your help. After looking into this a little more I've
decided that instead of converting the worksheet to a PDF and then e-
mailing it I would like to just send an e-mail alert to 2 e-mail
receipients notifing them that they need to take action. If Cell B33 =
Yes then send an e-mail alert saying "Please check your work area for
problems". I would also like the macro to run when the spreadsheet is
first opened, this way I can make it a scheduled task and have it
automatically run everyday at 8am. I have a few other spreadsheets
that work like this however I did not create them. The employee who
created these sheets is no longer with the company and I am now
responsible. I have never done anything like this before and I would
appreciate any help getting started with this. Thanks again for your
help.

 

[Aran Black]

Hi Justin,
There is an event that triggers whnever a sheet opens. It is called
worksheet_activate. You should put any code(routine) you want in there.

If you want Excel to create an email message for you all you have to do is
use the Macro recorder to record this! Start your macro recorder and then go
to your File menu then click on the "Send To..." item on the menu. Then click
on the "Send with attachment" item that pops up after. Your macro recorder
will have recorded the basis for your code!!

Let me know how that goes.
Please rate this answer!

Aran