• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can Excel trigger an email ?

Aidworker17

New Member
Hi,

I was just wondering if Excel could trigger an email if a certain condition was met. Maybe it could be done through conditional formatting; or maybe there is another way ?

If such a thing was possible, I imagine that the code would allow for the email address and text to be inserted somehow.

Maybe this is just my imagination gone wild.

Kind regards,
Jonathan.
 
Using your familiar Microsoft Excel application, you will learn all about automating emails from Excel: how to generate and send tailored, employee-specific emails to different recipients in a single click, right from your Excel spreadsheet. All it takes is: A simple Excel Table. A plug-and-play (VBA) script.

Regards,
Peter
 
Dear Peter,

Thank you for replying.

I understand the idea, but I don't know what a plug-and-play (VBA) script is; or what I would do with it.

Also, what would the simple Excel Table look like ?

Regards,
Jonathan
 
Jonathan, there may be a way to send an email by using Excel formulae, but I don't know it if so. You asked this question in the "VBA macros" forum, and it is certainly possible to send emails from Excel using VBA.

But VBA is a programming language, so to do it using any solution you learn here would involve you learning how to write a program. It needn't be a horrifyingly complicated program—but yeah, it's definitely going to be a computer program. Are you up for that?

The "table" mentioned in Peter's post simply means that you'd create an Excel worksheet containing, at the very least, a list of email addresses. Your program would read the data in that worksheet and send one or more emails. But nothing happens until you write the program.
 
I am up for writing a program, although it seems that the programs may already exist - see the link from the thread posted on May 18, 2022 (above).

Either way, I'm happy to proceed. My main issue is that I have no experience with macros. This does not scare me, but, as I'm a novice, what I would really like is for someone to outline the steps :

1. Write a program; or get one from the link from May 18, 2022 (https://www.rondebruin.nl/win/s1/outlook/mail.htm)

2. Go to the macro part of Excel by doing something

3. Go here, go there

4. Click this, click that

5. Copy and paste this; or write this code

6. Go here, go there

7. Save the macro

8. Go to an Excel spreadsheet

9. Do this, do that

10. Create a table of email addresses

11. Do this, do that

10. Voila you have a procedure / formula to send an email
 
LOL, sounds like you have the right mentality to learn programming, at any rate. Have you written in any other language already? If so, VBA will be easy for you.

Let's start from the very beginning: To get to the area where you would insert VBA statements, you have to start with the "Developer" option on your Excel ribbon.

What's that you say? You don't see "Developer" on your Excel ribbon? Excel doesn't come with it automatically enabled; you have to change a setting to make it visible. Here's how:

1) File, Options
2) In the left masthead, select "Customize Ribbon"
3) In the rightmost column is a list of ribbon options. You should see "Developer" there; check that box to make it appear.
4) Click OK.

Now you should see "Developer" in your Excel ribbon. Click on it, then on "Visual Basic" at the far left. You should now be looking at the VBE, the Visual Basic Editor.

I'm uncertain how the various subwindows will be arranged; I have so long had them arranged on my PC the way I want them that I no longer know how they come from the factory. But in one of the subwindows you should see a sort of tree diagram showing the worksheets that already exist in your workbook. Right-click anywhere in that window to see a list of options, and select Insert, then Module. When you click on Module, Excel will create an empty program module where you can type VBA statements.

To make the most trivial program possible, type in the module:
Code:
Sub Main()
  MsgBox "Hi, Jonathan!"
  End Sub
To run it you can select Devoper, then Macros, and you'll see a new macro named Main; run that.

That's just to get you started. Once you get a taste for blood, come back with more questions and we'll start writing a real program.
 
Thatnk you very much. All that is done.

I graduated IT in 1985; and Pascal is the only language I used seriously. We dabbled in Basic, COBOL and C; but I only remember the structure of Pascal and I think I would recognise most of its procedures / code. It seems that the Macros language has some common structure with Pascal.

Your example reminded me of the very first program I ever wrote in Pascal :

Begin
Writeln("Hi Jonathan");
End

My main issues are:

1. Understanding how to navigate around the Macro window

2. Getting the code's syntax right

3. Saving macros correctly

4. Understanding the link between a macro and the usual formula cells in Excel.

Is a macro just like a formula in Excel - with "=" at the start, then a procedure name, then parameters, etc ... or is it something else ?

5. The process of making all this happen - where to go, what to click, etc ...

I have attached 2 photos of the Macro window - one when it was new; and one after I created your example code. I hope this will help you to guide me around the Macro window.

Regards,
Jonathan
 

Attachments

  • Screen Shots - No Macros.jpg
    Screen Shots - No Macros.jpg
    155.7 KB · Views: 3
  • Screen Shot - Main Macro.jpg
    Screen Shot - Main Macro.jpg
    188.5 KB · Views: 3
Oh, this is going to be fun! If you already know some coding, we won't have to spend a lot of time on the basics, like "what is a variable?" and "how do you use an array?". Come to think of it, if you've used Pascal then I don't even need to explain associative arrays, right? (That's an array that can have a text key, not just numeric subscripts.)

You'll find some good documentation at https://docs.microsoft.com/en-us/office/vba/api/overview/. From there you can select, from the masthead at the left, any of the MS apps—you and I will be looking at Excel mostly, of course, but some Outlook too once we get that far—and the VBA language itself (where it says "Language reference" down near the bottom). If I'm right about the languages you've used so far, you may need me to explain why there's a reference for the VBA language and another for using VBA with Excel; I'm guessing that's another issue you're going to have. But you are SO LUCKY! I had to struggle with object-oriented programming for a few years before I suddenly caught on, thinking it isn't that different from regular programming and simultaneously that it was driving me crazy with unexplainable errors. I can, I hope, save you some frustration. As I said, we'll get to that.

About the subwindows in the VBE: The only two that don't show up in your screen shot that I use a lot are:

1) The "Watch" window. In the VBE you can step through your program one statement at a time, and stop at any point and mouse over a variable name to see what its current value is. This is great for debugging. But you can also put scalar and object variables in the Watch window and view them there.

2) The "Immediate" window. In this window you can type certain statements to be executed immediately. For example, you can type "i=5" to set a variable to a different value than the one it currently has in the program; then resume and i will have that value going forward. Or a more complicated expression, like 'xx = right(yy,5) & ".xlsx" '. Or a question mark followed by an expression, eg "?Now mod 7" or "?xx", to get it to display a value.

You can display these two windows by going, in the VBE, to View and selecting Watch and Immediate. Positioning the windows is harder and I can only recommend that you fool with them until they snap into the locations you want. I have to do this only when I change clients and have a new PC, so I don't remember what I did last time to make it work, except that it took some time and luck.

I'm going to bed now, so just one more thing before I quit for the night: When you look at the VBA language reference, you won't see anything in there that tells you how to interact with Excel. For now I'll give you some examples of how that works, and we'll drill down into more explanation (if you even need any) next time:
Code:
' To test the value of B5 in the currently active worksheet:
If ActiveSheet.Cells(5, 2).Value = "" Then .... 'row 5, column 2
ActiveSheet is an Excel object that refers to the worksheet you're currently looking at. Any worksheet has lots of "properties", such as Name (the worksheet name), DisplayPageBreaks (True or False) and Visible (also True and False). Another one is Cells, which is a collection of all the cells in the worksheet. Each cell is also an object with many properties, such as Font, Row, Column, Height and so on; the Value property is the value in the cell (which is not necessarily the same as the formula). ActiveSheet, Cells and Value are described not in the VBA language reference but in the Excel "object model".

Code:
Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(5, 5).Font.Bold = True
This turns on bold for a range from A1 to E5. Bold, you'll notice, is a subproperty of the Font object, which is itself a property of any Range object. (A Range object is any collection of cells, or of just one cell.)

So far I've been spelling out every object and its property, but I never make it that hard in a real program. I'm more likely to do it like this:
Code:
Set owb = ThisWorkbook 'from now on this variable refers to the whole workbook
Set ows = owb.Worksheets("Sheet1") 'now I can refer to ows instead of ActiveSheet
Set ocs = ows.Cells 'so from now on my Range specifications can take up less typing
If ocs(5, 2) = "" Then ... 'same as above
Range(ocs(1, 1), ocs(5, 5).Font.Bold = True 'same as above
Ok, now bed. Tell me how you're doing so far.
 
Dear Bob,

Thank you very much.

As I'm into the working week, I'll try and get to it before; but if not, I'll get to it on the weekend. Alternatively, could we Skype or Zoom. I imagine that would be a lot quicker.

Regards,
Jonathan.
 
Actually I would eventually have suggested that we switch to email, for the same reason (ie that it's easier). I'm at robhbridges on gmail; drop me a line, when you're ready to proceed, and we'll go on from there.
 
Back
Top